Did you know that SQL Server tracks suspect pages and puts the details into the msdb database in a table called suspect_pages? Well it does, but the question I want answering is what happens if the page within the suspect pages table is fixed? Does it automatically get removed/updated or do we as the administrators have to do anything manually?
There are lots of ways to manage noisy neighbors. For example, you could spin up additional instances of SQL Server on a single physical or virtual machine (VM), and then segregate the applications to a distinct instance. You could also follow the old adage of “one application, one SQL Server” by putting the SQL Server onto its own machine, either physical or virtual. But that can get very expensive very quickly, depending on your licensing methodology.
If you’re running SQL Server 2008 or later, you might want to investigate Resource Governor as an alternative. Resource Governor lets you create limits on the amount of system resources a database and application can consume. On versions 2008 to 2012, Resource Governor can explicitly limit CPU and memory and, starting with version 2014, limit I/O consumption as well. This is powerful medicine for multi-tenant instances with noisy neighbors!
My response to noisy neighbors is to turn my music up really loud as a passive-aggressive response. Oh, wait, wrong kind of noisy neighbor… H/T SentryOne
@DatabaseList – a comma delimited string of database names, allows wildcards
@ListOnly – 1- a list of affected databases will be displayed but snapshots aren’t created.
0- Snapshots are created automatically DEFAULT
I’m a big fan of database snapshots in development and QA environments—take a snapshot, run a workload, revert the snapshot.
First part of the process if to capture the info from DBCC LOGINFO or if you are ready for 2017 the new dmv sys.dm_db_log_stats into a table you can read later to know how many VLFs exist in your database currently. So we going to create table called VLFInfo and used the procedure VLF_UpdateInfo to populate that data. The procedure would be called in step one of a SQL Agent Job to automate the fixing of VLF files during appropriate maintenance windows on your server. But as you will see in the Step 2 may solutions tries to account for not doing to close to when the file just grew an acquired those new lovely extra VLFs.
A very common requirement which can be satisfied by various tools. Personally I like using Visual Studio 2017 Community Edition and I thought I would do a quick overview of it.
First thing, you can find the download from this link: https://www.visualstudio.com/downloads/ and once installed (making sure that you select SQL Server Data Tools) go find Visual Studio 2017 and you will be presented with your start screen.
Click through for the process. This tool is nice for one-off jobs, like when you want to synchronize production down to source control or see the differences between two environments. But if you’re doing these comparisons a lot, I think you’re better off scripting it out using SMO and Powershell.
It’s simple. This option, available in SQL Server 2008 standard and forward, will prevent long running queries based on run time measured in seconds. If I specify a value of 180 the query governor will not allow any execution of a query that it estimates will exceed that value. Notice it says ESTIMATES which means it will be based on optimizer estimates and not ACTUAL run times. It does NOT KILL an actively running query after designated amount of time. There is no worries for rollback scenarios or partial data.
This is totally different from Resource Governor, which can throttle resources like CPU and I/O. I’ve never turned on query governor and I’m not sure I ever would.
Get-CmsHosts is a function I wrote as part of a custom PowerShell module we maintain internally at my employer. It is simple to use, but is the base of most automation projects I work on.
This example will connect to srv-mycms-01 and return a distinct list of instance host names registered with that CMS server that start with the string srv-. This output can then be piped to other commands:
Read on for more examples and details, and then grab the script at the end of Mark’s post.
This can return more than one line with different ComputerManagement (like ComputerManagement10). It depends on the versions you have installed on the host. The number “10” refers to the SQL Server 2008.
Now I can uncomment the last command and run it. The result is:
Ok, a different error message. Let’s dig in it. I logged in on the host and confirmed that I have a SQL Server 2008 R2 instance installed. This means that I’m not accessing a lower version than 2005 like the initial warning message was suggesting.
When we notice that the ambari server is responding slow then we should look first the following details first:
1). The number of hosts added to the ambari cluster. So that accordingly we can tune the ambari agent thread pools.
2). The number of concurrent users (or the view users) who access the ambari server at a time. Sothat accordingly we can tune the ambari thread pools.
3). The age of the ambari cluster. If the ambari server is too old then the possibility is that some of the operational logs and the alert histories will be consuming a large amount of the Database which might be causing ambari DB queries to respond slow.
4). The Ambari Database health and it’s geographic location from the ambari server, to isolate if there are any network delays.
5). Ambari server memory related tuning parameters to see if the ambari heap is set correctly.
6). For ambari UI slowness we should check the network proxy issues to see if there are any network proxies added between client the ambari server machine Or the network slowness.
7). If the ambari users are synced with the AD or external LDAP and if the communication between server and the AD/LDAP is good.
8). Also the resource availability on the ambari host like the available free memory and if any other service/component running on ambari server is consuming more Memory/CPU/IO.
There is a lot of detail here, including quite a few checks to run.
In a recent post, I introduced you to how to work with the registry directly from within SQL Server. Continuing this theme, this post provides an example situation where you would do so.
In this example, we will want to configure SQL Server to enable a few trace flags (TF) when SQL Server starts. Specifically, let’s set trace flags 1117 and 1118 so that they are enabled when SQL Server starts up, and enable them now. Additionally, we have trace flags 1204 and 1222 now enabled on some servers, and we want to disable those (since we have the deadlocks being captured in the system health XE, we don’t need them in the error log also). We also don’t want to force a restart of the SQL Server services.
I’ve always felt a little icky about writing to the registry from SQL Server, but Wayne shows how to do it right.