November 13, 2014 at 7:28 am
I have the following query I use to select unused indexes. I'm returning 140. I see some that I know are needed that I used to remove some key lookups in query plans. We run the Hallergen index script for maintaining indexes nightly. Is it possible that when these indexes are rebuilt or when stats are updated that its setting the index user lookups, user seeks, user scans, and user updates to be zero? Just want to be sure these indexes aren't needed before removing them. Below is my script for returning unused indexes:
SELECT
o.name AS ObjectName
, i.name AS IndexName
, i.index_id
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO
November 13, 2014 at 7:33 am
Note that sys.dm_db_index_usage_stats only returns stats from the last time the server was started. Just because an index hasn't been used since start up doesn't mean it is truly unused.
Gerald Britton, Pluralsight courses
November 13, 2014 at 8:00 am
I'm 99% sure that the server hasn't been restarted in a couple months
November 13, 2014 at 8:23 am
You can find the last SQL Server restart using sys.dm_os_sys_info and the sqlserver_start_time column.
Before you DROP any indexes you need to:
1. Save the create scripts off somewhere.
2. Check to make sure no queries have index hints in them. You can use the free SQL Search tool from RedGate (http://www.red-gate.com/products/sql-development/sql-search/) to help find index hints in SP's, functions, and views.
3. Check to see if there is a similar/duplicate index which is why the index is never used (you can usually drop the unused index in this case).
4. Definitely use the full ALM stack to test (dev, qa, uat, and then production).
You also never know if there is an index out there for that year end report that takes 12hrs to run without the index and only 10 minutes with it. Which is why I recommend collecting and aggregating the index usage stats for quite awhile before dropping any indexes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 13, 2014 at 12:39 pm
dndaughtery (11/13/2014)
I'm 99% sure that the server hasn't been restarted in a couple months
And you're absolutely sure that there's no chance they can be used by a business process that only runs once every few months (like a year end)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2014 at 3:43 pm
Just make sure you carefully document what you're removing and have it stored somewhere that everyone remembers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 13, 2014 at 3:49 pm
I'd be extremely cautious about removing indexes. Rare processes that use the index is one issue. Another issue is the use of index hints.
Despite showing the index was unused, I had a client recently decide to remove 2 indexes that were referenced in index hints. Luckily we scripted out the index definition and could quickly respond.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply