dmvs

I recently mapped out my thought process for how I approach a new instance of SQL Server when it comes to index tuning.

SQL Server’s “index usage stats” dynamic management view is incredibly useful– but does it tell you what you THINK it tells you?

I explain the quirks of how sys.dm_db_index_usage_stats works and why the information is so valuable.

Microsoft has just released a new round of cumulative updates for SQL Server 2012, and the release notes indicate that a fix to the sys.dm_db_index_usage_stats dynamic management view has been included in these updates. This is good news for index tuners using SQL Server 2012.

As of SP2 + CU12 and SP3 + CU3, SQL Server 2012 will persist index usage information even when ALTER INDEX REBUILD is run.

I am a fangirl of Adam Machanic’s free sp_whoisactive stored procedure.

One of the great things about sp_whoisactive is that it’s very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval.