SQL Server 2008 R2 – How to Audit All DML Operations

auditdmlsql serversql-server-2008-r2

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.

Best Answer

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:

Related Question