Made a New Year’s resolution to lose weight this year? Forget taking it off your waistline – take it off your database instead with:
sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = ‘size’
sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2, @SortOrder = ‘rows’
This produces an inventory of all the indexes sorted by size on disk or the number of rows, descending:
You can copy/paste that into Excel, send it around amongst your teams, and start having discussions like:
- “Why do we have a 50GB heap called Sales_Backup_BeforeTheDeploy?”
- “Why do we have a 200GB table called ClickLog with over a billion rows? I thought we were purging that table nightly?”
- “Weren’t we supposed to archive the SalesHistory table?”
Things to be aware of when you’re reading the results:
- The Rows & Reserved MB columns are way off to the right. I just rearranged the SSMS output for this screenshot. That’s one of the reasons I typically do this analysis in Excel – that, and the fact that I like to highlight offensive rows.
- The Last User Seek, Last User Scan, and Last User Lookup columns can help identify when the index was last read, but it’s not entirely accurate for a lot of reasons that we discuss in this module of the Mastering Index Tuning class.
- The Last User columns are also incremented by any query, including things like your homegrown fragmentation scripts checking the contents of a row or a homegrown ETL package. Even if it’s run automatically by your apps, that’s still a “user” query to SQL Server.
That’s it. That’s the whole post. See, my blog post lost weight too.