July 17, 2014 at 7:54 pm
HI All,
I wonder why after running the "unused indexes script "
as below :
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
I got a list that has so many redundant on the same index , for example :
OBJECT NAMEINDEX NAMEUSER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES
BatchSentIX_BatchSent 14 0 0 0
BatchSentIX_BatchSent 114435003 3400529 0 156
BatchSentIX_BatchSent 27707223 39132 0 9977648
pls kindly advise 🙂
Many thannks
July 18, 2014 at 12:56 pm
You need to filter sys.dm_db_index_usage_stats by database_id like this:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
And S.database_id = DB_ID()
I had this same problem recently and someone point this out to me as well.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply