Visualizing SQL Indices with PowerPivot

Hey folks, got another special guest post for you.  Today we are honored by the presence of the SillyBoy himself, Scott Senkeresty.  Scott is a very close friend of mine that I met in my first week at Microsoft.  These days Scott is still at Microsoft, working on antivirus features.

…and like all of my techie friends, he hears a lot about PowerPivot from me, and has been playing with it in his work.  Here is one of his recent experiments:

This is a query you can run against any sql database:

query

It uses a dmv in sql that shows intersesting stats about your index.  I used it against our production database, and added a calculated column for “Total Reads” = Seeks+Scans+Lookups.

Then I create a Chart n’ Table… just cuz.  I created Slicers for TotalReads, Updates, and Index Name (for kicks).  I added Index Name as a Row Column and Reads as values.

Now it’s time to par’tay. 

I am interested in finding under-utilized indexes (lots of updates, few reads).  So, I just use the read slicer and select the first 5 or so.  The update slicer automagically updates… and I scroll down the remaining “big numbers”.  Select 5 or so of those… and <poof!> my data!

Notice that my Index Name slicer has my nasty’s highlighted.  Not required since my table & chart show that data, but it’s cool to see that auto-filtering works so well, I almost don’t need the charts.

And hey look, an index with 26 million updates, and not a single read… Thanks Power Pivot!

clip_image002

And here is the query again as text in case you want to copy/paste it into PowerPivot’s query tool:

SELECT object_name(s.object_id) as [Table Name], i.Name as [Index Name],  user_updates as Updates, user_seeks as Seeks, user_scans as Scans, user_lookups as Lookups FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND i.index_id = s.index_id WHERE objectproperty(s.object_id,’IsMsShipped’) = 0 AND s.database_id = db_id()

Enjoy!

One Response to Visualizing SQL Indices with PowerPivot

  1. alexv says:

    Hi Rob,

    ’IsMsShipped’ should be ‘IsMsShipped’ – save a few people some time debugging ;-)

    SELECT object_name(s.object_id) as [Table Name], i.Name as [Index Name], user_updates as Updates, user_seeks as Seeks, user_scans as Scans, user_lookups as Lookups FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND i.index_id = s.index_id WHERE objectproperty(s.object_id,’IsMsShipped’) = 0 AND s.database_id = db_id()

Leave a Comment or Question