SQL Server Community Tools: How I Use sp_Blitz

Elder Gods

---

For the last week of the series, I’m going to talk about the Blitz scripts. On top of using them for a hell of a long time, I’ve done a ton of work on them over the years.

Some of it was better than others, and there’s certainly some stuff I spent a long time on that I would never pursue today, but in general I think value was added.

I will say that nearly every check and condition that I felt inclined to add to the scripts were based on working with clients and finding some weird problems along the way.

If you ever find a really weird problem using any of these, you can thank the nice people who had really weird SQL Server problems that I worked with.

Once you spend a while tracking down a problem, you never wanna waste that time again, so you check for it every time just in case.

Deep Down

---

One thing that I always do with sp_Blitz is use the @CheckServerInfo parameter, set to 1.

EXEC sp_Blitz
    @CheckServerInfo = 1;

This parameter enables checks on a whole bunch of important system things, like:

• If instant file initialization is enabled
• If lock pages in memory is enabled
• A whole bunch of CPU information, including if they’re in balanced power mode
• If you have stacked SQL Server instances
• If you have a bunch of other components (SSAS, SSIS, SSRS) installed

Stuff like that can be awesome to make note of if you’re trying to track down weird, transient issues.

I never run sp_Blitz without it.

Hop Scotch

---

Another thing I end up doing a lot is skipping checks, because some of them just aren’t important to me, and can be very noisy.

I never say never, but I tend to skip past stuff like:

• Stored procedure with recompile
• Jobs owner by users
• Every security check (like logins with elevated permissions)
• Jobs without failure emails

That stuff can be a real drag to scroll through. Here’s what I usually do.

CREATE TABLE 
    dbo.BlitzCheckSkip 
(
    ServerName sysname NULL,
    DatabaseName sysname NULL,
    CheckID int NULL
);

INSERT INTO 
    dbo.BlitzCheckSkip 
(
    ServerName, 
    DatabaseName, 
    CheckID
)
VALUES 
    (NULL, NULL,   55), --Database Owner <> SA
    (NULL, NULL,   86), --Elevated Permissions on a Database 
    (NULL, NULL,    6), --Jobs Owned By Users
    (NULL, NULL,   78), --Stored Procedure WITH RECOMPILE
    (NULL, NULL,  104), --Control Server Permissions
    (NULL, NULL,    5), --Security Admins
    (NULL, NULL,    4), --Sysadmins
    (NULL, NULL, 2301); --Invalid Active Directory Accounts

EXEC sp_Blitz
    @CheckServerInfo = 1,
    @SkipChecksDatabase = 'master', 
    @SkipChecksSchema = 'dbo', 
    @SkipChecksTable = 'BlitzCheckSkip';

Most of my engagements focus on performance, so I can skip this stuff because I’m not gonna sit around lecturing people about any of this.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.