T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Bert Wagner (b|t) who has asked us to “write about code you’ve written that you would hate to live without.”
Interesting. I go into my toolbox of code/scripts every day. All day long. Oddly, most of the things I’ve written are simple things I don’t want to re-type, or are client specific (some going back to 2006 for a custom log shipping troubleshooting process…). A great many of them are written by others…most often in my case I use things from Adam, Brent and Pinal. Some are customized, most not.
Recently, I’ve been getting a lot of performance tuning work, much of which is basically “things are slow…can you fix them?” type of requests. Most experienced DBAs know that there a few gazillion factors that can lead to this request, and I won’t re-hash them here.
Lets assume, that we’ve optimzed or eliminated the server, disks, network, etc. and were now looking at SQL code and everyone’s favorite – indexes.
I have two scripts I use that give me a quick overview of the type of work SQL Server is doing. These complement each other, and are used AS A STARTING POINT to locate the low-hanging fruit that can be causing excessive work for the disks and memory of the server.
First off – lets look at any HEAPS and Clustered indexes are getting full scans (this is not perfect info…just a starting point remember):
-- base query provided by Jonathan Fite: -- https://dba.stackexchange.com/questions/210341/number-of-full-table-scans -- Added sys.Partitions for rowcount, Imapct computed column, Where conditions and Sorting -- Kevin3NF, https://dallasdbas.com exec sp_MSforeachdb 'use [?] --for just one database, choose the database context and simply highlight andrun the below query SELECT db_name() , S.name AS SchemaName , O.name AS ObjectName , I.name AS IndexName --, I.type AS IndexType , I.type_desc AS IndexType_Desc , IUS.last_user_scan , IUS.user_scans , p.rows , IUS.user_scans * p.rows as [Impact] FROM sys.objects AS O INNER JOIN sys.schemas AS S ON S.schema_id = O.schema_id INNER JOIN sys.indexes AS I ON I.object_id = O.object_id INNER JOIN sys.dm_db_index_usage_stats AS IUS ON IUS.index_id = I.index_id AND IUS.object_id = I.object_id JOIN sys.partitions p on p.object_id = I.object_id and p.index_id = I.index_id WHERE 1=1 and I.type IN (0,1) --0=HEAP, 1=CLUSTERED (table) and IUS.user_scans > 5 and p.rows > 1000 Order by --IUS.user_scans desc --p.Rows Desc IUS.user_scans * p.rows DESC '
When I run this in its entirety, I get a section of results for every database, for any scans returned by sys.dm_db_index_usage_stats. I can quickly scroll up and down that result set and eyeball any massive numbers for the database(s) in question, or all of them for the server as a whole. In a sample server I have I found 2 databases with crazy numbers, so I copied the results for those two into Excel and sorted by the Impact column (which is just user_scans * rows):

As you can see on DatabaseB there is a 27 million row table that gets scanned…a lot. I know some of this is a daily copy from one DB to another…but not that many, so I need to go find out what else is causing these. While I’ve been writing this, it has been scanned 2000 more times. On DatabaseA, I have 3 tables I want to look into…2 of them due to sheer size of the tables and one because it is hit so frequently. In the SQL Query results, the last_user_scan column shows current date and time for all of these…copying to Excel didn’t give me the time…just the date, but that is just a formatting issue.
Note that you can change the results in the WHERE clause to suit your needs.
My next step in this process is to run a Query I based off the Pinal Dave’s (b|t) Missing Index query, which simply gets the Top 5 Missing Indxes for each database, ordered by Impact:
-- Get the top 5 missing indexes for each database on an instance -- and output to a format that can be read as-is, or pasted to Excel for filtering, etc. -- Thanks to Pinal Dave at www.sqlauthority.com for providing the base query this is built on. -- Pinal is good guy and great teacher...you should hire him for your performance issues -- or me, lol -- Test in your Dev environment before trying this in Prod. -- I make no guarantees on performance or SQL version compatibility -- Filtering out various things is done in the WHERE clause (impact, last_seek, etc.) -- Create a temp table to hold the results: CREATE TABLE [dbo].[#MI]( [dbname] [nvarchar](128) NULL, [object_id] [nvarchar](255) NOT NULL, [improvement_measure] [bigint] NULL, [create_index_statement] [nvarchar](4000) NULL, [group_handle] [bigint] NOT NULL, [unique_compiles] [bigint] NOT NULL, [user_seeks] [bigint] NOT NULL, [user_scans] [bigint] NOT NULL, [last_user_seek] [datetime] NULL, [last_user_scan] [datetime] NULL, [avg_total_user_cost] [float] NULL, [avg_user_impact] [float] NULL, [system_seeks] [bigint] NOT NULL, [system_scans] [bigint] NOT NULL, [last_system_seek] [datetime] NULL, [last_system_scan] [datetime] NULL, [avg_total_system_cost] [float] NULL, [avg_system_impact] [float] NULL, [database_id] [int] NOT NULL ) -- Run through each db on the instance and record results to the temp table -- yes, sp_MSforeachdb is both undocumented and unsupported, but for this it works just fine exec master.sys.sp_MSforeachdb ' use [?]; INSERT #MI SELECT top 5 db_name(mid.database_id) as [dbname], Object_name(mid.[object_id]), Cast(((migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans)))as INT) AS [improvement_measure], ''CREATE INDEX [missing_index_'' + CONVERT (varchar, mig.index_group_handle) + ''_'' + CONVERT (varchar, mid.index_handle) + ''_'' + LEFT (PARSENAME(mid.statement, 1), 32) + '']'' + '' ON '' + mid.statement + '' ('' + ISNULL (mid.equality_columns,'''') + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN '','' ELSE '''' END + ISNULL (mid.inequality_columns, '''') + '')'' + ISNULL ('' INCLUDE ('' + mid.included_columns + '')'', '''') AS create_index_statement, migs.*, mid.database_id FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE 1=1 --and migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 40000 --uncomment to filter by improvement measure --and migs.user_seeks > 250 -- change this for activity level of the index, leave commented out to get back everything and mid.database_id > 4 --Skip system databases and db_name(mid.database_id) = ''?'' --Get top 5 for only the current database ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC ' --now that the table is populated, show me the data! --no data for a DB means no index recommendations. Select [dbname] ,[object_id] ,[Improvement_Measure] ,[create_index_statement] ,[user_seeks] ,[user_scans] ,[last_user_seek] ,[last_user_scan] ,[avg_total_user_cost] ,[avg_user_impact] From #MI Where 1=1 Order by [dbname],[improvement_measure] desc --Clean up your mess, you weren't raised in a barn! Drop Table #MI
Again, use the WHERE clause(s) to filter as you see fit. In my case I see only one of the tables from the FULL SCAN query here, so I already know I may have a good shot at seeing at least a valid place to start looking:
I use the Missing Index script all the time, in a variety of ways. Also, Unused indexes to see if newly created ones are actually in play and being used by the Query Optimizer.
And that’s all I have to say about that…except: USE THESE AT YOUR OWN RISK AND LEARN WHAT THEY DO FIRST! 🙂
Thanks for reading!