Sep 14 2010
This is my first foray into the now well established T-SQL Tuesday event. If you have not heard of T-SQL Tuesday before it was set up by Adam Machanic (Blog|Twitter) and you can read all about it here: T-SQL Tuesday
This months subject is “Indexes” (however I’m one of those annoying people who prefer to call them “Indices”), our gracious host is Michael J Swart (Blog|Twitter), if you’re quick you may still be able to participate: Invitation to Participate in T-SQL Tuesday #10 – Indexes
No doubt lots of people are going to be talking about how indices improve performance and show execution plans to prove their findings, there may even be some on the different types showcasing the new features available in 2008 such as compression and filtering. The particular subset of information about indices that I am going to talk about is maintenance, and in particular finding those indices that are no longer being used which are just causing an overhead to SQL Server.
What! You want to remove indices, but aren’t indices a good thing they make everything faster?!?
Well not always no. They can lead to bad system performance if left unchecked. For example, your boss may want that really important daily report which has been heavily indexed to speed up it for him/her. After a few weeks all is well with that aspect of the business and the report is never run again. In this scenario we have an index that no longer has any benefits as it was created specifically for this report and no other query is using it. Every time one of the referenced fields is updated or a new row inserted the index has to be updated. These updates are an unneccessary overhead for the database engine to maintain, plus if you are defragmenting your indices you may have extra overhead their too.
So, how do we find these indices?
The answer as with most things in SQL Server is to query one of the many DMV’s, in this case the DMV we want to look at is sys.dm_db_index_usage_stats and this is a very good place to start, however there is so much more that can be shown by querying some of the other system views that will add credence to your case for removing them. Yes that’s right credence, you should ideally create a business case for removing them, you never know if these indexes we re part of an early phase of a postponed implementation that started before your time or was handled by another DBA.
The script below was originally written by Glen Berry (Blog|Twitter) the latest version of his diagnostic scripts can be found here: Possible Bad NC Indexes (writes > reads) .The script has subsequently been modified by myself to provide additional information as well as to exclude things that may cause issues if they were to be removed, such as clustered and unique indices:
Script to check for indexes that have a greater write to read ratio
and have been updated in the last week.
Original script by Glenn Berry
Modified by Richard Douglas
Removed Primary Keys from resultset
Removed unique indices from resultset
Added a date range for the query.
Added a query to show server uptime to add quantification to the figures.
Added [SpaceUsed KB] attribute
DATEDIFF(DD,Create_Date, GETDATE()) [UpTimeInDays]
FROM sys.databases WHERE name = 'tempdb';
@Today = GETDATE(),
@LastWeek = DATEADD(DD,-7,@Today);
OBJECT_NAME(s.[object_id]) 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],
ISNULL((select 8192 * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.partitions as p
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
WHERE p.object_id = i.object_id
AND p.index_id = i.index_id)/1024
,0.0) AS [SpaceUsed KB]
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
AND user_seeks + user_scans + user_lookups = 0
AND Last_User_Update BETWEEN @LastWeek AND @Today
AND Is_Primary_Key = 0
AND IS_Unique = 0
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC;
The query can be amended to work for any time frame, however you must keep in mind that the data being queried is not a total cumulative it will only hold data up to the last time the service was restarted, hence my query against TempDB which is always recreated when the service is restarted.