It’s time once again for that monthly dose of TSQL Tuesday. This month we have database whisperer Michael Swart hosting and he has asked everyone to blog about indexes. As Michael so keenly pointed out indexing covers a huge swath of possibilities. For my part I want to speak about getting rid of those pesky, no good indexes. OK, well really I want to speak about first considering the existing indexes before adding new ones. All too often I find people who have just gone crazy adding an index for every column or have just listed every possible combination of columns they could think of.
So how do we do this? The first thing I like to do is look at the overall number of heaps vs clustered vs non-clustered indexes. This gives me some feel for how well the database may or may not be indexed. Have they taken the time to create clustered indexes on most of their tables? Is their a disproportionately large number of non-clustered vs clustered indexes? The following script will show you the count for each of the heaps, clustered, and non-clustered indexes.
--show a count of indexes by type select i.type_desc, count(*) as cnt from sys.indexes i inner join sys.objects o on i.object_id = o.object_id WHERE o.type<>'S' GROUP BY i.type_desc
Once I get a feel for the overall numbers and types of indexes I like to dive a little deeper and look at the individual tables. I pay particular attention to the tables with the most indexes, especially if they have gone past the single digits into 15, 20, 30, or more indexes on a table for an OLTP workload.
--show a count of indexes per table --does not count the clustered or heap select o.name as [Table Name], (select count(*) from sys.indexes i WHERE i.object_id = o.object_id AND i.index_id>1)as cnt from sys.objects o WHERE o.type='U' order by cnt desc
Once I am armed with a list of the “over” indexed tables I compare that to the actual index usage. Indexes come at a cost and they are not cheap. Every Time you insert, update, or delete data the indexes also have to be kept up. Then of course you have to perform regular index maintenance such as rebuilds and reorgs, not to mention keeping the statistics updated. So it is important to insure that your indexes are actually of use and not just costing you expensive cpu and disk io to maintain.
The following script is one I picked up from Glenn Berry’s excellent blog. It will show those indexes which have been written to more than they have been read. These are good candidates to consider dropping when you need to cut your index bloat. I wanted to note that I have modified Glenn’s script in a couple of ways. First I have added a column which contains the DROP INDEX statement which makes it easy to drop the index once I have VERIFIED it is actually not needed. Secondly you have to keep in mind that sys.dm_db_index_usage_stats only has statistics for indexes which are in cache. This is important for two reasons. First you may have indexes which are only used once in a while say for an end of month report. You definitely don’t want to drop indexes which prolong the CEO’s favorite report. Secondly you may have indexes that just aren’t even being used at all. Why waste disk space and time maintaining indexes that are not even being used?
-- Possible Bad Indexes (writes > reads) SELECT sch.name + '.' + t.name AS [Table Name], i.name AS [Index Name], i.type_desc, ISNULL(user_updates,0) AS [Total Writes], ISNULL(user_seeks + user_scans + user_lookups,0) AS [Total Reads], ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0) AS [Difference], 'DROP INDEX [' + i.name + '] ON [' + sch.name + '].[' + t.name + ']' AS [Drop_CMD] FROM sys.indexes AS i WITH (NOLOCK) LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK) ON s.object_id = i.object_id AND i.index_id = s.index_id AND s.database_id=db_id() AND objectproperty(s.object_id,'IsUserTable') = 1 INNER JOIN sys.tables AS t WITH (NOLOCK) on i.object_id=t.object_id INNER JOIN sys.schemas AS sch WITH (NOLOCK) on t.schema_id=sch.schema_id WHERE --ISNULL(user_updates,0) >= ISNULL((user_seeks + user_scans + user_lookups),0) --shows all indexes including those that have not been used ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0)>0 --only shows those indexes which have been used AND i.index_id > 1 AND i.is_primary_key<>1 AND i.is_unique_constraint<>1 ORDER BY [Table Name], [index name]
So there you have it. Before I ever even consider adding new indexes I take a dive into the existing indexes. I need to insure that what already exists is running optimally and that I am not going to bring things to a grinding halt by piling on new indexes. As always be incredibly careful before you make any changes to production. Adding and removing indexes can both have huge effects on performance so be doubly sure before you execute those CREATE and DROP statements.
***Bonus*** Hypothetical Indexes
Occasionally I still come across databases that have hypothetical indexes. What are hypothetical indexes? These are created by the Database Engine Tuning Advisor while it makes its recommendations. While normally DTA will remove these when it is done, occasionally it will leave them in place. You can read a little more (but not much) about them over at MSDN. The following query will show if the database has hypothetical indexes. Once the hypothetical indexes have been identified, they can be safely removed.
--shows hypothetical indexes SELECT object_name(object_id), name AS Hypothetical FROM sys.indexes WHERE Is_Hypothetical = 1
The last script I have for you will generate all of the drop commands for the hypothetical indexes.
SELECT 'DROP INDEX ['+i.name+'] ON ['+s.name+'].['+t.name+']' as drop_cmd from sys.indexes i inner join sys.tables t ON i.object_id=t.object_id inner join sys.schemas s on t.schema_id=s.schema_id where i.Is_Hypothetical=1