Dynamic Management Objects/ Presentations/ SQL Saturday/ Travel

Analyzing Your Indexing Strategies – Salt Lake City SQLSaturday 2015

, , , , , , , 2 Comments

Last week I was passing through Salt Lake City on my way home from Portland, Oregon and decided to get my fill of mountains and solitude and also speak at SQLSaturday in Salt Lake City since my speaking engagement count really took a hit this year due to the time spent on the PASS Board of Directors, SQL Cruise, and my new role at SurveyMonkey. The topic was Analyzing your Indexing Strategies using my favorite aspect of SQL Server: Dynamic Management Objects.  This was also intended to be an introduction to Power BI using the output of these scripts as fodder for Power BI but when you have the first session at a SQL Saturday your start time is dependent upon registration moving quickly.  We had a late start so I sacrificed Power BI for time spent digging in a little more deep than the introductory-level session I promised.

Like most presentations, you had to be there.  However I promised the attendees that I’d post-up the slide deck to this site for download.  That zip file with the scripts and presentation can be found here.  Additionally, I took advantage of the whiteboard in the room to crudely draw connections information for the DMOs we covered:

  • sys.dm_db_index_physical_stats()
  • sys.dm_db_index_usage_stats
  • sys.dm_db_index_operational_stats

with the supporting System Catalog Views that were important to fleshing-out details from the DMOs:

  • sys.indexes
  • sys.index_columns
  • sys.tables
  • sys.objects

I have included the image from the whiteboard with that information in the zip file referenced. As for the rest of the session materials, they include scripts covering the following analysis points:

  • Identifying index fragmentation
  • Identifying index fragmentation on only the most read indexes
  • Quantifying page splits
  • Returning page split information cross-referenced with usage information to identify page splits
    • Page splits on most-frequently read indexes
    • Page splits on most-frequently written indexes
  • Analyzing page split counts to identify poor fill factor settings
  • Indexes with most latch wait time
  • Indexes with most latch wait time per user interaction (read, write)
  • Indexes with most pageiolatch wait time
  • Indexes with most pageiolatch wait time per user interaction (read, write)
  • Usage information for all tables
  • Usage information for all indexes
  • Tables with no activity since last service restart
  • Indexes with no activity since last service restart
  • Index fragmentation information for just the most-read indexes

That’s right, 16 different items you can benchmark and analyze to see how healthy your indexes are and how well you’re doing in architecting and maintaining the foundations of your indexing strategy.

In the coming weeks I’ll be blogging about these scripts in more detail here as well as in articles on SQL Server Pro (aka SQLMag.com).