September 29, 2014 at 6:36 pm
Hi All,
I am wondering about the UNUsed InDEX ...
When I run this script :
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 will get the result like this :
OBJECT NAMEINDEX NAMEUSER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES
Users PK_Users 311 0 31180 0
Users PK_Users 0 0 0 9
My question is why there are 2 different data for the same index ??
Please kindly advise ..Thanks a lot
Cheers
September 29, 2014 at 8:13 pm
You are getting a cross contamination from the index stats for other databases. SYS.DM_DB_INDEX_USAGE_STATS contains entries for every database and sometimes index_id and object_id might be the same in different databases.
You can fix that with a simple change such as the following
SELECT OBJECT_NAME(i.[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(i.[OBJECT_ID],'IsUserTable') = 1
and s.database_id = db_id()
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
September 30, 2014 at 12:38 am
SQLRNNR (9/29/2014)
You are getting a cross contamination from the index stats for other databases. SYS.DM_DB_INDEX_USAGE_STATS contains entries for every database and sometimes index_id and object_id might be the same in different databases.You can fix that with a simple change such as the following
SELECT OBJECT_NAME(i.[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(i.[OBJECT_ID],'IsUserTable') = 1
and s.database_id = db_id()
Thanks so muchh !!! It works 🙂
September 30, 2014 at 7:26 am
You're welcome
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply