How Bad Are Your Indexes?

In my last post "Copying SSIS packages With DTUTIL" I described a "gotcha" where the dynamic management views (DMVs) had different columns in SQL Server 2005 compared to 2008+, and I showed a version check I had built into the script to handle it.

It made me think about other places this check would be useful, and the first thing that came to mind was the Bad Indexes DMV query.

If you read my blog or have seen me speak at a SQL Saturday, you know I am a *big* fan of Glenn Berry (@GlennAlanBerry/blog) and his DMV Diagnostic Queries.

http://img.memecdn.com/im-your-biggest-fan_o_668248.jpg

Glenn has put many hours of work into deciphering and joining the dynamic management views/functions that have been in SQL Server since 2005 into useful query frameworks, and I leverage his work (with credit) whenever I can.

The concept of a "bad" index in this context is an index with many more writes than reads - that is, the index requires more effort to maintain than there is benefit from its existence.

http://www.lexisnexis.com/legalnewsroom/resized-image.ashx/__size/500x400/__key/telligent-evolution-components-attachments/13-12-00-00-00-00-00-89/ContentImage_2D00_MugShot.jpg

** IMPORTANT ** - any time you consider removing an index, always verify via use patterns, user interviews, etc. that the index is truly "removeable" - you do *not* want to be the one to remove a "bad" index only to find that it is needed for the monthly payroll run, or the quarterly bonus reports, or some other critical business process.  Quite often "bad" indexes are only used periodically but are very crucial.  An alternative to consider rather than dropping the index outright is to check if the index can be dropped and then recreated when it is needed, but often the index creation process incurs too much overhead for this to be viable.

Out of the box, the bad index query from Glenn's script is database-specific (rather than instance-wide):

-- Possible Bad NC Indexes (writes > reads)  (Query 47) (Bad NC Indexes)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);
-- Look for indexes with high numbers of writes and zero or very low numbers of reads
-- Consider your complete workload, and how long your instance has been running
-- Investigate further before dropping an index!

The query relies on database-specific tables/views (such as sys.indexes) and therefore returns results for the current database context.

The first thing I wanted to do was to wrap the query in my undocumented little friend sp_msforeachdb.

http://mergeralpha.com/blog/wp-content/uploads/2015/10/resized_one-does-not-simply-meme-generator-one-does-not-simply-say-hello-to-my-little-friend-fdd94d.jpg

The query turned out like this:

EXEC sp_msforeachdb '
/* MODIFIED from Glenn - Possible Bad NC Indexes (writes > reads)  (Query 58) (Bad NC Indexes) */
SELECT ''?'' as DBName,o.Name AS [Table Name], i.name AS [Index Name],
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference],
i.index_id,
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN [?].sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
INNER JOIN [?].sys.objects as o WITH (nolock)
on i.object_ID=o.Object_ID
WHERE o.type = ''U''
AND s.database_id = DB_ID(''?'')
/* AND user_updates > (user_seeks + user_scans + user_lookups) */
AND i.index_id > 1
AND user_updates - (user_seeks + user_scans + user_lookups) >75000
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;
'

The query has all of the standard artifacts of sp_msforeachdb, such as the question mark placeholder for the database name sprinkled throughout to set the proper context for all of the database-specific tables and views ( such as [?].sys.indexes).

This was where the version-specific problem came up - SQL Server 2008 introduced the concept of filtered indexes, and therefore a new column (has_filter) was added to sys.indexes.  The result is that running the above query (which came from Glenn's SQL 2008 query script) errors out with a non-existent column error.

A fix to this could have been to have a modified version of the query without the offending column, and it would line up with how Glenn publishes his queries, with different scripts for each SQL Server version.

For *my* purpose I wanted a single script that I could run against any SQL Server 2005+, and the version check logic allows for that.

Here is the version checked version of the Bad Indexes For All Databases script:

/*
Bad Indexes DMV For All Databases
Modified by Andy Galbraith to run across all databases on the instance
Modified version of the Bad Indexes query in the Glenn Berry DMV scripts
http://www.sqlskills.com/blogs/glenn/category/dmv-queries/
Tested on MSSQL 2005/2008/2008R2/2012/2014
*/
SET NOCOUNT ON
DECLARE @SQLVersion char(4)
SET @SQLVersion = left(cast(SERVERPROPERTY('productversion') as varchar),4)
/* PRINT @SQLVersion */
IF LEFT(@SQLVersion,1) NOT IN ('1','9') /* Not 2005+ */
BEGIN
 PRINT 'SQL Server Version Not Supported By This Script'
END
ELSE
BEGIN
IF @SQLVersion = '9.00' /* 2005 */
BEGIN
/* SQL 2005 Version - removes i.has_filter column */
EXEC sp_msforeachdb '
/*
MODIFIED from Glenn - Possible Bad NC Indexes (writes > reads)  (Query 58) (Bad NC Indexes)
*/
SELECT ''?'' as DBName,o.Name AS [Table Name], i.name AS [Index Name],
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference],
i.index_id,
i.is_disabled, i.is_hypothetical, i.fill_factor
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN [?].sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
INNER JOIN [?].sys.objects as o WITH (nolock)
on i.object_ID=o.Object_ID
WHERE o.type = ''U''
AND s.database_id = DB_ID(''?'')
/* AND user_updates > (user_seeks + user_scans + user_lookups) */
AND i.index_id > 1
AND user_updates - (user_seeks + user_scans + user_lookups) >75000
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;'
END
ELSE
BEGIN
EXEC sp_msforeachdb '
/*
MODIFIED from Glenn - Possible Bad NC Indexes (writes > reads)  (Query 58) (Bad NC Indexes)
*/
SELECT ''?'' as DBName,o.Name AS [Table Name], i.name AS [Index Name],
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference],
i.index_id,
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN [?].sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
INNER JOIN [?].sys.objects as o WITH (nolock)
on i.object_ID=o.Object_ID
WHERE o.type = ''U''
AND s.database_id = DB_ID(''?'')
/* AND user_updates > (user_seeks + user_scans + user_lookups) */
AND i.index_id > 1
AND user_updates - (user_seeks + user_scans + user_lookups) >75000
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;'
END
END

This did exactly what I wanted, returning all non-clustered indexes with at least 75,000 more writes than reads (my chosen threshold) across all databases on the SQL Server 2005+ instance.

Hope this helps!