PSBlitz

Navigation

• Intro
• What it does
• Prerequisites
• Installation
• What it runs
• Default check VS in-depth check
• Output files
• Usage examples
• Screenshots
• License

Intro

Since I'm a big fan of Brent Ozar's SQL Server First Responder Kit and I've found myself in many situations where I would have liked a quick way to easily export the output of sp_Blitz, sp_BlitzCache, sp_BlitzFirst, sp_BlitzIndex, sp_BlitzLock, and sp_BlitzWho to Excel, as well as saving to disk the execution plans identified by sp_BlitzCache and deadlock graphs from sp_BlitzLock, I've decided to put together a PowerShell script that does just that. As of version 3.0.0, PSBlitz is also capable of exporting the report to HTML making Excel/Office no longer a hard requirement for running PSBlitz.

What it does

Outputs the following to an Excel spreadsheet:

• Instance information
• Wait stats - from sp_BlitzFirst
• Currently running queries - from sp_BlitzWho
• Instance health-related findings - from sp_Blitz
• tempdb size and usage information per object and session
• Index-related issues and recommendations - from sp_BlitzIndex
• Top 10 most resource intensive queries - from sp_BlitzCache
• Deadlock related information from the past 15 days - from sp_BlitzLock
• Statistics details for a given database - in the case of database-specific check or if a database accounts for at least 2/3 of the sp_BlitzCache data
• Index Fragmentation information for a given database - in the case of database-specific check or if a database accounts for at least 2/3 of the sp_BlitzCache data

Note: If the execution of PSBlitz took longer than 15 minutes up until the call to sp_BlitzLock, the timeframe for sp_BlitzLock will be narrowed down to the last 7 days in order to keep execution time within a reasonable amount.

Exports the following files:

• Execution plans (as .sqlplan files) - from the same dataset generated by sp_BlitzCache
• Execution plans (as .sqlplan files) - from the sample execution plans provided by sp_BlitzIndex @Mode = 0 and sp_BlitzIndex @Mode = 4 for missing index suggestions (only on SQL Server 2019)
• Execution plans (as .sqlplan files) of currently running sessions - from the same dataset generated by sp_BlitzWho
• Deadlock graphs (as .xdl files) - from the same dataset generated by sp_BlitzLock

Prerequisites

1. In order to be able to run the PSBlitz.ps1 script, you'll need to unblock it:

   Unblock-File .\PSBlitz.ps1

2. If you want the report to be in Excel format, then the MS Office suite needs to be installed on the machine where you're executing PSBlitz, otherwise use the HTML format.
3. Sufficient permissions to query DMVs, server state, and get database objects' definitions.

This should be ran from your workstation and not from the instance's host itself (why would you have the MS Word suite installed on a database server anyway?)

You don't need to have any of the sp_Blitz stored procedures present on the instance that you're executing PSBlitz.ps1 for, all the scripts are contained in the PSBlitz\Resources directory in non-stored procedure format.

Limitations:

• For the time being PSBlitz.ps1 can only run against SQL Server instances, not Azure SQL DB.

Back to top

Installation

Download the latest zip file from the Releases section of the repository and extract its contents.

Do not change the directory structure and file names.

Back to top

What it runs

PSBlitz.ps1 uses slightly modified, non-stored procedure versions, of the following components from Brent Ozar's SQL Server First Responder Kit:

• sp_Blitz
• sp_BlitzCache
• sp_BlitzFirst
• sp_BlitzIndex
• sp_BlitzLock
• sp_BlitzWho

Aside from the above scripts, PSBlitz also runs the following scripts to return sp_BlitzWho data, instance and resource information, as well as TempDB usage:

• GetBlitzWhoData.sql
• GetInstanceInfo.sql
• GetTempDBUsageInfo.sql

You can find the all the scripts in the repository's Resources directory

Back to top

Paramaters

Parameter	Description
-ServerName	Accepts either HostName\InstanceID (for named instances), HostName,Port when using a port number instead of an instance ID, or just HostName for default instances. If you provide either ? or Help as a value for -ServerName, the script will return a brief help menu.
-SQLLogin	The name of the SQL login used to run the script. If not provided, the script will use integrated security.
-SQLPass	The password for the SQL login provided via the -SQLLogin parameter, omit if -SQLLogin was not used.
-IsIndepth	Providing Y as a value will tell PSBlitz.ps1 to run a more in-depth check against the instance/database. Omit for default check.
-CheckDB	Used to provide the name of a specific database against which sp_BlitzIndex, sp_BlitzCache, and sp_BlitzLock will be ran. Omit to run against the whole instance.
-OutputDir	Used to provide a path where the output directory should be saved to. Defaults to PSBlitz.ps1's directory if not specified or a non-existent path is provided.
-ToHTML	Providing Y as a value will tell PSBlitz.ps1 to output the report as HTML instead of an Excel file. This is perfect when running PSBlitz from a machine that doesn't have Office installed.
-ZipOutput	Providing Y as a value will tell PSBlitz.ps1 to also create a zip archive of the output files.
-BlitzWhoDelay	Used to sepcify the number of seconds between each sp_BlitzWho execution. Defaults to 10 if not specified.
-ConnTimeout	Can be used to increased the timeout limit in seconds for connecting to SQL Server. Defaults to 15 seconds if not specified.
-MaxTimeout	Can be used to set a higher timeout for sp_BlitzIndex and Stats and Index info retrieval. Defaults to 800 (13.3 minutes)
-DebugInfo	Switch used to get more information for debugging and troubleshooting purposes.

Back to top

Default check VS in-depth check

• The default check will run the following:

sp_Blitz @CheckServerInfo = 1
sp_BlitzFirst @ExpertMode = 1, @Seconds = 30
sp_BlitzIndex @GetAllDatabases = 1, @Mode = 0
sp_BlitzCache @ExpertMode = 1, @SortOrder = 'CPU'/'avg cpu'	
sp_BlitzCache @ExpertMode = 1, @SortOrder = 'duration'/'avg duration'
sp_BlitzWho @ExpertMode = 1
sp_BlitzLock @StartDate = DATEADD(DAY,-15, GETDATE()), @EndDate = GETDATE()

• The in-depth check will run the following:

sp_Blitz @CheckServerInfo = 1, @CheckUserDatabaseObjects = 1	
sp_BlitzFirst @ExpertMode = 1, @Seconds = 30	
sp_BlitzFirst @SinceStartup = 1	
sp_BlitzIndex @GetAllDatabases = 1, @Mode = 1	
sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2	
sp_BlitzIndex @GetAllDatabases = 1, @Mode = 4	
sp_BlitzCache @ExpertMode = 1, @SortOrder = 'CPU'/'avg cpu'	
sp_BlitzCache @ExpertMode = 1, @SortOrder = 'reads'/'avg reads'	
sp_BlitzCache @ExpertMode = 1, @SortOrder = 'writes'/'avg writes'
sp_BlitzCache @ExpertMode = 1, @SortOrder = 'duration'/'avg duration'	
sp_BlitzCache @ExpertMode = 1, @SortOrder = 'executions'/'xpm'	
sp_BlitzCache @ExpertMode = 1, @SortOrder = 'memory grant'	
sp_BlitzCache @ExpertMode = 1, @SortOrder = 'recent compilations', @Top = 50	
sp_BlitzCache @ExpertMode = 1, @SortOrder = 'spills'/'avg spills'	
sp_BlitzWho @ExpertMode = 1	
sp_BlitzLock @StartDate = DATEADD(DAY,-15, GETDATE()), @EndDate = GETDATE()

• sp_BlitzWho will be executed as part of a background process at every 10 seconds. The frequency can be changed using the -BlitzWhoDelay parameter. Note that I don't recommend going with values lower than 5 for -BlitzWhoDelay, especially in a production environment.

• Using -CheckDB SomeDB will modify the executions of sp_Blitz, sp_BlitzCache, sp_BlitzIndex, and sp_BlitzLock as follows:

sp_Blitz @CheckServerInfo = 1, @CheckUserDatabaseObjects = 0
sp_BlitzIndex @GetAllDatabases = 0, @DatabaseName = 'SomeDB', @Mode = ...
sp_BlitzCache @ExpertMode = 1, @DatabaseName = 'SomeDB', @SortOrder = ...
sp_BlitzLock @StartDate = DATEADD(DAY,-15, GETDATE()), @EndDate = GETDATE(), @DatabaseName = 'SomeDB'

• Using -CheckDB SomeDB will also retrieve current statistics data and index fragmentation data for said database. Back to top

Output files

The output directory will be created by default in the PSBlitz directory where the PSBlitz.ps1 script lives. If you want to script to write the output directory to another path, use the -OutputDir parameter followed by the desired path (the path has to be valid otherwise PSBlitz will use the default output path).

Output directory name [HostName]_[Instance]_[TimeStamp] for an instance-wide check, or [HostName]_[Instance]_[TimeStamp]_[Database] for a database-specific check.

Deadlocks will be saved in the Deadlocks directory under the output directory.

Deadlock file naming convention - [EventDate]_[EventTime]_[RecordNumberOfDistinctDeadlockGroupVictim].xdl

Execution plans will be saved in the Plans directory under the output directory.

Execution plans file naming convention:

• for plans obtained through sp_BlitzCache - [SortOrder]_[RowNumber].sqlplan.
• for plans obtained through sp_BlitzIndex (only available in SQL Server 2019) - MissingIndex_[MissingIndexNumber].sqlplan.
• for plans obtained through sp_BlitzWho - RunningNow_[SPID]_[start_time]_[query_plan_hash].sqlplan. If no query plan hash is returned by sp_BlitzWho, then 0x00 will be used.

Back to top

Usage examples

You can run PSBlitz.ps1 by simply right-clicking on the script and then clicking on "Run With PowerShell" which will execute the script in interactive mode, prompting you for the required input. Note that parameters like -DebugMode and -OutputDir are only available in command line mode.

Otherwise you can navigate to the directory where the script is in PowerShell and execute it by providing parameters and appropriate values.

• Examples:

1. Print the help menu

   .\PSBlitz.ps1 ?

   or

   .\PSBlitz.ps1 Help

2. Run it against the whole instance (named instance SQL01), with default checks via integrated security

   .\PSBlitz.ps1 Server01\SQL01

3. Run it against the whole instance listening on port 1433 on host Server01, with default checks via integrated security

   .\PSBlitz.ps1 Server01,1433

4. Run it against the whole instance, with in-depth checks via integrated security

   .\PSBlitz.ps1 Server01\SQL01 -IsIndepth Y

5. Run it against the whole instance, with in-depth checks via integrated security, and have sp_BlitzWho execute every 5 seconds

   .\PSBlitz.ps1 Server01\SQL01 -IsIndepth Y -BlitzWhoDelay 5

6. Run it with in-depth checks, limit sp_BlitzIndex, sp_BlitzCache, and sp_BlitzLock to YourDatabase only, via integrated security

   .\PSBlitz.ps1 Server01\SQL01 -IsIndepth Y -CheckDB YourDatabase

7. Run it against the whole instance, with default checks via SQL login and password

   .\PSBlitz.ps1 Server01\SQL01 -SQLLogin DBA1 -SQLPass SuperSecurePassword

8. Run it against a default instance residing on Server02, with in-depth checks via SQL login and password, while limmiting sp_BlitzIndex, sp_BlitzCache, and sp_BlitzLock to YourDatabase only

   .\PSBlitz.ps1 Server02 -SQLLogin DBA1 -SQLPass SuperSecurePassword -IsIndepth Y -CheckDB YourDatabase

9. Run the same command as above, but increase execution timeout for sp_BlitzIndex, stats and index info retrieval, while also increasing delay between sp_BlitzWHo executions as well as getting more verbose console output and saving the output directory to C:\temp

   .\PSBlitz.ps1 Server02 -SQLLogin DBA1 -SQLPass SuperSecurePassword -IsIndepth Y -CheckDB YourDatabase -MaxTimeout 1200 -BlitzWhoDelay 20 -DebugInfo -OutputDir C:\Temp

10. Run PSBlitz but return the report as HTML instead of XLSX while also creating a zip archive of the output files.

    .\PSBlitz.ps1 Server01\SQL01 -ToHTML Y -ZipOutput Y 

Note that -ServerName is a positional parameter, so you don't necessarily have to specify the parameter's name as long as the first thing after the script's name is the instance

Back to top

Screenshots

Back to top