Thursday, May 30, 2013

sp_Blitz Features for BI Admins

I recently discovered the sp_BLITZ™ – SQL Server Takeover Script via the SQL Server Central Daily Newsletter. (You need to sign up for this if you don't already.) You can find sp_BLITZ™ at I took a quick look at it and found several features that would be very useful in a SQL Server BI environment.

I first tried the sp_BLITZ™ App but wasn't able to use it. It is a click-once application which means you can't run it as another user. I use a special administrative account that has access to the SQL Server instances that I manage while my regular account is used for tasks that don't require elevated permissions. Since I couldn't run the sp_BLITZ™ app under the administrative account, it wasn't going to be able to connect to a server to do its work. If there is a locally installed app in the future I will give it a try as long as it doesn't require Internet access. (My admin account, per company policy, can't get out to the Internet and potentially download bad stuff.)

With that out of the way, I pulled up the sp_BLITZ™ script file and copied it into a query window connected to a development server. It was only a couple bazillion lines of code so I made sure to review the script to make sure it was safe. Ah, forget that, Brent Ozar has a trustworthy face so I just ran it.

This thing provides a crazy amount of useful information (in a good way). Rather than writing a novel, I am going to list a few of the stand-out items (in no particular order) from the perspective of a BI administrator. Here goes.
  • Storage Corruption: Aaaaaahhhhhh!!! Quit reading and go fix this now!
  • High Virtual Log File (VLF) Count: This issue can cause trouble with high volumes of INSERTs, UPDATES, and DELETES. We're in BI so we do lots of those.
  • Partitioned Tables with Non-Aligned Indexes: Data Warehouses often use table partitioning to maximize performance. You need to make sure your indexes are set up accordingly.
  • DBCC CHECKDB Not Run Recently: This may seem like a basic DBA task but it is just as important to check the consistency of your DW data. I don't know about your shop, but we even have some staging data that can't be recovered from source systems any more. Make sure you keep this data safe!
  • Slow Storage Reads or Writes: Your disks can't be fast enough, that is all.
  • Triggers Found on Tables: Holy crap! This can kill performance, especially during high-volume data loads. It's time to go smack the developers around (again).

All in all, this is a fantastic tool. Despite the enormous amount of stuff sp_BLITZ™ checks for it only bothers you with what you need to know. Add on the fact that the sp_BLITZ™ web site provides great information on how to fix each and every problem it diagnoses there is no reason our SQL Servers aren't running at top-flight speed. I guess it's time I got started on some of the issues that sp_BLITZ™ found...

Have you tried sp_BLITZ™ yet? Tell us what you thought in the comments or post a link to your blog post.

No comments:

Post a Comment