Recently, I blogged about the Top 10 SQL Server Performance Tuning Tips, the things we typically see when we healthcheck SQL Servers here at Sequallity. Now, we have developed a simple script to find the top 10 SQL Server Performance Problems.

T-SQL Select GoFaster From SQLServer

The script can take between 20 seconds and 42 minutes to run, depending on the number of databases present on the SQL server. It looks at the usual suspects with poor SQL performance. It has been coded for low impact but there are a couple of things to talk about here…

Firstly, it checks for blocking. Over 20 seconds it runs 10 queries finding the number of blocks. If you have a high number of blocks or executing queries, this may take longer than 20 seconds. Run this on a test system first.

Secondly, it shells out to dos to find the Power Options settings and enables show advanced options. You may want to edit this part out. It’s a clever piece of code, sourced from Randolph West’s blog, Balanced Power Saving T-SQL script. What I love about this self-aware piece of SQL code is that is remembers the starting settings and sets them back.

This is specifically for on-premises SQL Server, virtual SQL Servers and SQL virtual machines. With this I wanted a quick sanity check on key settings and configuration settings for SQL Server, with a quick explanation of the issues. As a consultant, I have limited time to make the most impact and with scripts like this, I can pinpoint the problems immediately.

Please check our warranty page before running this query.

-- SQL Points - Top 10 Performance Points on SQL Server
-- Sequallity Limited - the SQL Consultancy, where SQL and Quality join!
-- V1.1
USE tempdb;
 
DECLARE @servername NVARCHAR(255) 
SET @servername = @@servername;
 
IF object_id('tempdb..#top10') IS NOT NULL
	DROP TABLE #top10;
 
CREATE TABLE #top10 (
	Top10ID INT
	,Point VARCHAR(255)
	,PointInstance VARCHAR(255)
	,PointDatabase VARCHAR(255)
	,PointDesc VARCHAR(1024)
	,PointGoodorBad VARCHAR(1024)
	);
 
IF object_id('tempdb..#fixedDrives') IS NOT NULL
	DROP TABLE #fixedDrives;
 
CREATE TABLE #fixedDrives (
	Drive VARCHAR(255)
	,MBFree BIGINT
	);
 
INSERT INTO #fixedDrives
EXECUTE xp_fixedDrives;
 
-- SQL Points 0 -- Server Killer No Space
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	,PointGoodorBad
	)
SELECT 0
	,'Windows Server Status - Space'
	,@servername
	,''
	,Drive + ':/' + cast(MBFree AS VARCHAR) + ' MB'
	,CASE 
		WHEN MBFree < 1000
			THEN 'Bad: Less Than 10GB'
		ELSE 'Good: Free Space > 10GB'
		END
FROM #fixedDrives
 
IF object_id('tempdb..#fixedDrives') IS NOT NULL
	DROP TABLE #fixedDrives;
 
-- SQL Points 1 -- Missing Indexes
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	,PointGoodorBad
	)
SELECT 1
	,'SQL Status - Missing Indexes'
	,@servername
	,db_name(database_id)
	,cast(count(*) AS NVARCHAR)
	,'Bad: Missing Indexes'
FROM sys.dm_db_missing_index_group_stats AS a WITH (NOLOCK)
JOIN sys.dm_db_missing_index_groups AS b WITH (NOLOCK) ON a.group_handle = b.index_group_handle
JOIN sys.dm_db_missing_index_details AS c WITH (NOLOCK) ON b.index_handle = c.index_handle
WHERE (user_seeks + user_scans) * (avg_total_user_cost * 100) * avg_user_impact >= 1--5000000
GROUP BY db_name(database_id)
 
-- SQL Points 2 -- Maintenance Statistics
IF object_id('tempdb..statsdates') IS NOT NULL
	DROP TABLE statsdates
 
CREATE TABLE tempdb..statsdates (
	Database_name VARCHAR(255)
	,LastUpdated DATETIME
	,RowModCtr INT
	)
 
EXEC sp_msforeachdb 'use ?;insert into tempdb..statsdates (Database_name,LastUpdated,RowModCtr) select Db_name(),cast(stats_date(o.object_id,i.index_id) as date) , si.rowmodctr from sys.indexes i with (nolock) join sys.objects o on i.object_id = o.object_id
join sys.sysindexes si on i.object_id = si.id and i.index_id = si.indid
where o.type = ''U'' and i.type_desc <> ''HEAP'' and si.rowmodctr <> 0'
 
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc

	,PointGoodorBad
	)
SELECT 2
	,'Maintenance - Statistics'
	,@servername
	,Database_name
	,cast(count(*) AS VARCHAR) + ' table(s) affected'
	,'Bad: Out-of-date Statistics - Stale over 14 days'
FROM statsdates
WHERE LastUpdated IS NOT NULL
	AND LastUpdated < dateadd(d, - 14, getdate())
GROUP BY Database_name
 
IF object_id('tempdb..statsdates') IS NOT NULL
	DROP TABLE statsdates
 
-- SQL Points 3 -- Server Configuration - Memory
INSERT INTO #top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	,PointGoodorBad
	)
SELECT 3
	,'Server Configuration - Memory'
	,@servername
	,''
	,cast(total_physical_memory_kb / 1024. AS NUMERIC(20, 2))
	,CASE 
		WHEN total_physical_memory_kb / 1024. < 16000
			THEN 'Bad: Less than 16GB'
		ELSE 'Good: Greater than 16GB'
		END
FROM sys.dm_os_sys_memory WITH (NOLOCK);
 
INSERT INTO #top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	,PointGoodorBad
	)
SELECT 3
	,'SQL Configuration - Minimum Memory'
	,@servername
	,''
	,cast(Value_in_use AS NVARCHAR)
	,CASE 
	WHEN Value_in_use in(16,0)
		THEN 'OK:Default Max Memory'
	ELSE 'OK:Changed Max Memory'
	END
FROM sys.configurations WITH (NOLOCK)
WHERE name = 'min server memory (MB)';

INSERT INTO #top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	,PointGoodorBad
	)
SELECT 3
	,'SQL Configuration - Maximum Memory'
	,@servername
	,''
	,cast(Value_in_use AS NVARCHAR)
	,CASE 
		WHEN Value_in_use = 2147483647
			THEN 'Bad: Default Max Memory'
		ELSE 'Good: Changed Max Memory'
		END
FROM sys.configurations WITH (NOLOCK)
WHERE name = 'max server memory (MB)';

IF (Select PointDesc from #top10 where Point = 'SQL Configuration - Minimum Memory')
= (Select PointDesc from #top10 where Point = 'SQL Configuration - Maximum Memory')
update #top10 set PointGoodorBad = 'Bad: Min Memory matches Max Memory'
where Point  = 'SQL Configuration - Minimum Memory'
  
-- SQL Points 4 -- Server Configuration - Parallelism
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	)
SELECT 4
	,'SQL Configuration - Advanced CTFP'
	,@servername
	,''
	,cast(Value_in_use AS NVARCHAR)
FROM sys.configurations WITH (NOLOCK)
WHERE name = 'cost threshold for parallelism'
 
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	)
SELECT 4
	,'SQL Configuration - Advanced MaxDOP'
	,@servername
	,''
	,cast(Value_in_use AS NVARCHAR)
FROM sys.configurations WITH (NOLOCK)
WHERE name = 'max degree of parallelism'
 
UPDATE #top10
SET PointGoodorBad = CASE 
		WHEN PointDesc = '5'
			THEN 'Bad: Default CTFP'
		ELSE 'Good: Changed CTFP'
		END
WHERE Point = 'SQL Configuration - Advanced CTFP'
 
UPDATE #top10
SET PointGoodorBad = CASE 
		WHEN PointDesc = '0'
			THEN 'Bad: Default MaxDop'
		ELSE 'Good: Changed MaxDop'
		END
WHERE Point = 'SQL Configuration - Advanced MaxDOP'
 
-- SQL Points 5 -- Maintenance - Reindexing
IF object_id('tempdb..fragmented') IS NOT NULL
	DROP TABLE fragmented
 
CREATE TABLE tempdb..fragmented (
	Database_name VARCHAR(255)
	,object_id BIGINT
	,index_type_desc VARCHAR(255)
	,avg_fragmentation_in_percent NUMERIC(10, 2)
	)
 
EXEC sp_msforeachdb 'use ?;insert into tempdb..fragmented (Database_name,object_id,index_type_desc,avg_fragmentation_in_percent) 
select Db_name(), object_id, index_type_desc, avg_fragmentation_in_percent from 
sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,null)
where avg_fragmentation_in_percent > = 20'
 
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	,PointGoodorBad
	)
SELECT 5
	,'Maintenance - Fragmentation'
	,@servername
	,Database_name
	,index_type_desc + ': ' + cast(count(1) AS VARCHAR) + ' table(s) affected : Average = ' + cast(cast(avg(avg_fragmentation_in_percent) AS NUMERIC(20, 2)) AS VARCHAR) + '%'
	,'Bad: Internally Fragmented Tables'
FROM fragmented
GROUP BY Database_name
	,index_type_desc
ORDER BY index_type_desc
	,Database_name
 
IF object_id('tempdb..fragmented') IS NOT NULL
	DROP TABLE fragmented
 
-- SQL Points 6 - Server Status - Power Options
IF object_id('tempdb..#output') IS NOT NULL
	DROP TABLE #output
 
DECLARE @XPCmdShell TINYINT
DECLARE @ShowAdvancedOptions TINYINT
 
SELECT @XPCmdShell = CAST(value AS TINYINT)
FROM sys.configurations WITH (NOLOCK)
WHERE name = 'xp_cmdshell';
 
SELECT @ShowAdvancedOptions = CAST(value AS TINYINT)
FROM sys.configurations WITH (NOLOCK)
WHERE name = 'show advanced options';
 
CREATE TABLE #Output (PowerOutput NVARCHAR(4000))
 
IF @ShowAdvancedOptions = 0
BEGIN
	EXEC sp_configure 'show advanced options'
		,1;
 
	RECONFIGURE
	WITH OVERRIDE;
END;
 
IF @XPCmdShell = 0
BEGIN
	EXEC sp_configure 'xp_cmdshell'
		,1;
 
	RECONFIGURE
	WITH OVERRIDE;
END;
 
--Run xp_cmdshell to get power settings
INSERT INTO #Output (PowerOutput)
EXEC xp_cmdshell 'powercfg /list';
 
--Turn off 'xp_cmdshell'
IF @XPCmdShell = 0
BEGIN
	EXEC sp_configure 'xp_cmdshell'
		,0;
 
	RECONFIGURE
	WITH OVERRIDE;
END;
 
IF @ShowAdvancedOptions = 0
BEGIN
	EXEC sp_configure 'show advanced options'
		,0;
 
	RECONFIGURE
	WITH OVERRIDE;
END;
 
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	)
SELECT 6
	,'Server Status - Power Options'
	,@servername
	,''
	,'Power Options: ' + PowerOutput
FROM #Output
WHERE poweroutput LIKE '%*%'
	AND poweroutput NOT LIKE '%Active%'
 
UPDATE #Top10
SET PointGoodorBad = 'Bad: Power Saving Mode'
WHERE (
		PointDesc LIKE '%a1841308-3541-4fab-bc81-f71556f20b4a%'
		OR PointDesc LIKE '%saving%'
		)
	AND Top10ID = 6
 
UPDATE #Top10
SET PointGoodorBad = 'Bad: Balanced Mode'
WHERE (
		PointDesc LIKE '%381b4222-f694-41f0-9685-ff5bb260df2e%'
		OR PointDesc LIKE '%balanced%'
		)
	AND Top10ID = 6
 
UPDATE #Top10
SET PointGoodorBad = 'Good: High Performance Mode'
WHERE (
		PointDesc LIKE '%8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c%'
		OR PointDesc LIKE '%high%'
		)
	AND Top10ID = 6
 
UPDATE #Top10
SET PointGoodorBad = 'Good: Ultra Performance Mode'
WHERE (
		PointDesc LIKE '%e9a42b02-d5df-448d-aa00-03f14749eb61%'
		OR PointDesc LIKE '%ultra%'
		)
	AND Top10ID = 6
 
UPDATE #Top10
SET PointGoodorBad = 'Good: Custom Power Mode'
WHERE PointGoodorBad IS NULL
	AND Top10ID = 6
 
IF object_id('tempdb..#output') IS NOT NULL
	DROP TABLE #output
 
-- SQL Points 7 - Server Configuration - TempDB
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	)
SELECT 7
	,'SQL Config TempDB # Data Files'
	,@servername
	,'TempDB'
	,count(1)
FROM tempdb.dbo.sysfiles WITH (NOLOCK)
WHERE groupid = 1
 
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	)
SELECT 7
	,'SQL Config TempDB # Log Files'
	,@servername
	,'TempDB'
	,count(1)
FROM tempdb.dbo.sysfiles WITH (NOLOCK)
WHERE groupid = 0
 
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	)
SELECT 7
	,'SQL Config TempDB Data files same size'
	,@servername
	,'TempDB'
	,count(1)
FROM (
	SELECT growth
	FROM tempdb.dbo.sysfiles WITH (NOLOCK)
	WHERE groupid = 1
	GROUP BY growth
	) a
 
UPDATE #top10
SET PointGoodorBad = CASE 
		WHEN PointDesc BETWEEN 4
				AND 8
			THEN 'Good: Optimal # TempDB Files'
		ELSE 'Bad: TempDB # Files Needs Investigating'
		END
WHERE Point = 'SQL Config TempDB # Data Files'
 
UPDATE #top10
SET PointGoodorBad = CASE 
		WHEN PointDesc = '1'
			THEN 'Good: Optimal Log File'
		ELSE 'Bad: No Need for multiple Log Files'
		END
WHERE Point = 'SQL Config TempDB # Log Files'
 
UPDATE #top10
SET PointGoodorBad = CASE 
		WHEN PointDesc = '1'
			THEN 'Good: Optimal TempDB Files'
		ELSE 'Bad: Odd-Sized TempDB Data Files'
		END
WHERE Point = 'SQL Config TempDB Data files same size'
 
-- SQL Points 8 - Server Status - Blocking
IF object_id('tempdb..#countblocks') IS NOT NULL
	DROP TABLE #countblocks
 
DECLARE @counter INT = 1
 
CREATE TABLE #countblocks (blockcnt INT)
 
WHILE @counter <= 10
BEGIN
	INSERT INTO #countblocks (blockcnt)
	SELECT count(1)
	FROM sys.dm_exec_requests WITH (NOLOCK)
	WHERE blocking_session_id <> 0
 
	SET @counter = @counter + 1
 
	WAITFOR DELAY '00:00:02'
END
 
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	,PointGoodorBad
	)
SELECT 8
	,'Server Status - Blocking'
	,@servername
	,''
	,cast(cast(avg(blockcnt) AS NUMERIC(10, 4)) AS NVARCHAR) + ' average blocks in the last 20 seconds'
	,CASE 
		WHEN avg(blockcnt) = 0
			THEN 'Good: No Blocking'
		WHEN avg(blockcnt) < 0.5
			THEN 'Good: Low Blocking'
		ELSE 'Bad: High Blocking'
		END
FROM #countblocks
 
UPDATE #top10
SET PointGoodorBad = CASE 
		WHEN PointDesc = '1'
			THEN 'Good: Optimal TempDB Files'
		ELSE 'Bad: Odd-Sized TempDB Data Files'
		END
WHERE Point = 'SQL Config TempDB Data files same size'
 
IF object_id('tempdb..#countblocks') IS NOT NULL
	DROP TABLE #countblocks
 
-- SQL Points 9 - Server Status - Deadlocks
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	,PointGoodorBad
	)
SELECT 9
	,'Server Status Deadlocks'
	,@servername
	,''
	,'Restart = ' + cast(sqlserver_start_time AS VARCHAR) + ': Deadlocks :' + cast(cntr_Value AS VARCHAR) + ': Per Day DLs : ' + CASE 
		WHEN cntr_Value = 0
			THEN '0.00'
		ELSE cast(cast(cntr_value * 1.0 / (datediff(d, sqlserver_start_time, getdate()) + 1) AS NUMERIC(10, 2)) AS VARCHAR)
		END
	,CASE 
		WHEN cntr_Value = 0
			THEN 'Good: No Deadlocks'
		ELSE 'Bad: Per Day Deadlocks'
		END
FROM sys.dm_os_sys_info WITH (NOLOCK)
CROSS APPLY sys.dm_os_performance_counters WITH (NOLOCK)
WHERE counter_name LIKE '%dead%'
	AND instance_name = '_total'
 
-- SQL Points 10 - Missing Statistics
IF object_id('tempdb..spcreatestats') IS NOT NULL
	DROP TABLE spcreatestats
 
CREATE TABLE tempdb.dbo.spcreatestats (
	Database_name VARCHAR(255)
	,object_id BIGINT
	)
 
EXEC sp_msforeachdb 'use ?;insert into tempdb..spcreatestats (Database_name,object_id)
select Db_name(), object_id from sys.stats with (nolock) where user_created = 1'
 
INSERT INTO #Top10 (
	Top10ID
	,Point
	,PointInstance
	,PointDatabase
	,PointDesc
	,PointGoodorBad
	)
SELECT 10
	,'Missing Statistics'
	,@servername
	,sys.databases.name
	,'# of user created stats = ' + cast(count(1) - 1 AS VARCHAR)
	,CASE 
		WHEN count(1) - 1 = 0
			THEN 'Bad: No sp_Createstats Ran'
		ELSE 'Good: sp_Createstats ran probably'
		END
FROM sys.databases WITH (NOLOCK)
LEFT JOIN spcreatestats ON spcreatestats.Database_name = sys.databases.name
WHERE sys.databases.name NOT IN ('TempDB')
GROUP BY sys.databases.name
ORDER BY sys.databases.name
 
IF object_id('tempdb..spcreatestats') IS NOT NULL
	DROP TABLE spcreatestats
 
SELECT *
FROM #top10;
 
IF object_id('tempdb..#top10') IS NOT NULL
	DROP TABLE #top10;

Find SQL Performance Problems Version History

V1-0 – May 14th 2020

OK, so this was the first release. In IT I’ve always found it easier to progress something when it’s built so I got it done and I can keep improving it over time.

V1-1 – June 17th 2020

I noticed the performance checking script didn’t do anything with minimum memory settings on SQL Server. So I added something a check so if the Max Memory matches the Min Memory to report on this.

I also fixed problems whereby the Out-of-Date statistics was picking up tables that couldn’t have their statistics updated, and changed it to 14 days and rowmodctr >0 for stale statistics.

I also added more descriptive information to some of the GoodorBadPoints. I run this to quickly find config issues when I run a Sequallity SQL Server Health Check. I’d like to expand it to include additional performance topics to it.

Leave a Reply