2

In order to assess options of implementing indexed views, I want to check the usage of DML operations of the tables. The database is new to me as well as the relevant business knowledge, so I just have no idea.

Is there a way to quickly collect a statistic within couple of days?

I am using Standard Edition.

  • 1
    If you just want to know number of index updates (regardless of how many rows affected, how wide the indexes are, etc.) and as long as you can be sure the service doesn't get restarted or indexes dropped/re-created in that timeframe, then you can look at sys.dm_db_index_usage_stats now and after a couple of days. – Aaron Bertrand Aug 19 '15 at 16:16
  • Thats a nice approach - I immedeately took a snapshot. Thanks. Anything to consider with this approach (besides what you mentioned)? Is it missing something? – Magier Aug 19 '15 at 16:23
  • 1
    @Magier its worth reading - FAQ around sys.dm_db_index_usage_stats and you can use this script to collect some stats as Aaron Pointed out. – Kin Shah Aug 19 '15 at 16:24
2

If you just want to know number of index updates (regardless of how many rows affected, how wide the indexes are, etc.) and as long as you can be sure the service doesn't get restarted or indexes dropped/re-created in that timeframe, then you can look at sys.dm_db_index_usage_stats now and after a couple of days.

As suggested by Kin, have a look at Nacho's FAQ. Basically:

  • The DMV can reset if:
    • the server is restarted
    • an index is dropped and re-created
  • The DMV can be empty if you start with -x, -m, or trace flag 2330
  • If you have a filtered index, updates will increment the usage stats even if the update did not affect the rows in the index
  • If any column in the clustering key is modified, all non-clustered indexes also increment their usage stats
  • Errors occurring after a DML attempt is made will still increment counters
  • Index usage tells you number of operations - it does not have any way to report to you number of rows or pages touched (an update to every row in a very wide table looks the same as an update to a single row in a very skinny table) - if you want more details on actual volume touched, see sys.dm_db_index_operational_stats.

And also Tim Ford's series on snapshotting the DMV:

  • For those reading and maybe asking themselves if there maybe a way to persist those data in defiance of server restarts etc. - I took the approach described here, to persist the statistics: mssqltips.com/sqlservertip/1749/… – Magier Sep 9 '15 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.