Product has been added to your cart.

sp_Blitz® Documentation

sp_Blitz® Documentation

Don't use your SQL Server as a print queue.
Please don’t use your SQL Server as a print queue for sp_Blitz® documentation.

sp_Blitz® is a free SQL Server health check stored procedure that looks for a lot of common health and performance issues. If you just use the default parameters, you’ll get a prioritized list of problems, but by tweaking the input parameters, you can affect how sp_Blitz® runs.

Requirements

SQL Server 2005 or newer. SQL Server 2000 is not supported, nor are databases in 70 compatibility mode.

Sysadmin permissions. sp_Blitz® checks a lot of system-level diagnostic data.

What The sp_Blitz® Parameters Do

@CheckUserDatabaseObjects – if 1, we review the user databases looking for things like heaps and untrusted foreign keys.  If your databases have more than a few thousand objects, this may require additional processing time.

@CheckServerInfo – if 1, we output things like the SQL Server version, whether it’s a virtual machine, how much memory it has, and more. This output is at the bottom of the results as a low priority. We don’t check this by default because most users are just interested in returning the problems, not the information, but if you’re using sp_Blitz® to gather server inventory data, this helps.

@IgnorePrioritiesBelow, @IgnorePrioritiesAbove – if you’re not interested in the low-priority checks, you can pass in @IgnorePrioritiesAbove = 100 (or any other number). sp_Blitz®’s output will exclude all items with a priority > 100. If you don’t care about your pants being on fire, you can set @IgnorePrioritiesAbove = 50, and you’ll be blissfully unaware of your failing database backup jobs.

@SkipChecksServer, @SkipChecksDatabase, @SkipChecksSchema, @SkipChecksTable – This does NOT mean to skip checks for a particular server or database. It’s much trickier – and more powerful – than that. If all of these are set, we look for a table with this name. The table needs to have the fields ServerName NVARCHAR(128), DatabaseName NVARCHAR(128), and CheckID INT. We review the contents of that table, and then we’ll skip (or not output) the checks you specify:

  • ServerName = ‘MyServer, DatabaseName = NULL, CheckId = NULL – will not check anything on servername MyServer
  • ServerName = NULL, DatabaseName = ‘MyDB’, CheckId = NULL – will not check MyDB on any server
  • ServerName = ‘MyServer’, DatabaseName = ‘MyDB’, CheckId = NULL – will not check MyDB on MyServer
  • ServerName = NULL, DatabaseName = NULL, CheckId = 5 – will skip CheckId 5 on all databases
  • ServerName = NULL, DatabaseName = ‘MyDB’, CheckId = 5 – will skip CheckId 5 on MyDB

Parameters That Influence sp_Blitz®’s Output

@OutputType – if ‘TABLE’, we dump out the findings in, well, a table.  If ‘COUNT’, a vampire comes to your door and – wait, I’m hearing that’s not quite right. If ‘COUNT’, we output a single result row with the number of problems found.  You can use this in conjunction with monitoring software to find out when somebody’s been sleeping in your bed. If ‘SCHEMA’, we bypass all of the checks and just return the sp_Blitz® version number and the list of fields included the default result set.

@SummaryMode – if 1, we only return one row per distinct FindingsGroup and Finding and Priority combo, so if you have a thousand triggers or a dozen corrupt databases, we will only show the first one, plus a count of them in the Findings column.

Procedure Cache Health Parameters

@CheckProcedureCache – if 1, we grab the top 20-50 resource-intensive plans from the cache and analyze them for common design issues.  We’re looking for missing indexes, implicit conversions, user-defined functions, and more.  This fast scan isn’t incredibly detailed – we’re just looking for queries that might surprise you and require some performance tuning.

@OutputProcedureCache – if 1, we output a second result set that includes the queries, plans, and metrics we analyzed.  You can do your own analysis on these queries too looking for more problems.

@CheckProcedureCacheFilter – can be CPU, Reads, Duration, ExecCount, or null.  If you specify one, we’ll focus the analysis on those types of resource-intensive queries (like the top 20 by CPU use.)  If you don’t, we analyze the top 20 for all four (CPU, logical reads, total runtime, and execution count).  Typically we find that it’s not 80 different queries – it’s usually 25-40 queries that dominate all of the metrics.

Inserting sp_Blitz® Output Into a Table

For a user table, pass in the @OutputDatabaseName, @OutputSchemaName, and @OutputTableName parameters. If these are set, we check to see if the table exists. If not, we create it. Then, we insert the results into the table along with a ServerName and CheckDate. This way, you can look back at your results over time to see if things are getting better or worse. You can also use this to collect server health data across your company.

Temp tables, on the other hand, are a little trickier. sp_Blitz® uses INSERT INTO statements to get some of its results, so if you try this:

Then you’ll get an error like this:

Instead, what you need to do is create the temp table, then use the OPENROWSET command. It’s like opening a local linked server temporarily, punching a hole through space and time to fetch the results over the network (even though it’s the same server). Here’s an example:

The @OutputXMLasNVARCHAR parameter changes sp_Blitz®’s output so that it uses NVARCHAR(MAX) instead of XML – otherwise, the XML fields will break OPENROWSET.

To get the syntax for the default list of fields that sp_Blitz® will return, use:

That will return the current version number, plus the datatypes for the output fields. Keep in mind that if you use other parameters, you can change the output result set of sp_Blitz®, and you’re on your own.

More Free sp_Blitz® Downloads