Recently I went to a client for a one day data warehouse performance tuning exercise. Because you only have one day, it\u2019s important to quickly find the pain-points of the system. I remembered seeing a webinar somewhere of Brent explaining the sp_Blitz script, so I decided to bring those scripts with me. I couldn\u2019t have made a better choice.<\/p>\n
There was a serious indexing problem at the client. They had heard \u201cindexes make reads go faster\u201d, so they slapped a lot of indexes on most of the tables. None of them clustered.\u00a0I ran the script with its default settings and I quickly got a list of all the problems it could find with the indexes on the data warehouse.<\/p>\n
It gave me an overview the following items, all of them were immediately actionable:<\/p>\n
- \n
- duplicate indexes. Remove the offenders immediately.<\/li>\n
- near-duplicate indexes. Check if for example an index has columns (A,B,C) and another index (A,B). Delete the last one.<\/li>\n
- Heaps<\/a>. Quite a long list, but the script also has a section on which tables are accessed the most. This allowed us to focus on the more important heaps in the data warehouse.<\/li>\n
- the so-called work-a-holics: indexes which were used a lot. I focused on making these indexes more efficient: could I make a filtered index out of it? Or maybe add some included columns?<\/li>\n<\/ul>\n
Other topics were listed as well, but these were the main ones I focused on.<\/p>\n
What\u2019s great is that this script also provides you with the URLs to knowledge articles on the Brent Ozar website. If you don\u2019t understand one of the results, you can immediately look it up and read about it.<\/p>\n
By focusing on the results of sp_BlitzIndex script, I could boost performance in just a few hours of work. This near real-time data warehouse is the source for\u00a0a reporting application used by dozens of people in the field, and you could immediately tell it worked a lot faster. Awesomesauce.<\/p>\n
- the so-called work-a-holics: indexes which were used a lot. I focused on making these indexes more efficient: could I make a filtered index out of it? Or maybe add some included columns?<\/li>\n<\/ul>\n
![\"sp_blitzindex\"](\"http:\/\/sqlkover.com\/wp-content\/uploads\/2015\/12\/sp_blitzindex-239x300.jpg\")
<\/a><\/p>\n