You’ve got a Microsoft SQL Server that somebody else built, or that other people have made changes to over the years, and you’re not exactly sure what kind of shape it’s in. Are there dangerous configuration settings that are causing slow performance or unreliability?
You want a fast, easy, free health check that flags common issues in seconds, and for each warning, gives you a link to a web page with more in-depth advice.
That’s sp_Blitz in our First Responder Kit, and here’s how it works:
Download it in our free First Responder Kit.
@CheckUserDatabaseObjects = 0 – turns off database checks like heaps and triggers. This makes sp_Blitz run way faster on systems with hundreds of databases.
@OutputServerName, @OutputDatabaseName, @OutputSchemaName, @OutputTableName – you can write sp_Blitz’s results to a central server for easier reporting, or just to a DBA utilities database on the same server.
@IgnorePrioritiesAbove = 50 – excludes lower-priority alarms like informational alerts. (You can use any priority level here.)
@Help = 1 – explains the rest of sp_Blitz’s parameters, plus the output columns as well.
Sometimes, you’re the honey badger, and you just don’t care. If you’d like to skip specific checks or databases, create a table like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
USE DBAtools; GO CREATE TABLE dbo.BlitzChecksToSkip (ServerName NVARCHAR(128), DatabaseName NVARCHAR(128), CheckID INT); /* This example skips all checks on the server named Skippy: */ INSERT INTO dbo.BlitzChecksToSkip (ServerName, DatabaseName, CheckID) VALUES('Skippy', NULL, NULL); GO /* This example skips all checks on the database named HorribleDB: */ INSERT INTO dbo.BlitzChecksToSkip (ServerName, DatabaseName, CheckID) VALUES(NULL, 'HorribleDB', NULL); GO /* This example skips check #42 on all servers, all databases: */ INSERT INTO dbo.BlitzChecksToSkip (ServerName, DatabaseName, CheckID) VALUES(NULL, NULL, 42); GO |
Then, you can run sp_Blitz and point it at that table to get the list of servers, databases, or checks to skip:
|
1 2 3 4 |
sp_Blitz @SkipChecksDatabase = 'DBAtools', @SkipChecksSchema = 'dbo', @SkipChecksTable = 'BlitzChecksToSkip'; |
Here’s the full list of check IDs to pick which ones to skip.
sp_Blitz works on SQL Server 2008 and newer. If Microsoft supports it, so do we – unfortunately, that means SQL 2000 and 2005 are out of luck. We use a lot of cool DMVs and techniques that only work in current versions of SQL Server. For free interactive support, you’ve got a few good options:
Don’t have time to learn the hard way? We’re here to help with our quick, easy process that gets to the root cause of your database health and performance pains. Our 3-day SQL Critical Care gets to the root of your SQL Server pains and teaches you how to make your databases faster and more reliable. Learn more, see sample reports, and book a free call with me, Brent.
Free class: Dr. DeWitt on Query Optimization, June 28. Dismiss