Advisory Conditions™

Using Advisory Conditions, you can easily create advanced performance alerting, enhanced change detection and Advisory rule sets. When building Advisory Conditions, you can use any combination of ANDs and ORs, and any number of nesting levels, to compare the values retrieved from these multiple sources. Everything is point-and-click, so even complex conditions can be configured quickly.

SentryOne provides a base set of conditions that you can download from inside the Client. This set of conditions will give you a good starting point for monitoring and can serve as reference when creating your own conditions. For more information about Advisory Conditions and how you can create them, check out Greg Gonzalez’s blog post about intelligent alerting.

 

Version: 1

This condition evaluates to True when multiple Availability Replicas from the same Availability Group are hosted on the same VMware/Hyper-V host.

In the event of an issue with the Host server, there could be an outage that nullifies the Always On Availability Group configuration within the Virtual Machines hosted on the VMware Infrastructure.

See Also:

http://blogs.sqlsentry.com/johnmartin/always_on_availabilitygroupsandvms/

--// AG Replicas on the same VM host
--// Applies to : VMware & Hyper-V
--// ConditionKey column breakdown as follows;
--// Host : {VMwareHost Name} | WSFC : {Windows Cluster Name} | AG : {AG Name} - {AG Replicas on Host}
SELECT
	 'Host : ' + QUOTENAME(HS1.Name) + ' | WSFC : ' + CR.ClusterName + ' | AG : ' +  AG1.Name + ' - ' +
		 STUFF((SELECT N', ' + QUOTENAME(NodeName) + '(' + CASE Role WHEN 1 THEN 'P' ELSE 'S' END + ')'
				FROM AlwaysOn.AvailabilityReplica AS AR2
				INNER JOIN VM.VirtualMachine AS VM2
				   ON AR2.NodeName = VM2.Name
				WHERE AR2.GroupID = AR1.GroupID
				  AND VM2.HostSystemID = VM1.HostSystemID
				ORDER BY Role, NodeName
  				FOR XML PATH(N'), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N') as ConditionKey
	,COUNT(VM1.Name) AS ReplicaCount
FROM VM.HostSystem HS1
INNER JOIN VM.VirtualMachine AS VM1
	ON HS1.ID = VM1.HostSystemID
INNER JOIN AlwaysOn.AvailabilityReplica AR1
	ON AR1.NodeName = VM1.Name
INNER JOIN AlwaysOn.AvailabilityGroup AS AG1
	ON AG1.GroupID = AR1.GroupID
INNER JOIN AlwaysOn.ClusterReference CR
	ON AR1.EventSourceConnectionID = CR.EventSourceConnectionID
GROUP BY
	 CR.ClusterName
	,AR1.GroupID
	,AG1.Name
	,VM1.HostSystemID
	,HS1.Name
HAVING COUNT(VM1.Name) > 1
ORDER BY CR.ClusterName, AG1.Name, HS1.Name
;
                        

Version: 1

This condition evaluates to True when multiple Availability Replicas from the same Availability Group have their VMDKs in the same VMware Datastore.

If there is an issue with the Datastore for the VMware server, this could result in an outage that nullifies the Always On Availability Group configuration within the Virtual Machines hosted on the VMware Infrastructure.

See Also:

http://blogs.sqlsentry.com/johnmartin/always_on_availabilitygroupsandvms/

--// AG nodes with storage in the same data store.
--// Applies to : VMware
--// ConditionKey column breakdown as follows;
--// Data Store : {Data Store Name} | WSFC : {Windows Cluster Name} | AG : {AG Name} | {Replica:Disk}
select 'Data Store : ' + quotename(ds.name) + N' | WSFC : ' + cr.ClusterName 
		+ ' | AG : ' + ag.name + ' | ' +
	stuff((select ', ' + ar2.nodename + '('
					+ case [Role] WHEN 1 THEN 'P' ELSE 'S' END 
					+'):{'+ right(vd.[filename],(charindex('/',reverse(vd.[FileName]))-1)) +'}'
			from vm.virtualMachineVirtualDisk as vd
			join vm.datastore as ds2 
				on replace(replace(left(vd.filename,charindex(']'',' vd.filename)),'[','),']',') = ds2.name
			join vm.virtualMachine as vm2 on vd.virtualMachineId = vm2.id
			join AlwaysOn.AvailabilityReplica as ar2 on vm2.name = ar2.NodeName
			where ar2.GroupId = ag.GroupId
				and ds2.name = ds.name
			order by vm2.name
			for xml path('), type).value(N'.[1]',N'nvarchar(max)'),1,2,N') as conditionKey,
			count(vmdk.[filename]) as DiskOverlapCount
from vm.datastore as ds
join vm.virtualMachineVirtualDisk as vmdk
	on replace(replace(left(vmdk.[filename],charindex(']', vmdk.[filename])),'[','),']',') = ds.name
join vm.virtualmachine as vm on vmdk.virtualMachineId = vm.id
join AlwaysOn.AvailabilityReplica as ar on ar.nodename = vm.name
join AlwaysOn.AvailabilityGroup as ag on ar.GroupId = ag.GroupId
join AlwaysOn.ClusterReference as cr on ar.EventSourceConnectionID = cr.EventSourceConnectionID
group by cr.ClusterName, ag.Name, ds.Name, ag.GroupId
order by cr.ClusterName, ag.Name, ds.Name
;
                        

Version: 1

This condition evaluates to True when it finds sys.check_constraints.is_not_trusted values on objects that it expects to be trusted.

When check constraints are not trusted, SQL Server may not be able to use them for query plans and optimization, affecting the performance of your queries. Check constraints that were disabled, perhaps for bulk loading of data, and then enabled, may not be trusted. The table must be altered for those check constraints to be trusted again.

DECLARE @sql nvarchar(max);
SET @sql = N';
SELECT @sql = @sql + N'UNION ALL 
  SELECT DBName = N' name ' COLLATE Latin1_General_BIN, 
  CCsNotTrusted =  
  (
    SELECT COUNT(*) AS CCsNotTrusted
      FROM ' + QUOTENAME(name) + '.sys.check_constraints AS c
      WHERE c.is_not_trusted = 1 
        AND c.is_not_for_replication = 0 
        AND c.is_disabled = 0
  )
  ' FROM sys.databases 
WHERE database_id > 4 AND state = 0;
SET @sql = N'SELECT DBName, CCsNotTrusted FROM 
(' + STUFF(@sql, 1, 10, N') 
   + N') AS x WHERE CCsNotTrusted > 0;';
EXEC sys.sp_executesql @sql;
                        

Version: 1

Verifies that a data file is at least 5120MB.

This condition is used to build other conditions.

Version: 1

Checks the size of the error log.

This condition is used in other custom conditions that query the error log to prevent performance issues associated with larger log files.

DECLARE @currentlogid int
DECLARE @createdate datetime
DECLARE @currfilesize int

CREATE TABLE #err_log_tmp(ArchiveNo int, CreateDate nvarchar(128), Size int)

INSERT #err_log_tmp exec master.dbo.sp_enumerrorlogs

SELECT TOP 1
      @currentlogid = er.ArchiveNo,
      @createdate = CONVERT(datetime, er.CreateDate, 101),
      @currfilesize = er.Size
FROM #err_log_tmp er
ORDER BY [ArchiveNo] ASC

DROP TABLE #err_log_tmp

SELECT @currfilesize;

Version: 1

Verifies that the transaction log file is at least 1024MB.

This condition is used to build other conditions.

Version: 1

Queries sys.configurations to detect when Common Criteria Compliance has been enabled and will be activated on server restart.

Common Criteria Compliance is an advanced option in SQL Server Enterprise and Datacenter editions that can have a dramatic impact on server performance. The value field indicates that the setting is enabled; however, the value_in_use field indicates whether it has been activated. Activating Common Criteria Compliance requires a server restart.

See Also:

https://msdn.microsoft.com/en-us/library/bb326650.aspx

                           SELECT value 
			               FROM sys.configurations 
                           WHERE name = N'common criteria compliance enabled'
                        

Version: 1

This condition evaluates to True when contention factor is greater than 0.

A value over 0 indicates contention on this memory object, with 0 meaning no contention. The value is updated whenever a specified number of memory allocations were made reflecting contention during that period. Applies only to thread-safe memory objects.

See Also:

https://msdn.microsoft.com/en-us/library/ms179875.aspx

SELECT contention_factor 
FROM sys.dm_os_memory_objects;

Version: 1

Queries sys.configurations to detect whenever the server-wide Cost Threshold of Parallelism configuration setting changes on a monitored server, by comparing the last value retrieved to the current value.

                           SELECT value_in_use
                           FROM sys.configurations 
                           WHERE name = N'cost threshold for parallelism'
                        

Version: 1

This condition evaluates to True when there are schedulers that could not create a new worker, most likely due to memory constraints.

See Also:

https://msdn.microsoft.com/en-us/library/ms177526.aspx

SELECT COUNT(*) AS 'Failed Workers' 
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE failed_to_create_worker = 1;

Version: 1

This condition evaluates to True when there are schedulers that have been added due to a hot add CPU event.

See Also:

https://msdn.microsoft.com/en-us/library/ms177526.aspx

SELECT COUNT(*) AS 'Hot Added' 
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE [status] = N'HOT_ADDED';

Version: 1

This condition evaluates to True when there are CPU cores that are visible to SQL Server, but are offline.

SQL Server licensing might prevent a server from using all available cores. Not only can this limit performance, but it can cause additional harm by unbalancing NUMA nodes.

See Also:

http://www.sqlskills.com/blogs/glenn/balancing-your-available-sql-server-core-licenses-evenly-across-numa-nodes/

SELECT COUNT(*) AS 'Offline Schedulers' 
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE [status] = N'VISIBLE OFFLINE'
and scheduler_id < 255;

Version: 1

This condition evaluates to True when CPU schedulers are disabled (is_online = 0).

This indicates that a CPU is offline and SQL Server cannot use it for processing (e.g. queries, batches, etc.). A disabled CPU could be caused by affinity masking or licensing issues, and can impact performance.

See the CPU Schedulers Visible Offline Status condition for additional information.

SELECT COUNT(*) AS 'Unused Schedulers' 
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE [is_online] = 0
and scheduler_id < 255;

Version: 1

This condition evaluates to True if any data file (>=5GB) is larger than it was during the last evaluation of this condition.

Version: 1

Evaluates to True if any data file is smaller than it was during the last evaluation of this condition. Shrinking data files is rarely recommended.

See Also:

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Version: 1

This condition will evaluate to True when the number of database files changes.

Checks the count of database files in master.sys.master_files, regardless of file type (e.g. ROWS, LOG, etc.)

A highlight will appear on the Backups chart of the dashboard to show that backup jobs might need to be created or removed depending on the scenario.

select count(*) from sys.master_files;

Version: 1

This condition evaluates to True when it finds sys.foreign_keys.is_not_trusted values on objects that it expects to be trusted.

When foreign keys are not trusted, SQL Server may not be able to use them for query plans and optimization, affecting the performance of your queries. Foreign keys that were disabled, perhaps for bulk loading of data, and then enabled, may not be trusted. The table must be altered for those Foreign Keys to be trusted again.

DECLARE @sql nvarchar(max);
SET @sql = N';
SELECT @sql = @sql + N'UNION ALL 
  SELECT DBName = N'' + name + '' COLLATE Latin1_General_BIN, 
  FKsNotTrusted =  
  (
    SELECT COUNT(*) AS FKsNotTrusted
      FROM ' + QUOTENAME(name) + '.sys.foreign_keys AS f
      WHERE f.is_not_trusted = 1 
        AND f.is_not_for_replication = 0 
        AND f.is_disabled = 0
  )
  ' FROM sys.databases 
WHERE database_id > 4 AND state = 0;

SET @sql = N'SELECT DBName, FKsNotTrusted FROM 
(' + STUFF(@sql, 1, 10, N') 
   + N') AS x WHERE FKsNotTrusted > 0;';

EXEC sys.sp_executesql @sql;

Version: 3

This condition will be triggered if the count of user sessions with recent activity goes over a specified threshold.

High active sessions by itself does not universally correlate with performance or other problems, but on some systems it can precede an overload state, and/or be an indicator of malicious denial of service (DoS) activity. As such, this condition should be enabled selectively and the threshold adjusted appropriately.

                           select count(*) 
                           from sys.dm_exec_sessions 
                           where is_user_process = 1 and 
                           last_request_start_time > DATEADD(minute, -1, GETDATE())
                        

Version: 1

This condition checks for plan cache bloat that may be alleviated by enabling 'optimize for ad hoc workloads'.

If 'optimize for ad hoc workloads' is false, a high percentage of plan cache is dedicated to single use plans, and page life expectancy is low, then this condition will evaluate to True.

See Also:

http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/

SELECT sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(19,3)))/1024/1024 
FROM sys.dm_exec_cached_plans

Version: 1

This condition evaluates to True when sys.dm_os_schedulers.work_queue_count has an average value greater than 1.

A high average work queue count can indicate that max worker threads for the server should be increased.

See Also:

https://blogs.msdn.microsoft.com/sqlsakthi/2011/03/13/max-worker-threads-and-when-you-should-change-it/

SELECT AVG (work_queue_count)
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE [status] = N'VISIBLE ONLINE';

Version: 4

Wait time measures how long queries are waiting for CPU, disk, memory, or other resources, across all sessions. High avg wait time per second by itself may not be indicative of a problem though, because it doesn't consider the activity level of the system. For example, 5,000ms of avg wait time/sec would be poor on a system with only 30 active users, but excellent on a system with 3,000 active users.

For this reason, this condition divides avg wait time/sec (Total) by the number of active user sessions to calculate the avg wait time per session . If this value is over 50ms users may be experiencing noticeable delays.

This value should not be considered a universal hard threshold, but more of a guideline, and may need to be adjusted for different systems. Note that SQL Sentry filters out innocuous waits, so the wait time used here will be lower than if collected directly from SQL Server, and more accurately reflects user-impacting waits.

See Also:

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

http://www.sqlperformance.com/category/waits-2

                           select count(*) 
                           from sys.dm_exec_sessions 
                           where is_user_process = 1 and 
                           last_request_start_time > DATEADD(minute, -1, GETDATE())

Version: 3

Query plan compiles should generally be < 15% of batches per second. higher values indicate plan reuse is low, and will often correlate with high cpu, since plan compilation can be a cpu-intensive operation. high compiles may correlate with low plan cache hit ratios, and can be an indicator of memory pressure, since there may not be enough room to keep all plans in cache.

If you see consistently high compiles, run a Quick Trace and sort the results by Cache Misses , then expand details to view actual compiling statements (SP:CacheMiss events, highlighted) along with the reason (SubClass) and procedure (Object).

See Also:

http://technet.microsoft.com/en-us/library/cc966425.aspx

http://technet.microsoft.com/en-us/library/cc293623.aspx

Version: 4

Query plan compiles can be a CPU-intensive operation. If total CPU is high for an extended period and compiles are also high, they may be causing or at least contributing to the high CPU.

See the High Compiles conditions for more details on troubleshooting the issue.

Version: 4

Context switches represent the combined rate at which all processors on the computer are switched from one thread to another. Consistently high values over 7,500 per logical processor can mean that the server is spending too much time switching threads instead of actively running threads.

Version: 1

Context switches represent the combined rate at which all processors on the computer are switched from one thread to another. Consistently high values over 5,000 per logical processor can mean that the server is spending too much time switching threads instead of actively running threads.

Version: 3

Sustained CPU utilization greater than 90% may indicate a CPU bottleneck.

Use the Peformance Advisor for Windows Processes tab to ascertain the processes causing the high CPU. On dedicated SQL Server machines most CPU should be associated with the SQL Server process(es). If SQL Server is causing the high CPU, use Top SQL and/or QuickTrace to determine which sessions and queries are consuming the most CPU.

NOTE: If you launch QuickTrace by highlighting a range on the CPU Usage chart, the results will be pre-sorted by CPU % descending, so the greatest consumers will be on top.

Version: 3

On dedicated SQL Servers most of the CPU utilization should generally be related to the SQL Server process (sqlservr.exe). This condition will detect when CPU utilization is high, and at least 25% is related to some process other than SQL Server.

Version: 1

Sustained CPU utilization greater than 90% on a single core may indicate a CPU bottleneck. The CPU Total usage value is checked against 100/Number of cores used by the SQL Server instance.

Use the Performance Advisor for Windows Processes tab to ascertain the processes causing the high CPU. On dedicated SQL Server machines most CPU should be associated with the SQL Server process(es). If SQL Server is causing the high CPU, use Top SQL and/or QuickTrace to determine which sessions and queries are consuming the most CPU.

NOTE: If you launch QuickTrace by highlighting a range on the CPU Usage chart, the results will be pre-sorted by CPU % descending, so the greatest consumers will be on top.

SELECT  100.0/COUNT(*) 
FROM SYS.DM_OS_SCHEDULERS 
WHERE STATUS = 'VISIBLE ONLINE' AND IS_ONLINE = 1;

Version: 1

This condition evaluates to True when total signal waits are above 20%.

High signal waits are often indicative of CPU pressure, but the threshold may vary by system.

See Also:

https://sqlserverperformance.wordpress.com/2010/04/13/a-dmv-a-day-%E2%80%93-day-14/

http://www.sqlsentry.com/white-papers/troubleshooting-sql-server-wait-stats

SELECT CAST(100.0 * SUM(signal_wait_time_ms)/ SUM (wait_time_ms)AS NUMERIC(20,2)) 
FROM sys.dm_os_wait_stats WITH (NOLOCK); 

Version: 3

Large log send or recovery queues can be an indicator of a system or network bottleneck. This condition will be triggered if either queue goes over 3MB.

Use the Performance Advisor AlwaysOn Management interface and Dashboard to troubleshoot.

See Also:

Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups -

http://msdn.microsoft.com/en-us/library/jj191711.aspx

Version: 3

This condition will detect if high disk latency is potentially causing high disk wait time in SQL Server.

To troubleshoot, view the Performance Advisor Disk Activity tab to locate bottlenecks in the disk system or specific database files. On the Dashboard, highlight a range with high latency and Jump To->Top SQL to determine which queries may be contributing to and/or suffering from high disk waits.

Disk latency is the only disk measurement for which there are generally accepted ranges that represent good and bad performance from a SQL Server perspective. The following ranges can be used as a general guideline to determine whether disk latency is acceptable:

  • Less than 10ms - Fast *
  • Between 10ms - 20ms - Acceptable
  • Between 20ms - 50ms - Slow
  • Greater than 50ms - Critical

* For transaction log writes, between 0ms and 2ms is desirable.

Version: 3

Large log send or redo queues can be an indicator of a system or network bottleneck. This condition will be triggered if either queue goes over 3MB.

Use the Performance Advisor Dashboard to troubleshoot.

Version: 1

This condition evaluates to True when there are 10+ failed logins in the last two minutes. The first query ensures that the error log is not more than 2MB (for performance consideration) and the second query checks for the number of failed logins.

A high number of failed login attempts may indicate that an unauthorized user is trying to access the system. SQL Server Security properties must have Login auditing enabled for this condition to function.

In SQL Server Management Studio go to Server Properties --> Security page to check on these settings. The Login auditing option must be set to "Failed logins only" or "Both failed and successful logins".

See Also:

https://msdn.microsoft.com/en-us/library/ms188470.aspx

						DECLARE @currentlogid int
						DECLARE @createdate datetime
						DECLARE @currfilesize int

						CREATE TABLE #err_log_tmp(ArchiveNo int, CreateDate nvarchar(128), Size int)

						INSERT #err_log_tmp exec master.dbo.sp_enumerrorlogs

						SELECT TOP 1
						@currentlogid = er.ArchiveNo,
						@createdate = CONVERT(datetime, er.CreateDate, 101),
						@currfilesize = er.Size
						FROM #err_log_tmp er
						ORDER BY
						[ArchiveNo] ASC

						DROP TABLE #err_log_tmp

						SELECT @currfilesize;
						CREATE TABLE #log
						  (
						   logdate DATETIME,
						   info    VARCHAR (25) ,
						   data    VARCHAR (200)
						  );

						  INSERT INTO #log
						  EXECUTE sp_readerrorlog 0, 1, 'Login failed';

						  SELECT count(*) AS occurences
						  FROM   #log
						  WHERE  logdate > dateadd(minute, -2, getdate());

						  DROP TABLE #log;

Version: 1

This condition evaluates to True when sys.dm_os_schedulers.pending_disk_io_count has an average value greater than 0.

A high pending disk IO count may be indicative of an IO bottleneck.

See Also:

https://msdn.microsoft.com/en-us/library/ms177526.aspx

https://sqlserverperformance.wordpress.com/2010/03/08/easy-ways-to-detect-io-pressure-in-sql-server-2008/

SELECT AVG (pending_disk_io_count)
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE [status] = N'VISIBLE ONLINE';

Version: 1

If the High Performance power plan is not enabled, Windows may be throttling CPU performance. This in turn can have a significant negative impact on both Windows and SQL Server performance, so it is highly recommended that a dedicated SQL Server always use the High Performance plan.

This condition uses a WMI query to determine whether the High Performance plan is active, and will trigger if it is not.

Notes:

- It is possible to use a custom power plan that does not throttle CPU, although this is not a common practice.
- If the WMI query returns the error, "The program is blocked by group policy", it may mean that your network admin has applied a power plan via group policy as described in the post below. If so, you should check with them to ensure that the plan does not throttle CPU.

See Also:

http://support.microsoft.com/kb/2207548

http://greg.blogs.sqlsentry.net/2011/01/ensuring-maximum-cpu-performance-via.html

Version: 3

Query plan recompiles should generally be < 15% of initial compiles, and will often correlate with high cpu, since plan compilation can be a cpu-intensive operation. They may be caused by statistics updates, schema changes, etc., and may correlate with low plan cache hit ratios.

If you see consistently high recompiles, run a Quick Trace and sort the results by Recompiles , then expand details to view actual recompiling statements (sp:recompile events, highlighted) along with the reason (subclass) and procedure (object).

See Also:

http://technet.microsoft.com/en-us/library/cc966425.aspx

http://technet.microsoft.com/en-us/library/cc293623.aspx

Version: 1

This condition divides the 'log_send_rate' and 'redo_rate' values found in the sys.dm_hadr_database_replica_states DMV to determine the average redo completion time.

Log Send Rate is the rate at which the logs are being sent to secondary databases in an availability group, and Redo Rate is the rate in which the log records are being redone on the secondary databases.

By default, this condition will evaluate to True if the average redo completion time is greater than 5 mins (in secs). In addition, please note that this condition is designed to return rows only when it is executed against the primary replica.

See Also:

http://sqlperformance.com/2015/08/monitoring/availability-group-replica-sync

SELECT [AG Name, Replica, & Database] = AG.name
          + N' [' + AR.replica_server_name + N']'
          + N': (' + DB.database_name + N')',
        [Average Redo Completion Time (Sec)]
          = COALESCE( RS.log_send_rate / NULLIF (RS.redo_rate, 0),0)
 FROM sys.dm_hadr_database_replica_states AS RS
 INNER JOIN sys.availability_databases_cluster AS DB
   ON RS.group_id = DB.group_id
   AND RS.group_database_id = DB.group_database_id
 INNER JOIN sys.availability_groups AS AG
   ON AG.group_id = RS.group_id
 INNER JOIN sys.availability_replicas AS AR
   ON RS.group_id = AR.group_id
   AND RS.replica_id = AR.replica_id
      INNER JOIN sys.dm_hadr_availability_group_states AS AGS
   ON AGS.group_id = AG.group_id 
    INNER JOIN sys.dm_hadr_availability_replica_states AS ars
    ON ar.replica_id = ars.replica_id
    WHERE ars.role_desc = 'SECONDARY'
    AND AGS.primary_replica = @@SERVERNAME
 ORDER BY AG.name, AR.replica_server_name, DB.database_name;

Version: 1

This condition evaluates to True when sys.dm_os_schedulers.runnable_tasks_count has an average value greater than 10.

A high runnable tasks count is indicative of CPU pressure.

See Also:

https://sqlserverperformance.wordpress.com/2010/04/08/a-dmv-a-day-%E2%80%93-day-9/

SELECT AVG (runnable_tasks_count)
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE [status] = N'VISIBLE ONLINE';

Version: 3

SQL Server database transaction logs contain multiple virtual log files , or VLFs. The number of VLFs is dictated by the initial size and auto-growth size for the transaction log. Too many VLFs can lead to increased backup/recovery times and possible performance problems.

See the Performance Advisor Disk Space tab for VLF counts for all log files, as well as configured auto-growth size and how many VLFs will result from each auto-growth.

If VLFs are too high, the count can be reset by shrinking and resizing the log. See the "8 Steps" article below for more details and scripts.

See Also:

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

Version: 1

The vCPU Wait Time is a SQL Sentry virtual counter. It is calculated by multiplying CPU wait time per dispatch (the average time, in nanoseconds, spent waiting for a virtual processor to be dispatched onto a logical processor) by the number of dispatches per second onto the logical processor.

Version: 1

This condition evaluates to True when a database in the SQL Sentry repository has a compatibility level that does not match that of the Master database (current compatibility level).

Compatibility mode allows an older database to run on a newer version of SQL Server at the expense of not being able to run newer features. While some databases need to use an older compatibility mode, not all of them do.

If there are databases which must run in compatibility mode, please emake provisions to exclude them in order to reduce false positive values.

SELECT 
CAST (pasd.CompatabilityLevel AS NVARCHAR(3)) + ' ' + esc.ServerName  + ': (' + pasd.Name + ')' as [Current Level, Server, Database],
der.MaxCompatabilityLevel
FROM [dbo].[PerformanceAnalysisSqlDatabase] pasd
INNER JOIN [dbo].[EventSourceConnection] esc
	ON esc.ID = pasd.EventSourceConnectionID
INNER JOIN 
	(SELECT EventSourceConnectionID, CompatabilityLevel [MaxCompatabilityLevel]
	FROM [dbo].[PerformanceAnalysisSqlDatabase]  
	WHERE DatabaseID = 1) der
	ON der.EventSourceConnectionID = pasd.EventSourceConnectionID
WHERE pasd.DatabaseID <> 2
AND esc.IsPerformanceAnalysisEnabled = 1
AND pasd.CompatabilityLevel <> der.MaxCompatabilityLevel;

Version: 4

The Windows file (or system) cache stores in memory file data that is read from or written to disk. Since SQL Server manages its own memory, this cache will typically not grow large on a dedicated SQL Server. However, if some other process is causing it to grow large unexpectedly it can lead to memory pressure for SQL Server.

In the case of Analysis Services, database files may be loaded into and served from the Windows file cache, even if the associated file data doesn't exist in the SSAS internal caches. For this reason, monitoring the file cache is important to ensure that physical memory is being used effectively, and that memory contention doesn't occur between the SSAS process, the file cache, and other processes on the server, including SQL Server.

Version: 1

This conditions evaluates to True if any transaction log, of at least 1024MB in size, is larger than it was during the last evaluation of this condition.

Frequent log file growth can lead to the creation of too many Virtual Log Files (VLFs). You can prevent this by optimizing the initial size and auto-growth size for the transaction log.

See the High VLF Count custom condition for more information.

Version: 1

This condition queries sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_sessions, sys.dm_exec_connections, and sys.dm_exec_sql_text to find running transactions. The query returns the value (in seconds) of the longest running open transaction and the condition evaluates to True if that value is greater than or equal to 90 seconds.

This is a modified version of a query in Paul Randal's blog:

http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/

						SELECT COALESCE(MAX([Transaction Length]),0) [LongestRunningTransaction]
						FROM (
						SELECT
							[s_tst].[session_id],
							[s_es].[login_name] AS [Login Name],
							DB_NAME (s_tdt.database_id) AS [Database],
							[s_tdt].[database_transaction_begin_time] AS [Begin Time],
						 DATEDIFF(ss, [s_tdt].[database_transaction_begin_time], GETDATE()) [Transaction Length],
							[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
							[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
						 CASE [s_tdt].database_transaction_type
						  WHEN 1 THEN 'Read/write transaction'
						  WHEN 2 THEN 'Read-only transaction'
						  WHEN 3 THEN 'System transaction'
						 END [Transaction Type],
						 CASE [s_tdt].database_transaction_state
						  WHEN 1 THEN 'The transaction has not been initialized.'
						  WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
						  WHEN 4 THEN 'The transaction has generated log records.'
						  WHEN 5 THEN 'The transaction has been prepared.'
						  WHEN 10 THEN 'The transaction has been committed.'
						  WHEN 11 THEN 'The transaction has been rolled back.'
						  WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.'
						 END [Transaction State],
						 [s_est].text AS [Last T-SQL Text]
						FROM sys.dm_tran_database_transactions [s_tdt]
						INNER JOIN sys.dm_tran_session_transactions [s_tst]
						 ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
						INNER JOIN sys.[dm_exec_sessions] [s_es]
						 ON [s_es].[session_id] = [s_tst].[session_id]
						INNER JOIN sys.dm_exec_connections [s_ec]
						 ON [s_ec].[session_id] = [s_tst].[session_id]
						CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
						WHERE [s_tst].is_user_transaction = 1
						) RunningTransactions

Version: 3

Most Windows servers require at least 100MB of available memory at all times to ensure proper function, and avoid expensive disk paging operations. This condition will alert if available memory drops below this threshold.

Version: 3

Page Life Expectancy (PLE) is the average lifespan in seconds of a data page in buffer, and is one of the best indicators of memory pressure. In general, the larger the buffer cache size, the higher it should be.

The formula* used in this condition takes the size of the buffer pool into account when determining whether the current PLE value is problematic:

PLE < (DataCacheSizeInGB/4GB * 300)

Each NUMA node has its own PLE value, so some nodes may be suffering from memory pressure while others are not. This condition checks all NUMA nodes via the Any instance, which automatically iterates all nodes and applies the formula in a synchronized fashion across all counters.

The first check in the condition ensures that the buffer size is at least 2GB (default) before processing the formula, to avoid false positives caused by small buffers.

See Also:

http://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/

http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

Version: 3

The Output Queue Length is the size of the network output packet queue, in packets. A sustained value of >3 may indicate a network bottleneck. This condition will detect a queue length >3 on any network adapter.

See Also:

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

http://www.sqlperformance.com/category/waits-2

Version: 1

This condition evaluates to True when NUMA is disabled.

See Also:

https://sqlserverperformance.wordpress.com/2010/04/08/a-dmv-a-day-%E2%80%93-day-9/

SELECT COUNT(DISTINCT parent_node_id)
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE parent_node_id <> 32;

Version: 7

Detects whenever the optimize for ad hoc workloads server config setting changes, by comparing the last value retrieved to the current value.

                            SELECT value_in_use
							FROM sys.configurations
							WHERE name = N'optimize for ad hoc workloads';                        
                        

Version: 1

This condition evaluates to True when it locates databases in the SQL Sentry repository with an inadequate level of page protection.

From release 2005 databases should be using Checksum. Databases upgraded or scripted from an earlier version may still be using torn protection.

The result set format is as follows: Key (server name: (Database), value (1 if incorrect).

See Also:

https://msdn.microsoft.com/en-GB/library/bb522682.aspx

To change a single database on a server run the following code after changing the database name:

USE [master]
GO
ALTER DATABASE [AdventureWorks] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;
GO

To change all databases on a server run the following code:

SELECT N'ALTER DATABASE [' + db.name + N'] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;'
FROM sys.databases AS db 
WHERE db.page_verify_option_desc <> N'CHECKSUM'; 

SELECT 
esc.ServerName  + ': (' + pasd.Name + ')',
 CASE 
 WHEN pasd.CompatibilityLevel <= 80 AND pasd.PageVerifyOption <> 1 THEN 1
 WHEN pasd.CompatibilityLevel >= 90 AND pasd.PageVerifyOption <> 2 THEN 1
 ELSE 0
 END [IsBad],
pasd.CompatibilityLevel,
pasd.PageVerifyOption
FROM [dbo].[PerformanceAnalysisSqlDatabase] pasd
INNER JOIN [dbo].[EventSourceConnection] esc
 ON esc.ID = pasd.EventSourceConnectionID
WHERE pasd.DatabaseID <> 2
AND esc.IsPerformanceAnalysisEnabled = 1
AND 
(CASE 
 WHEN pasd.CompatibilityLevel <= 80 AND pasd.PageVerifyOption <> 1 THEN 1
 WHEN pasd.CompatibilityLevel >= 90 AND pasd.PageVerifyOption <> 2 THEN 1
 ELSE 0
END) = 1;

Version: 7

Detects whenever the server-wide max degree of parallelism (MAXDOP) server config setting changes on a server with more than one processor, by comparing the last value retrieved to the current value.

                              SELECT value_in_use
							  FROM sys.configurations
							  WHERE name = N'max degree of parallelism';
                        

Version: 1

Detects when there are sleeping sessions with open transactions older than 10 minutes by default. Such sessions can cause blocking, and can prevent the transaction log from clearing, leading to excessive log file growth and space exhaustion. Additionally, when snapshot isolation is used, they can prevent version cleanup from occurring in tempdb.

The start time, session_id, host, application and database are returned for the oldest 5 transactions by default. The query itself only returns transactions older than 5 minutes by default, to avoid bringing back unnecessary results on systems with many short-running transactions.

SELECT TxDesc = '[' + CONVERT(varchar, es.last_request_start_time, 120) + '] (' + CAST(es.session_id AS varchar(6)) + ') ' + host_name + ':' + program_name + ' [' + DB_NAME(dt.database_id) + ']'
	, OpenMinutes = DATEDIFF(minute, es.last_request_start_time, GETDATE())
FROM sys.dm_exec_sessions es
JOIN sys.dm_tran_session_transactions st
  ON es.session_id = st.session_id
JOIN sys.dm_tran_database_transactions dt
  ON dt.transaction_id = st.transaction_id
WHERE dt.database_id <> 32767
  AND status = 'sleeping'
  AND es.last_request_start_time < DATEADD(MINUTE, -5, GETDATE())
ORDER BY es.last_request_start_time

Version: 1

This condition queries the SQL Sentry database to detect when auto-growth has been disabled for any data or transaction log file.

Although auto-growth should be considered a contingency for unexpected file growth vs. the primary means of managing growth, disabling it can lead to eventual free space exhaustion, which will halt all DML activity.

See Also:

https://support.microsoft.com/en-us/kb/315512

SELECT
  DBFileName = EC.ObjectName + '.' + SF.Name
 ,SF.Growth
FROM EventSourceConnection EC
JOIN PerformanceAnalysisSqlFile SF
  ON EC.ID = SF.EventSourceConnectionID
WHERE EC.IsPerformanceAnalysisEnabled = 1

Version: 1

Queries the SQL Sentry database to detect when the next auto-growth will exceed either available free disk space or the max file size. The key column represents the server and file name, and the value column represents the "space debt" in MB.

The "space debt" is the minimum of the free space (PerformanceAnalysisDeviceLogicalDisk.FreeSpace) minus the growth size setting (PerformanceAnalysisSqlFile.Growth) or the maximum size (PerformanceAnalysisSqlFile.MaxSize) minus the current size plus growth size.

				SELECT 
				  DBFileName
				 ,SpaceDebtMB = MIN(SpaceDebtMB)
				FROM
				 (
				  SELECT
					DBFileName = EC.ObjectName + '.' + SF.Name
				   ,SpaceDebtMB = (LD.FreeSpace / 1024.0 / 1024.0) - (SF.Growth / 128)
				  FROM EventSourceConnection EC
				  JOIN PerformanceAnalysisSqlFile SF
					ON EC.ID = SF.EventSourceConnectionID
				  JOIN PerformanceAnalysisDeviceLogicalDisk LD
					ON LD.DeviceID = EC.DeviceID
				   AND LD.Name = LEFT(SF.FileName, LEN(LD.Name))
				  WHERE SF.Growth > (LD.FreeSpace / 8192)
				   AND EC.IsPerformanceAnalysisEnabled = 1
				  UNION
				  SELECT
					DBFileName = EC.ObjectName + '.' + SF.Name
				   ,SpaceDebtMB = (SF.MaxSize - (SF.Size + SF.Growth)) / 128
				  FROM EventSourceConnection EC
				  JOIN PerformanceAnalysisSqlFile SF
					ON EC.ID = SF.EventSourceConnectionID
				  JOIN PerformanceAnalysisDeviceLogicalDisk LD
					ON LD.DeviceID = EC.DeviceID
				   AND LD.Name = LEFT(SF.FileName, LEN(LD.Name))
				  WHERE SF.MaxSize <> -1
				   AND (SF.Growth + SF.Size) > SF.MaxSize
				   AND EC.IsPerformanceAnalysisEnabled = 1
				  ) DBFiles
				GROUP BY DBFileName

Version: 1

Returns the SQL Sentry Monitoring Service that's been offline the longest in any site with actively watched connections. This condition only works when two or more monitoring services are used.

To avoid triggering this condition when intentionally stopping a service, you can temporarily move it into a site with no watched connections using Site Configuration.

					SELECT TOP 1 dbo.ManagementEngine.ServerName
					FROM
						(
							SELECT Device.ID
								 , Device.SiteID
							FROM dbo.Device
							WHERE IsPerformanceAnalysisEnabled = 1
						UNION
							SELECT Device.ID
								 , Device.SiteID
							FROM dbo.EventSourceConnection
							INNER JOIN dbo.Device
							   ON dbo.EventSourceConnection.DeviceID = dbo.Device.ID
							WHERE EventSourceConnection.IsWatched = 1
							   OR EventSourceConnection.IsPerformanceAnalysisEnabled = 1
						) WatchedDevices
					INNER JOIN dbo.ManagementEngine
					   ON WatchedDevices.SiteID = dbo.ManagementEngine.SiteID
					WHERE dbo.ManagementEngine.HeartbeatDateTime < DATEADD(minute, -3, GETUTCDATE())
					   OR dbo.ManagementEngine.HeartbeatDateTime IS NULL
					ORDER BY ISNULL(dbo.ManagementEngine.HeartbeatDateTime, dbo.ManagementEngine.LastInitializationDateTime)

Version: 3

Memory Grants Pending is the number of processes waiting for a query workspace memory grant. Ideally this value should be 0 at all times, but it can go above 0 in cases of severe memory pressure.

When it does, RESOURCE_SEMAPHORE waits will typically also be >0, since this wait is a measure of the time that queries had to wait for memory grants. This type will be visible in the Waits chart tooltips for the Memory class and category.

Version: 3

Under severe memory pressure Windows can page the SQL Server process to disk, which can dramatically impact SQL Server performance. This custom condition will query the ring buffer for the latest working set utilization value for the SQL Server process, and alert if it drops below 75%. If no record exists, 100(%) will be returned, which is the optimal state.

Note that if the Lock pages in memory user right has been assigned to the SQL Server service user account, it will prevent Windows from paging the SQL Server process.

See Also:

How to reduce paging of buffer pool memory in the 64-bit version of SQL Server -

http://support.microsoft.com/kb/918483

SQL Server and the “Lock pages in memory” Right in Windows Server

Great SQL Server Debates: Lock Pages in Memory

SELECT MIN(MemUtilization)
FROM
(
SELECT
	ISNULL(rec.x.value('(MemoryRecord/MemoryUtilization)[1]','tinyint'), 100) As MemUtilization
FROM (
		SELECT TOP 1 CAST(orb.record AS XML) AS xmlRec
		FROM sys.dm_os_ring_buffers AS orb
		CROSS JOIN sys.dm_os_sys_info AS osi
		WHERE orb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
			AND DATEADD(second, -((osi.cpu_ticks/(osi.cpu_ticks/osi.ms_ticks) - orb.timestamp) / 1000), GETDATE()) > DATEADD(minute, -120, GETDATE())
		ORDER BY timestamp DESC
	) rb
CROSS APPLY rb.xmlRec.nodes('Record') rec(x)
UNION
SELECT 100
) MU
 

Version: 1

This condition evaluates to True when sys.dm_os_process_memory.process_physical_memory_low is true.

This indicates that the process is responding to low physical memory.

See Also:

https://msdn.microsoft.com/en-us/library/bb510747.aspx

SELECT process_physical_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK); 

Version: 1

This condition evaluates to True when sys.dm_os_process_memory.process_virtual_memory_low is true.

This indicates that more virtual memory is needed for the SQL Server process.

See Also:

https://msdn.microsoft.com/en-us/library/bb510747.aspx

SELECT process_virtual_memory_low
FROM sys.dm_os_process_memory WITH (NOLOCK); 

Version: 0

The query pool refers to Formula Engine activity for queries. If you are seeing consistently high queue lengths, but not high CPU utilization, you may want to adjust your MaxThreads and/or CoordinatorExecutionMode properties for your SSAS instance.

More details on these settings are covered in section 6.11 of the SSAS 2008 Performance Guide.

Remember that the FE is single threaded, so increasing the query pool setting may not improve performance of any one query, but may improve the performance in handling multiple simultaneous requests.

Version: 0

SSAS uses memory limit settings to determine how it allocates and manages its internal memory. Memory\LowMemoryLimit defaults to 65% of the total available physical memory on the machine (75% on AS2005), and Memory\TotalMemoryLimit (also sometimes called the High Memory Limit) defaults to 80%. This is the total amount of memory that the SSAS process itself (msmdsrv.exe) can consume.

Once memory usage hits the Low limit, memory cleaner threads will kick in and start moving data out of memory in a relatively non-aggressive fashion. If memory hits the Total limit, the cleaner goes into crisis mode… it spawns additional threads and gets much more aggressive about memory cleanup, and this can dramatically impact performance.

See Also:

http://greg.blogs.sqlsentry.net/2009/06/analysis-services-memory-limits.html

Version: 0

SSAS uses memory limit settings to determine how it allocates and manages its internal memory. Memory\LowMemoryLimit defaults to 65% of the total available physical memory on the machine (75% on AS2005), and Memory\TotalMemoryLimit (also sometimes called the High Memory Limit) defaults to 80%. This is the total amount of memory that the SSAS process itself (msmdsrv.exe) can consume.

Once memory usage hits the Low limit, memory cleaner threads will kick in and start moving data out of memory in a relatively non-aggressive fashion. If memory hits the Total limit, the cleaner goes into crisis mode… it spawns additional threads and gets much more aggressive about memory cleanup, and this can dramatically impact performance.

See Also:

http://greg.blogs.sqlsentry.net/2009/06/analysis-services-memory-limits.html

Version: 0

The IOProcess thread pool separates reads from other activities. If the I/O job queue length is consistently above 0, you may be experiencing an IO bottleneck.

See the Analysis Services MOLAP Guide for SQL Server 2012 and 2014 for more information, including optimizing for NUMA.

Version: 0

Depending on your version of SSAS, queuing of jobs in this pool can be related to all Storage Engine activity (SSAS 2005 to 2008R2), or strictly processing activity in SSAS 2012 and above.

See the appropriate Microsoft SSAS Performance Guide for more details on optimizing this activity for your version of SSAS.

Version: 0

If Cache Evictions/sec or Memory : KB shrunk/sec are consistently above 0, you likely have memory pressure on the SSAS instance. This is often seen when SSAS memory usage exceeds configured limits.

Version: 0

A sustained value above zero indicates an inability for users to successfully connect to SSAS. This could be related to overburdened resources on the server.

Version: 1

This condition queries the dbo.suspect_pages table located in the MSDB database. If there are any pages with a status of 1, 2, or 3 then it will evaluate to True, advising that there is potential corruption in databases on the server being queried.

See Also:

http://blogs.sqlsentry.com/johnmartin/monitoring-for-suspect-pages/

SELECT db_name(database_id) as databaseName, count(*) as corruptPages
FROM dbo.suspect_pages
WHERE event_type in (1,2,3)
GROUP BY database_id;

Version: 1

This condition counts the number of rows present in the dbo.suspect_pages table in the MSDB database. When the total count of rows in the table is greater than or equal to 900, it will evaluate to True. This is because the dbo.suspect_pages table is only allowed a maximum of 1,000 rows before it fills, at which point any new suspect pages that are detected will not have information logged about them.

See Also:

http://blogs.sqlsentry.com/johnmartin/monitoring-for-suspect-pages/

SELECT count(*) as suspectPageCount
FROM dbo.suspect_pages;

Version: 1

This condition will query dbo.suspect_pages in the MSDB database, checking the number of fixed pages (event types 4, 5, and 7) per-database. This condition will evaluate to True if the number of fixed pages has increased since the last check.

If this condition is True, then it can mean that DBCC CHECKDB has corrected or deallocated pages. Furthermore, if Enterprise Edition is in use and the database is in either an Availability Group or Mirroring session, it could indicate that pages were fixed via Automatic Page Repair, alerting you to a potential corruption issue you might not have otherwise observed.

See Also:

http://blogs.sqlsentry.com/johnmartin/monitoring-for-suspect-pages/

SELECT db_name(database_id) as databaseName, count(*) as fixedPages
FROM dbo.suspect_pages
WHERE event_type in (4,5,7)
GROUP BY database_id;

Version: 1

This condition evaluates to True when there are more data files than logical cores in use by SQL Server, or there are more than 4 logical cores in use and fewer than 4 tempdb data files.

As a general rule, if the number of logical processors is <= 8, use the same number of data files as logical processors. If the number of logical processors is > 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.

See Also:

https://support.microsoft.com/en-us/kb/2154845

http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/

http://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx

DECLARE @TempDBFiles TinyInt
DECLARE @CPUCount TinyInt

SELECT @CPUCount = COUNT(*) 
FROM SYS.DM_OS_SCHEDULERS 
WHERE STATUS = N'VISIBLE ONLINE' AND IS_ONLINE = 1;

SELECT @TempDBFiles = COUNT(*) 
FROM MASTER.SYS.MASTER_FILES
WHERE database_id = 2
AND file_id <> 2;

SELECT 
 CASE
  WHEN @TempDBFiles = @CPUCount THEN 0
  /* If there are more data files than CPU Cores then this may cause overhead */
  WHEN @TempDBFiles > @CPUCount THEN 1 
  /* If there are 4 or more cores and less than 4 TempDB Data files there could be contention */
  WHEN @CPUCount >= 4 AND @TempDBFiles < 4 THEN 2
  ELSE 0
 END [Contention];

Version: 1

This condition evaluates to True when less than 10% of tempdb's page count is unallocated. This may indicate that tempdb is running out of space and will soon experience autogrowth or hit a size limit.

See Also:

https://msdn.microsoft.com/en-us/library/ms174412.aspx

SELECT (CONVERT(DECIMAL(18,0),(SUM(unallocated_extent_page_count)))/(SUM(total_page_count))*100) as percent_used 
FROM tempdb.sys.dm_db_file_space_usage;

Version: 1

The benefits of having multiple tempdb files can be lost if one of those files grows larger than the other files. This condition will evaulate to True if your tempdb files are not the same size.

The query counts the distinct tempdb file sizes from sys.master_files.

See Also:

http://blogs.sqlsentry.com/aaronbertrand/sql-server-2016-tempdb-fixes/

http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/

SELECT count(distinct size) as unique_sizes 
FROM sys.master_files
WHERE database_id = 2
AND type_desc <> 'LOG';

Version: 1

Checks that you have one tempdb file per CPU core, up to 8 files. As with most SQL Server topics, an “it depends” caveat accompanies tempdb recommendations, however, starting with SQL Server 2016, Microsoft creates a default number of eight Tempdb files. It uses the number of cores if there are fewer than eight cores or just one file it is SQL Server Express.

The first query counts the visible online CPUs from sys.dm_os_schedulers and the second query counts the number of tempdb files from sys.master_files. This condition evaluates to True when the count of CPUs is not equal to the count of tempdb files.

“As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.” (source: http://blogs.sqlsentry.com/aaronbertrand/sql-server-2016-tempdb-fixes/)

See Also:

https://msdn.microsoft.com/en-us/library/ms144259(v=sql.130).aspx

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

SELECT count(cpu_id) as cpu_count
FROM sys.dm_os_schedulers 
WHERE status = 'VISIBLE ONLINE';
SELECT count(Name) as tempdb_files
FROM sys.master_files 
WHERE database_id = 2
AND type_desc <> 'LOG';

Version: 1

If you have a large number of cores, you will generally set the number of tempdb files as ¼ to ½ the number of cores. This condition will verify that you have at least the ¼ ratio. This means if you have 32 cores and 8 files it will evaluate to False, but if there are more than 32 cores and only the 8 default tempdb files, then it will evaluate to True.

The first query counts the visible online CPUs from sys.dm_os_schedulers and the second query counts the number of tempdb files from sys.master_files.

You may wish to customize this based on contention or performance issues you have seen in your environments. Keep in mind that Microsoft recommends adding files in multiples of 4, so you would likely want to go from 8 to 12, 16, 20, etc.

See Also:

http://blogs.sqlsentry.com/aaronbertrand/sql-server-2016-tempdb-fixes/

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

SELECT count(cpu_id) as cpu_count
FROM sys.dm_os_schedulers 
WHERE status = 'VISIBLE ONLINE';
SELECT count(Name) as tempdb_files
FROM sys.master_files 
WHERE database_id = 2
AND type_desc <> 'LOG';

Version: 1

This condition evaluates to True when the number of trace flags set to ON (status = 1) has changed.

The query analyzes the results of DBCC TRACESTATUS WITH NO_INFOMSGS to determine the status of the trace flags.

CREATE TABLE [#TraceFlagsOn] 
      ([TraceFlag] INT, [Status] INT, [Global] INT, [Session] INT);

INSERT INTO #TraceFlagsOn EXEC ('DBCC TRACESTATUS WITH NO_INFOMSGS');

SELECT Count(*) as TraceFlagsStatusOn
FROM #TraceFlagsOn
WHERE #TraceFlagsOn.Status = 1;

DROP TABLE [#TraceFlagsOn];

Version: 1

This condition evaluates to True when the number of trace flags enabled on the system has changed.

The query analyzes the results of DBCC TRACESTATUS WITH NO_INFOMSGS to determine the status of the trace flags.

CREATE TABLE [#TraceFlags] 
      ([TraceFlag] INT, [Status] INT, [Global] INT, [Session] INT);

INSERT INTO #TraceFlags EXEC ('DBCC TRACESTATUS WITH NO_INFOMSGS');

SELECT Count(*) as TraceFlagsTotal
FROM #TraceFlags;

DROP TABLE [#TraceFlags];

Version: 1

This VMware-specific condition evaluates to True when a significant amount of memory has been reclaimed by the host machine from the guest virtual machine(s).

See Also:

https://www.vmware.com/files/pdf/perf-vsphere-memory_management.pdf

Version: 1

This condition evaluates to True when the VMware Co-Stop time value is greater than 3%.

This is the time that a virtual machine is ready to run, but unable to run due to co-scheduling constraints. May be indicative of too many vCPU resources.

See Also:

https://www.vmware.com/support/developer/converter-sdk/conv60_apireference/cpu_counters.html

Version: 1

This VMware-specific condition evaluates to True when Ready Time % per vCPU is critically high.

When a vCPU is ready to do work, but is waiting for the hypervisor to schedule that work on one or more physical CPUs, the vCPU accumulates Ready Time. This is often caused by having a high ratio of vCPUs to physical CPUs on the host machine or having VMs of significantly different sizes (by vCPU count) on the same host.

See Also:

https://www.sqlskills.com/blogs/jonathan/cpu-ready-time-in-vmware-and-how-to-interpret-its-real-meaning/

Version: 1

This VMware-specific condition evaluates to True when Ready Time % per vCPU is within the warning threshold.

When a vCPU is ready to do work, but is waiting for the hypervisor to schedule that work on one or more physical CPUs, the vCPU accumulates Ready Time. This is often caused by having a high ratio of vCPUs to physical CPUs on the host machine or having VMs of significantly different sizes (by vCPU count) on the same host.

See Also:

https://www.sqlskills.com/blogs/jonathan/cpu-ready-time-in-vmware-and-how-to-interpret-its-real-meaning/

Version: 3

This condition queries the SQL Server ring buffer for recent occurrences of system-level RESOURCE_MEMPHYSICAL_LOW alerts. These occur when Windows signals that physical memory is low, and can cause SQL Server to release its memory in response.

See Also:

How It Works: What are the RING_BUFFER_RESOURCE_MONITOR telling me?

Using sys.dm_os_ring_buffers To Diagnose Memory Issues in SQL Server

SELECT COUNT(*) AS AlertCount
FROM (
	    SELECT CAST(orb.record AS XML) AS xmlRec
	    FROM sys.dm_os_ring_buffers AS orb
	    CROSS JOIN sys.dm_os_sys_info AS osi
	    WHERE orb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
		    AND DATEADD(second, -((osi.cpu_ticks/(osi.cpu_ticks/osi.ms_ticks) - orb.timestamp) / 1000), 
        GETDATE()) > DATEADD(minute, -6, GETDATE())
	    ) rb
CROSS APPLY rb.xmlRec.nodes('Record') rec(x)
WHERE rec.x.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') = 2
 

LOOKS LIKE YOUR BROWSER IS OUT OF DATE

For your convenience here are links to supported browsers:

Your browser could be incompatible with this website.
If you close this window and continue you may experience less than optimal behavior.

Don't show this again