Another post on SQL Server Central has the user asking about shrinking a database that apparently has 100 gig free space and a large transaction log file. There are several issues to consider, as Jeff Moden pointed out, including the size of the largest index in the database.
Yesterday a person replied that you might look at dropping unused indexes. But that brings up the question: what determines whether an index is unused.
The problem is that any statistics that you pull from sys.dm_db_index_usage_stats only show stats since SQL Server was last restarted, not since the dawn of time. Brent Ozar explains it well from today’s newsletter:
Unused indexes are tricky. When you are analyzing this data, you have to keep in mind that this data is only available since the last restart. If you rebooted the server yesterday and are viewing the data today, you might see a lot of unused indexes in the list. But are they really unused? Or have the indexes not just been used YET? This is a very important point when deciding to disable or drop an index based on this list. If you reboot your servers monthly due to Microsoft security patches, consider reviewing the list the day prior to the reboot. I once dropped an index 3 weeks after the server was rebooted, thinking that the entire application workload must have been run by now. A few days later, I got a call on the weekend that the database server was pegged at 100% CPU utilization. I reviewed which queries were using the most CPU and found that the top query’s WHERE clause matched the index I had dropped. That query only ran once a month, which is why it hadn’t recorded any reads yet. We later moved that monthly process to another server that was refreshed regularly with production data.
Here’s some code that can show you what indexes are unused or empty. An empty index just means that there’s no data in that table right now, it may always be populated later, so I would not drop an empty index. Besides, how much space would an empty index take?
For my personal preferences, I order the output by table then index name, also I put a u.* at the end of the select statement so the more interesting usage stat columns can be seen.
--Jonathan Fahey, 23 Sept 2011 -- http://www.sqlservercentral.com/Forums/Topic1068035-2908-2.aspx#bm1164093 SELECT LEFT(OBJECT_NAME (i.id), 50) AS TableName, LEFT(ISNULL(i.name,'HEAP'), 50) AS IndexName, CASE WHEN u.object_id IS NULL THEN 'No Data' ELSE 'No Usage' END AS Determination, i.dpages AS PagesUsed, CONVERT(DECIMAL(8,2), i.dpages / 128.0) AS MBUsed FROM sys.objects o JOIN sys.sysindexes i ON i.id = o.object_id LEFT JOIN sys.dm_db_index_usage_stats u ON i.indid = u.index_id AND u.object_id = i.id WHERE o.type = 'U' -- Exclude system tables. AND i.name IS NOT NULL -- Exclude heaps AND i.name NOT LIKE '_WA_Sys%' -- Exclude statistics AND ( u.object_id IS NULL -- Either no usage stats, or... OR ( u.user_seeks = 0 -- all usage stats are zero. AND u.user_scans = 0 AND u.user_lookups = 0 ) ) ORDER BY i.dpages DESC