{"id":937,"date":"2020-06-16T10:03:00","date_gmt":"2020-06-16T10:03:00","guid":{"rendered":"https:\/\/www.sequallity.co.uk\/?p=937"},"modified":"2020-06-17T09:20:55","modified_gmt":"2020-06-17T09:20:55","slug":"find-top-10-sql-server-performance-problems","status":"publish","type":"post","link":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/","title":{"rendered":"Find Top 10 SQL Server Performance Problems"},"content":{"rendered":"\n<p>Recently, I blogged about the <a href=\"https:\/\/www.sequallity.co.uk\/top-10-sql-server-performance-tuning-tips\/\"><strong><em>Top 10 SQL Server Performance Tuning Tips<\/em><\/strong>,<\/a> the things we typically see when we <a href=\"https:\/\/www.sequallity.co.uk\/sql-server-health-check\/\"><strong><em>healthcheck SQL Servers<\/em><\/strong><\/a> here at <strong><em><a href=\"https:\/\/www.sequallity.co.uk\">Sequallity<\/a><\/em><\/strong>. Now, we have developed a simple script to find the top 10 SQL Server Performance Problems.<\/p>\n\n\n\n<div class=\"wp-block-image is-style-default\"><figure class=\"aligncenter size-large is-resized\"><img decoding=\"async\" data-src=\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/06\/T-SQL-Select-GoFaster.png\" alt=\"T-SQL Select GoFaster From SQLServer\" class=\"wp-image-1040 lazyload\" width=\"607\" height=\"111\" data-srcset=\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/06\/T-SQL-Select-GoFaster.png 464w, https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/06\/T-SQL-Select-GoFaster-300x55.png 300w\" data-sizes=\"(max-width: 607px) 100vw, 607px\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" style=\"--smush-placeholder-width: 607px; --smush-placeholder-aspect-ratio: 607\/111;\" \/><\/figure><\/div>\n\n\n\n<p>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&#8230;<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>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&#8217;s a clever piece of code, sourced from Randolph West&#8217;s blog, <a href=\"https:\/\/bornsql.ca\/blog\/balanced-power-saving-t-sql-script\/\"><strong><em>Balanced Power Saving T-SQL script<\/em><\/strong><\/a>. What I love about this self-aware piece of SQL code is that is remembers the starting settings and sets them back.<\/p>\n\n\n\n<p>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. <\/p>\n\n\n\n<p>Please check our <a href=\"https:\/\/www.sequallity.co.uk\/sql-script-information-warranty\/\"><strong><em>warranty<\/em><\/strong><\/a> page before running this query.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- SQL Points - Top 10 Performance Points on SQL Server\n-- Sequallity Limited - the SQL Consultancy, where SQL and Quality join!\n-- V1.1\nUSE tempdb;\n \nDECLARE @servername NVARCHAR(255) \nSET @servername = @@servername;\n \nIF object_id('tempdb..#top10') IS NOT NULL\n\tDROP TABLE #top10;\n \nCREATE TABLE #top10 (\n\tTop10ID INT\n\t,Point VARCHAR(255)\n\t,PointInstance VARCHAR(255)\n\t,PointDatabase VARCHAR(255)\n\t,PointDesc VARCHAR(1024)\n\t,PointGoodorBad VARCHAR(1024)\n\t);\n \nIF object_id('tempdb..#fixedDrives') IS NOT NULL\n\tDROP TABLE #fixedDrives;\n \nCREATE TABLE #fixedDrives (\n\tDrive VARCHAR(255)\n\t,MBFree BIGINT\n\t);\n \nINSERT INTO #fixedDrives\nEXECUTE xp_fixedDrives;\n \n-- SQL Points 0 -- Server Killer No Space\nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t,PointGoodorBad\n\t)\nSELECT 0\n\t,'Windows Server Status - Space'\n\t,@servername\n\t,''\n\t,Drive + ':\/' + cast(MBFree AS VARCHAR) + ' MB'\n\t,CASE \n\t\tWHEN MBFree &lt; 1000\n\t\t\tTHEN 'Bad: Less Than 10GB'\n\t\tELSE 'Good: Free Space > 10GB'\n\t\tEND\nFROM #fixedDrives\n \nIF object_id('tempdb..#fixedDrives') IS NOT NULL\n\tDROP TABLE #fixedDrives;\n \n-- SQL Points 1 -- Missing Indexes\nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t,PointGoodorBad\n\t)\nSELECT 1\n\t,'SQL Status - Missing Indexes'\n\t,@servername\n\t,db_name(database_id)\n\t,cast(count(*) AS NVARCHAR)\n\t,'Bad: Missing Indexes'\nFROM sys.dm_db_missing_index_group_stats AS a WITH (NOLOCK)\nJOIN sys.dm_db_missing_index_groups AS b WITH (NOLOCK) ON a.group_handle = b.index_group_handle\nJOIN sys.dm_db_missing_index_details AS c WITH (NOLOCK) ON b.index_handle = c.index_handle\nWHERE (user_seeks + user_scans) * (avg_total_user_cost * 100) * avg_user_impact >= 1--5000000\nGROUP BY db_name(database_id)\n \n-- SQL Points 2 -- Maintenance Statistics\nIF object_id('tempdb..statsdates') IS NOT NULL\n\tDROP TABLE statsdates\n \nCREATE TABLE tempdb..statsdates (\n\tDatabase_name VARCHAR(255)\n\t,LastUpdated DATETIME\n\t,RowModCtr INT\n\t)\n \nEXEC 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\njoin sys.sysindexes si on i.object_id = si.id and i.index_id = si.indid\nwhere o.type = ''U'' and i.type_desc &lt;> ''HEAP'' and si.rowmodctr &lt;> 0'\n \nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\n\t,PointGoodorBad\n\t)\nSELECT 2\n\t,'Maintenance - Statistics'\n\t,@servername\n\t,Database_name\n\t,cast(count(*) AS VARCHAR) + ' table(s) affected'\n\t,'Bad: Out-of-date Statistics - Stale over 14 days'\nFROM statsdates\nWHERE LastUpdated IS NOT NULL\n\tAND LastUpdated &lt; dateadd(d, - 14, getdate())\nGROUP BY Database_name\n \nIF object_id('tempdb..statsdates') IS NOT NULL\n\tDROP TABLE statsdates\n \n-- SQL Points 3 -- Server Configuration - Memory\nINSERT INTO #top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t,PointGoodorBad\n\t)\nSELECT 3\n\t,'Server Configuration - Memory'\n\t,@servername\n\t,''\n\t,cast(total_physical_memory_kb \/ 1024. AS NUMERIC(20, 2))\n\t,CASE \n\t\tWHEN total_physical_memory_kb \/ 1024. &lt; 16000\n\t\t\tTHEN 'Bad: Less than 16GB'\n\t\tELSE 'Good: Greater than 16GB'\n\t\tEND\nFROM sys.dm_os_sys_memory WITH (NOLOCK);\n \nINSERT INTO #top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t,PointGoodorBad\n\t)\nSELECT 3\n\t,'SQL Configuration - Minimum Memory'\n\t,@servername\n\t,''\n\t,cast(Value_in_use AS NVARCHAR)\n\t,CASE \n\tWHEN Value_in_use in(16,0)\n\t\tTHEN 'OK:Default Max Memory'\n\tELSE 'OK:Changed Max Memory'\n\tEND\nFROM sys.configurations WITH (NOLOCK)\nWHERE name = 'min server memory (MB)';\n\nINSERT INTO #top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t,PointGoodorBad\n\t)\nSELECT 3\n\t,'SQL Configuration - Maximum Memory'\n\t,@servername\n\t,''\n\t,cast(Value_in_use AS NVARCHAR)\n\t,CASE \n\t\tWHEN Value_in_use = 2147483647\n\t\t\tTHEN 'Bad: Default Max Memory'\n\t\tELSE 'Good: Changed Max Memory'\n\t\tEND\nFROM sys.configurations WITH (NOLOCK)\nWHERE name = 'max server memory (MB)';\n\nIF (Select PointDesc from #top10 where Point = 'SQL Configuration - Minimum Memory')\n= (Select PointDesc from #top10 where Point = 'SQL Configuration - Maximum Memory')\nupdate #top10 set PointGoodorBad = 'Bad: Min Memory matches Max Memory'\nwhere Point  = 'SQL Configuration - Minimum Memory'\n  \n-- SQL Points 4 -- Server Configuration - Parallelism\nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t)\nSELECT 4\n\t,'SQL Configuration - Advanced CTFP'\n\t,@servername\n\t,''\n\t,cast(Value_in_use AS NVARCHAR)\nFROM sys.configurations WITH (NOLOCK)\nWHERE name = 'cost threshold for parallelism'\n \nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t)\nSELECT 4\n\t,'SQL Configuration - Advanced MaxDOP'\n\t,@servername\n\t,''\n\t,cast(Value_in_use AS NVARCHAR)\nFROM sys.configurations WITH (NOLOCK)\nWHERE name = 'max degree of parallelism'\n \nUPDATE #top10\nSET PointGoodorBad = CASE \n\t\tWHEN PointDesc = '5'\n\t\t\tTHEN 'Bad: Default CTFP'\n\t\tELSE 'Good: Changed CTFP'\n\t\tEND\nWHERE Point = 'SQL Configuration - Advanced CTFP'\n \nUPDATE #top10\nSET PointGoodorBad = CASE \n\t\tWHEN PointDesc = '0'\n\t\t\tTHEN 'Bad: Default MaxDop'\n\t\tELSE 'Good: Changed MaxDop'\n\t\tEND\nWHERE Point = 'SQL Configuration - Advanced MaxDOP'\n \n-- SQL Points 5 -- Maintenance - Reindexing\nIF object_id('tempdb..fragmented') IS NOT NULL\n\tDROP TABLE fragmented\n \nCREATE TABLE tempdb..fragmented (\n\tDatabase_name VARCHAR(255)\n\t,object_id BIGINT\n\t,index_type_desc VARCHAR(255)\n\t,avg_fragmentation_in_percent NUMERIC(10, 2)\n\t)\n \nEXEC sp_msforeachdb 'use ?;insert into tempdb..fragmented (Database_name,object_id,index_type_desc,avg_fragmentation_in_percent) \nselect Db_name(), object_id, index_type_desc, avg_fragmentation_in_percent from \nsys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL,null)\nwhere avg_fragmentation_in_percent > = 20'\n \nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t,PointGoodorBad\n\t)\nSELECT 5\n\t,'Maintenance - Fragmentation'\n\t,@servername\n\t,Database_name\n\t,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) + '%'\n\t,'Bad: Internally Fragmented Tables'\nFROM fragmented\nGROUP BY Database_name\n\t,index_type_desc\nORDER BY index_type_desc\n\t,Database_name\n \nIF object_id('tempdb..fragmented') IS NOT NULL\n\tDROP TABLE fragmented\n \n-- SQL Points 6 - Server Status - Power Options\nIF object_id('tempdb..#output') IS NOT NULL\n\tDROP TABLE #output\n \nDECLARE @XPCmdShell TINYINT\nDECLARE @ShowAdvancedOptions TINYINT\n \nSELECT @XPCmdShell = CAST(value AS TINYINT)\nFROM sys.configurations WITH (NOLOCK)\nWHERE name = 'xp_cmdshell';\n \nSELECT @ShowAdvancedOptions = CAST(value AS TINYINT)\nFROM sys.configurations WITH (NOLOCK)\nWHERE name = 'show advanced options';\n \nCREATE TABLE #Output (PowerOutput NVARCHAR(4000))\n \nIF @ShowAdvancedOptions = 0\nBEGIN\n\tEXEC sp_configure 'show advanced options'\n\t\t,1;\n \n\tRECONFIGURE\n\tWITH OVERRIDE;\nEND;\n \nIF @XPCmdShell = 0\nBEGIN\n\tEXEC sp_configure 'xp_cmdshell'\n\t\t,1;\n \n\tRECONFIGURE\n\tWITH OVERRIDE;\nEND;\n \n--Run xp_cmdshell to get power settings\nINSERT INTO #Output (PowerOutput)\nEXEC xp_cmdshell 'powercfg \/list';\n \n--Turn off 'xp_cmdshell'\nIF @XPCmdShell = 0\nBEGIN\n\tEXEC sp_configure 'xp_cmdshell'\n\t\t,0;\n \n\tRECONFIGURE\n\tWITH OVERRIDE;\nEND;\n \nIF @ShowAdvancedOptions = 0\nBEGIN\n\tEXEC sp_configure 'show advanced options'\n\t\t,0;\n \n\tRECONFIGURE\n\tWITH OVERRIDE;\nEND;\n \nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t)\nSELECT 6\n\t,'Server Status - Power Options'\n\t,@servername\n\t,''\n\t,'Power Options: ' + PowerOutput\nFROM #Output\nWHERE poweroutput LIKE '%*%'\n\tAND poweroutput NOT LIKE '%Active%'\n \nUPDATE #Top10\nSET PointGoodorBad = 'Bad: Power Saving Mode'\nWHERE (\n\t\tPointDesc LIKE '%a1841308-3541-4fab-bc81-f71556f20b4a%'\n\t\tOR PointDesc LIKE '%saving%'\n\t\t)\n\tAND Top10ID = 6\n \nUPDATE #Top10\nSET PointGoodorBad = 'Bad: Balanced Mode'\nWHERE (\n\t\tPointDesc LIKE '%381b4222-f694-41f0-9685-ff5bb260df2e%'\n\t\tOR PointDesc LIKE '%balanced%'\n\t\t)\n\tAND Top10ID = 6\n \nUPDATE #Top10\nSET PointGoodorBad = 'Good: High Performance Mode'\nWHERE (\n\t\tPointDesc LIKE '%8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c%'\n\t\tOR PointDesc LIKE '%high%'\n\t\t)\n\tAND Top10ID = 6\n \nUPDATE #Top10\nSET PointGoodorBad = 'Good: Ultra Performance Mode'\nWHERE (\n\t\tPointDesc LIKE '%e9a42b02-d5df-448d-aa00-03f14749eb61%'\n\t\tOR PointDesc LIKE '%ultra%'\n\t\t)\n\tAND Top10ID = 6\n \nUPDATE #Top10\nSET PointGoodorBad = 'Good: Custom Power Mode'\nWHERE PointGoodorBad IS NULL\n\tAND Top10ID = 6\n \nIF object_id('tempdb..#output') IS NOT NULL\n\tDROP TABLE #output\n \n-- SQL Points 7 - Server Configuration - TempDB\nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t)\nSELECT 7\n\t,'SQL Config TempDB # Data Files'\n\t,@servername\n\t,'TempDB'\n\t,count(1)\nFROM tempdb.dbo.sysfiles WITH (NOLOCK)\nWHERE groupid = 1\n \nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t)\nSELECT 7\n\t,'SQL Config TempDB # Log Files'\n\t,@servername\n\t,'TempDB'\n\t,count(1)\nFROM tempdb.dbo.sysfiles WITH (NOLOCK)\nWHERE groupid = 0\n \nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t)\nSELECT 7\n\t,'SQL Config TempDB Data files same size'\n\t,@servername\n\t,'TempDB'\n\t,count(1)\nFROM (\n\tSELECT growth\n\tFROM tempdb.dbo.sysfiles WITH (NOLOCK)\n\tWHERE groupid = 1\n\tGROUP BY growth\n\t) a\n \nUPDATE #top10\nSET PointGoodorBad = CASE \n\t\tWHEN PointDesc BETWEEN 4\n\t\t\t\tAND 8\n\t\t\tTHEN 'Good: Optimal # TempDB Files'\n\t\tELSE 'Bad: TempDB # Files Needs Investigating'\n\t\tEND\nWHERE Point = 'SQL Config TempDB # Data Files'\n \nUPDATE #top10\nSET PointGoodorBad = CASE \n\t\tWHEN PointDesc = '1'\n\t\t\tTHEN 'Good: Optimal Log File'\n\t\tELSE 'Bad: No Need for multiple Log Files'\n\t\tEND\nWHERE Point = 'SQL Config TempDB # Log Files'\n \nUPDATE #top10\nSET PointGoodorBad = CASE \n\t\tWHEN PointDesc = '1'\n\t\t\tTHEN 'Good: Optimal TempDB Files'\n\t\tELSE 'Bad: Odd-Sized TempDB Data Files'\n\t\tEND\nWHERE Point = 'SQL Config TempDB Data files same size'\n \n-- SQL Points 8 - Server Status - Blocking\nIF object_id('tempdb..#countblocks') IS NOT NULL\n\tDROP TABLE #countblocks\n \nDECLARE @counter INT = 1\n \nCREATE TABLE #countblocks (blockcnt INT)\n \nWHILE @counter &lt;= 10\nBEGIN\n\tINSERT INTO #countblocks (blockcnt)\n\tSELECT count(1)\n\tFROM sys.dm_exec_requests WITH (NOLOCK)\n\tWHERE blocking_session_id &lt;> 0\n \n\tSET @counter = @counter + 1\n \n\tWAITFOR DELAY '00:00:02'\nEND\n \nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t,PointGoodorBad\n\t)\nSELECT 8\n\t,'Server Status - Blocking'\n\t,@servername\n\t,''\n\t,cast(cast(avg(blockcnt) AS NUMERIC(10, 4)) AS NVARCHAR) + ' average blocks in the last 20 seconds'\n\t,CASE \n\t\tWHEN avg(blockcnt) = 0\n\t\t\tTHEN 'Good: No Blocking'\n\t\tWHEN avg(blockcnt) &lt; 0.5\n\t\t\tTHEN 'Good: Low Blocking'\n\t\tELSE 'Bad: High Blocking'\n\t\tEND\nFROM #countblocks\n \nUPDATE #top10\nSET PointGoodorBad = CASE \n\t\tWHEN PointDesc = '1'\n\t\t\tTHEN 'Good: Optimal TempDB Files'\n\t\tELSE 'Bad: Odd-Sized TempDB Data Files'\n\t\tEND\nWHERE Point = 'SQL Config TempDB Data files same size'\n \nIF object_id('tempdb..#countblocks') IS NOT NULL\n\tDROP TABLE #countblocks\n \n-- SQL Points 9 - Server Status - Deadlocks\nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t,PointGoodorBad\n\t)\nSELECT 9\n\t,'Server Status Deadlocks'\n\t,@servername\n\t,''\n\t,'Restart = ' + cast(sqlserver_start_time AS VARCHAR) + ': Deadlocks :' + cast(cntr_Value AS VARCHAR) + ': Per Day DLs : ' + CASE \n\t\tWHEN cntr_Value = 0\n\t\t\tTHEN '0.00'\n\t\tELSE cast(cast(cntr_value * 1.0 \/ (datediff(d, sqlserver_start_time, getdate()) + 1) AS NUMERIC(10, 2)) AS VARCHAR)\n\t\tEND\n\t,CASE \n\t\tWHEN cntr_Value = 0\n\t\t\tTHEN 'Good: No Deadlocks'\n\t\tELSE 'Bad: Per Day Deadlocks'\n\t\tEND\nFROM sys.dm_os_sys_info WITH (NOLOCK)\nCROSS APPLY sys.dm_os_performance_counters WITH (NOLOCK)\nWHERE counter_name LIKE '%dead%'\n\tAND instance_name = '_total'\n \n-- SQL Points 10 - Missing Statistics\nIF object_id('tempdb..spcreatestats') IS NOT NULL\n\tDROP TABLE spcreatestats\n \nCREATE TABLE tempdb.dbo.spcreatestats (\n\tDatabase_name VARCHAR(255)\n\t,object_id BIGINT\n\t)\n \nEXEC sp_msforeachdb 'use ?;insert into tempdb..spcreatestats (Database_name,object_id)\nselect Db_name(), object_id from sys.stats with (nolock) where user_created = 1'\n \nINSERT INTO #Top10 (\n\tTop10ID\n\t,Point\n\t,PointInstance\n\t,PointDatabase\n\t,PointDesc\n\t,PointGoodorBad\n\t)\nSELECT 10\n\t,'Missing Statistics'\n\t,@servername\n\t,sys.databases.name\n\t,'# of user created stats = ' + cast(count(1) - 1 AS VARCHAR)\n\t,CASE \n\t\tWHEN count(1) - 1 = 0\n\t\t\tTHEN 'Bad: No sp_Createstats Ran'\n\t\tELSE 'Good: sp_Createstats ran probably'\n\t\tEND\nFROM sys.databases WITH (NOLOCK)\nLEFT JOIN spcreatestats ON spcreatestats.Database_name = sys.databases.name\nWHERE sys.databases.name NOT IN ('TempDB')\nGROUP BY sys.databases.name\nORDER BY sys.databases.name\n \nIF object_id('tempdb..spcreatestats') IS NOT NULL\n\tDROP TABLE spcreatestats\n \nSELECT *\nFROM #top10;\n \nIF object_id('tempdb..#top10') IS NOT NULL\n\tDROP TABLE #top10;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Find SQL Performance Problems Version History<\/strong><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>V1-0 \u2013 May 14th 2020<\/strong><\/h3>\n\n\n\n<p>OK, so this was the first release. In IT I&#8217;ve always found it easier to progress something when it&#8217;s built so I got it done and I can keep improving it over time.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>V1-1 \u2013 June 17th 2020<\/strong><\/h3>\n\n\n\n<p>I noticed the performance checking script didn\u2019t 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.<\/p>\n\n\n\n<p>I also fixed problems whereby the Out-of-Date statistics was picking up tables that couldn&#8217;t have their statistics updated, and changed it to 14 days and rowmodctr >0 for stale statistics.<\/p>\n\n\n\n<p>I also added more descriptive information to some of the GoodorBadPoints. I run this to quickly find config issues when I run a <a href=\"https:\/\/www.sequallity.co.uk\"><strong><em>Sequallity<\/em><\/strong><\/a> <a href=\"https:\/\/www.sequallity.co.uk\/sql-server-health-check\"><strong><em>SQL Server Health Check<\/em><\/strong><\/a>. I&#8217;d like to expand it to include additional performance topics to it.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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. 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<\/p>\n<div class=\"read-more\"><a class=\"btn read-more-btn\" href=\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/\">Read More<\/a><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":true,"template":"","format":"standard","meta":{"footnotes":""},"categories":[542,715,755],"tags":[],"class_list":["post-937","post","type-post","status-publish","format-standard","hentry","category-sql-scripts","category-sql-server-performance-tuning","category-t-sql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v20.8 (Yoast SEO v20.8) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Find Top 10 SQL Server Performance Problems<\/title>\n<meta name=\"description\" content=\"Sequallity are a SQL Server Consultancy since 2005. Here is their SQL Server blog. All comments are opinions of the Authors.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Find Top 10 SQL Server Performance Problems\" \/>\n<meta property=\"og:description\" content=\"Sequallity are a SQL Server Consultancy since 2005. Here is their SQL Server blog. All comments are opinions of the Authors.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Server Consultancy and SQL Server Training\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/Sequallity-Ltd-437772140011198\/\" \/>\n<meta property=\"article:published_time\" content=\"2020-06-16T10:03:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-06-17T09:20:55+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/06\/T-SQL-Select-GoFaster.png\" \/>\n<meta name=\"author\" content=\"John Parker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sequallity\" \/>\n<meta name=\"twitter:site\" content=\"@sequallity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"John Parker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/\"},\"author\":{\"name\":\"John Parker\",\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#\/schema\/person\/a607249919447f52446059e05c32ae4f\"},\"headline\":\"Find Top 10 SQL Server Performance Problems\",\"datePublished\":\"2020-06-16T10:03:00+00:00\",\"dateModified\":\"2020-06-17T09:20:55+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/\"},\"wordCount\":422,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#organization\"},\"articleSection\":[\"SQL Scripts\",\"SQL Server Performance Tuning\",\"T-SQL\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/\",\"url\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/\",\"name\":\"Find Top 10 SQL Server Performance Problems\",\"isPartOf\":{\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#website\"},\"datePublished\":\"2020-06-16T10:03:00+00:00\",\"dateModified\":\"2020-06-17T09:20:55+00:00\",\"description\":\"Sequallity are a SQL Server Consultancy since 2005. Here is their SQL Server blog. All comments are opinions of the Authors.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Performance Tuning\",\"item\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/sql-server-performance-tuning\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Find Top 10 SQL Server Performance Problems\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#website\",\"url\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/\",\"name\":\"SQL Server Consultancy and SQL Server Training\",\"description\":\"where SQL and Quality join...\",\"publisher\":{\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-GB\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#organization\",\"name\":\"Sequallity Limited\",\"url\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2018\/09\/Sequality_Logo_Blue-Cyan-Blend_Large.jpg\",\"contentUrl\":\"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2018\/09\/Sequality_Logo_Blue-Cyan-Blend_Large.jpg\",\"width\":800,\"height\":229,\"caption\":\"Sequallity Limited\"},\"image\":{\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/Sequallity-Ltd-437772140011198\/\",\"https:\/\/twitter.com\/sequallity\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#\/schema\/person\/a607249919447f52446059e05c32ae4f\",\"name\":\"John Parker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-GB\",\"@id\":\"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a7f67ba401f89da69efc12c5309f35c08d867db69cddc738a4ab325da67106c3?s=96&d=monsterid&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a7f67ba401f89da69efc12c5309f35c08d867db69cddc738a4ab325da67106c3?s=96&d=monsterid&r=g\",\"caption\":\"John Parker\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Find Top 10 SQL Server Performance Problems","description":"Sequallity are a SQL Server Consultancy since 2005. Here is their SQL Server blog. All comments are opinions of the Authors.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/","og_locale":"en_GB","og_type":"article","og_title":"Find Top 10 SQL Server Performance Problems","og_description":"Sequallity are a SQL Server Consultancy since 2005. Here is their SQL Server blog. All comments are opinions of the Authors.","og_url":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/","og_site_name":"SQL Server Consultancy and SQL Server Training","article_publisher":"https:\/\/www.facebook.com\/Sequallity-Ltd-437772140011198\/","article_published_time":"2020-06-16T10:03:00+00:00","article_modified_time":"2020-06-17T09:20:55+00:00","og_image":[{"url":"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2020\/06\/T-SQL-Select-GoFaster.png"}],"author":"John Parker","twitter_card":"summary_large_image","twitter_creator":"@sequallity","twitter_site":"@sequallity","twitter_misc":{"Written by":"John Parker","Estimated reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/#article","isPartOf":{"@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/"},"author":{"name":"John Parker","@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#\/schema\/person\/a607249919447f52446059e05c32ae4f"},"headline":"Find Top 10 SQL Server Performance Problems","datePublished":"2020-06-16T10:03:00+00:00","dateModified":"2020-06-17T09:20:55+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/"},"wordCount":422,"commentCount":0,"publisher":{"@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#organization"},"articleSection":["SQL Scripts","SQL Server Performance Tuning","T-SQL"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/","url":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/","name":"Find Top 10 SQL Server Performance Problems","isPartOf":{"@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#website"},"datePublished":"2020-06-16T10:03:00+00:00","dateModified":"2020-06-17T09:20:55+00:00","description":"Sequallity are a SQL Server Consultancy since 2005. Here is their SQL Server blog. All comments are opinions of the Authors.","breadcrumb":{"@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/find-top-10-sql-server-performance-problems\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/"},{"@type":"ListItem","position":2,"name":"SQL Server Performance Tuning","item":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/sql-server-performance-tuning\/"},{"@type":"ListItem","position":3,"name":"Find Top 10 SQL Server Performance Problems"}]},{"@type":"WebSite","@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#website","url":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/","name":"SQL Server Consultancy and SQL Server Training","description":"where SQL and Quality join...","publisher":{"@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-GB"},{"@type":"Organization","@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#organization","name":"Sequallity Limited","url":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/","logo":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#\/schema\/logo\/image\/","url":"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2018\/09\/Sequality_Logo_Blue-Cyan-Blend_Large.jpg","contentUrl":"https:\/\/www.sequallity.co.uk\/wp-content\/uploads\/2018\/09\/Sequality_Logo_Blue-Cyan-Blend_Large.jpg","width":800,"height":229,"caption":"Sequallity Limited"},"image":{"@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/Sequallity-Ltd-437772140011198\/","https:\/\/twitter.com\/sequallity"]},{"@type":"Person","@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#\/schema\/person\/a607249919447f52446059e05c32ae4f","name":"John Parker","image":{"@type":"ImageObject","inLanguage":"en-GB","@id":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a7f67ba401f89da69efc12c5309f35c08d867db69cddc738a4ab325da67106c3?s=96&d=monsterid&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a7f67ba401f89da69efc12c5309f35c08d867db69cddc738a4ab325da67106c3?s=96&d=monsterid&r=g","caption":"John Parker"}}]}},"_links":{"self":[{"href":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/wp-json\/wp\/v2\/posts\/937","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/wp-json\/wp\/v2\/comments?post=937"}],"version-history":[{"count":7,"href":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/wp-json\/wp\/v2\/posts\/937\/revisions"}],"predecessor-version":[{"id":1045,"href":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/wp-json\/wp\/v2\/posts\/937\/revisions\/1045"}],"wp:attachment":[{"href":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/wp-json\/wp\/v2\/media?parent=937"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/wp-json\/wp\/v2\/categories?post=937"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sequallity.co.uk\/sql-server-consultancy\/wp-json\/wp\/v2\/tags?post=937"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}