July 7, 2017 at 2:13 pm
Hello Experts,
To monitor the real index usage I mostly use the below std query. Do you suggest running any other scripts/column to also be checked to see the real usage of the indexes?
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
--T.Name 'Table Name',
--I.Name '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
INNER JOIN sys.tables T
ON I.object_id = T.object_id
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
and i.name in ('')
Thanks.
July 7, 2017 at 2:21 pm
My only comment on that is your last line: "and i.name in ('')". that'll remove all of the indexes, no?
but the last_user_* columns can be interesting too. I mean, if there were a million user seeks, but the last user seek was over 5 years ago, that is interesting.
so I'd add "last_user_seek", "last_user_scan",'Last_user_lookup","last_user_update".
I'd also add "type_desc". This indicates if it is a heap, a clustered index or a non-clustered index.
And depending on what you are hoping to learn from the report, the "is_unique", "is_primary_key", "fill_factor", "is_padded" may be interesting too.
I'd also add an extra clause into your where of:
"AND is_disabled = 0"
as I doubt you really care about the disabled indexes (or maybe you do?).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 7, 2017 at 2:32 pm
SQL-DBA-01 - Friday, July 7, 2017 2:13 PMHello Experts,To monitor the real index usage I mostly use the below std query.
Keep in mind that is index usage since the last time SQL Server started, not overall.
Inner joins everywhere means you won't see indexes that haven't been used at all since the last start, only those that have had some access, read or write.
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
July 7, 2017 at 2:37 pm
Is there anyway I also can check which indexes are doing more writes and which are doing more reads etc?
Thanks.
July 8, 2017 at 3:00 am
SQL-DBA-01 - Friday, July 7, 2017 2:37 PMIs there anyway I also can check which indexes are doing more writes and which are doing more reads etc?
Yes, the query you posted allows that.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply