In past two weeks’ posts (1, 2) I talked about how I’ve got a few copies of Expert Performance Indexing for SQL Server to give away. This is a book that I co-wrote with Grant Fritchey (Blog | @gfritchey) last summer, which I think can be a valuable resource to anyone building indexes on SQL Server. To give away these books, I’m asking a question a week and sending out a book to someone based on comments left on this post.
The Question
For week three, the questions will focus on chapter three, which is Index Metadata and Statistics. The topic for discussion in the comments this time is…
What indexing dynamic management view do you use the most, and how does it help you? How have index dynamic management views helped you in your indexing and performance tuning quests?
If you have a story to share in this area, leave it in a comment below. At the end of a week, I’ll select one of the comments and send that author a copy of the book.
June, 24 Update
The winner for the book this week is SQLDBAPro. I had to go with him because he mentioned my favorite index DMV, sys.dm_db_index_operational_stats. This DMV is chalk full of low level details on how indexes are used. I encourage you to check it out. Chapter three of Expert Performance Indexing for SQL Server digs into this DMV quite a bit with examples on how the details are generated.
The other comments this week were from:
- Bob talked about using sys.dm_db_index_usage_stats and sys.dm_db_index_physical_stats
- Kathi mentioned indexing being an iterative process and reading the last edition of the book (Yes!)
- Shaishav uses sys.dm_db_missing_index_details for finding new indexes
- Josh pointed out using sys.dm_db_index_physical_stats for index fragmentation
