Vadivel's blog

There are quite a lot of DMV's which have been introduced along with SQL Server 2005. One such is sys.dm_db_missing_index_details. Using this DMV we can identify what all indexes SQL optimizer is expecing in our tables. Query: select * from sys.dm_db_missing_index_details Fields to note: equality_columns, inequality_columns, included_columns, statement So by querying this DMV and having a look at these 4 columns we can decide on the types of indexes which needs to be created for our tables. Each of this column has a meaning. Either find it yourself :) or drop a line in the comment section. One important point to keep in mind is the result you see out of running the above DMV is just a 'suggestion' on the way to improve the performance of your query. But its not 100% guaranteed that on creating all these indexes would help. We cannot access the DMV using accounts which doesn't have permission for 'View Server State'. I would write more on these in upcoming post...

I am glad to know that I have been renominated as a MVP in SQL Server category this year (2007 - 2008). Thanks everyone for your support and encouragement!

When queries / SP's hits those tables where appropriate indexes are not there then the database engine would automatically create indexes to be used by its execution plan. The hitch is those dynamic indexes would not be reused by the engine the next time when it hits the same table / column. i.e., it would create another dynamic index each time when it hits the same table / column. That said, normally till SQL Server 2000 I used to execute the below statement to list down those dynamically created indexes. So that i can analyze and create indexes appropriately. Select [name] from sysindexes where [name] like '_WA_%' I would write more on this shortly.