Unused(?) Indexes

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

One thought on “Unused(?) Indexes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s