| Author |
 Topic  |
|
|
egemen_ates
Yak Posting Veteran
Turkey
63 Posts |
 Posted - 06/27/2012 : 04:02:51
|
| i want to see index usage statistics only one database.how can i do this? |
|
|
Manigandan
Starting Member
4 Posts |
Posted - 06/27/2012 : 06:27:37
|
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 |
 |
|
|
egemen_ates
Yak Posting Veteran
Turkey
63 Posts |
Posted - 06/27/2012 : 07:16:15
|
This query result; some indexname same one of more database.
this query is working i find database id from sys.databases and added where block
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES,
database_id
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 database_id =6 and OBJECT_NAME(S.[OBJECT_ID])='ORDER_ROW_RESERVED'
quote:
Originally posted by Manigandan
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
|
|
|
| |
Topic |
|
|
|
Index usage statistics.
