DMV Queries Tied to My Custom Index Stats Tables

I've mentioned a set of DMV queries that I use in a previous post on this blog. I've been chopping and changing the base set of queries for quite a while to suit my needs.

I have also written a series of posts describing how to perform index and statistics maintenance on a SQL Server's databases without losing the very important index stats that the queries mentioned above return.

So, in this post I will show you how I've modified a few of the index focused DMV queries to include the maintenance tables where I now store the cumulative index statistics.



One of the queries I use very often lists unused indexes. To keep performance optimal you should only have indexes that the database engine uses. There may be several reasons why the database engine chooses not to use an index (unfortunately, that's outside the scope of this post), but identifying indexes that are not used is an important first step. You can then decide whether you should drop them or find the queries which you'd expect to use the indexes and tune them.

Another useful query lists indexes that have index writes and zero index reads. These are arguably more problematic than the unused indexes in that the database is maintaining the indexes (writing to them) but not using them for any select statements (reading from them). So your database is getting the negative impact of the index without any benefit.

I hope this helps.