SQL Server Indexing Book Giveaway – Week 3

by Jason S, posted in SQL Server, SQLServerSyndication

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

12 thoughts on “SQL Server Indexing Book Giveaway – Week 3”

  1. Pingback: SQL Server Indexing Book Giveaway – Week 4 (the late edition) | Strate SQL

  2. I typically use below DMVs to tune my queries and it answers a lot depending on the type of queries I am trying to tune.

    1) Are there any indexes that are no longer in use, or have never been used? (index_usage_stats)
    2) For indexes that are in use, what is the usage pattern? (index_operational_stats)
    3) Which indexes are missing? (missing_index_details, missing_index_group_stats)

    I have come across a lot of DML queries wherein just removing some unused and duplicate indexes helped a lot in speeding up the queries. Sometimes just small and simple changes does make a lot of difference. 🙂 Same goes with the select queries as well, as missing index index warning gives a good starting point to tune some of your queries.

    We usually take snapshot of all these data on a weekly basis and apply/drop indexes depending on the analysis and data we gather. We then monitor its effectiveness for few days and reiterate the whole process again.

    Like

    1. Thanks for the comment, SQLProdDBA. Taking snapshots of this information over time is very useful.

      Like

  3. sys.dm_db_index_usage_stats and sys.dm_db_index_physical_stats are the main elements of a super-query I built to show me all facets of a table’s indexes. It’s difficult to choose one over the other, since they go so well together.

    Like

    1. Thanks for the comments, Bob. They are both good DMVs. What kind of data do you look at for your super-query?

      Like

      1. The query produces several result sets.
        First is the overall information about the object: is it replicated, create and modify dates, number of rows, and storage usage split into Used, Open, Data, Index, Lob and Overflow.
        Then comes the overview of each index, showing: ID, name, various flags, no. of partitions, storage, depth, fragmentation, rows, stats age, seek count, scan count, lookup count, last seek, last scan, last lookup, list of index columns, list of included columns, and filter.
        Then suggested indexes, including: avg cost, avg impact, compiles, seeks, scans, index columns, included columns.
        Also, there is a result set showing various operational stats, mainly concentrating on waits.

        Like

  4. I’m not sure I have a specific favorite, since index tuning is an iterative process. I like to clean up duplicates, look at missing indexes, track down key lookups, and look at index usage. I have learned a ton from the first edition of your book as well as from Jonathan Kehayias’ and Glenn Berry’s blogs.

    Like

    1. Thanks for the comment, Kathi. Indexing tuning is definitely iterative, every time the usage patterns on the database change, so do the index needs.

      Like

  5. Sys.dm_db_missing_index_details and stored a backup copy of each months run to analyze as well as to keep it after decom.

    Like

  6. We use Sys.dm_db_index_physical_stats a lot to check for fragmentation levels to help automate index rebuilds.

    Like

    1. Thanks for the comment, Josh. I think checking for index fragmentation is probably the most popular use for index DMVs.

      Like

Comments are closed.