PSBlitz

Navigation

Intro
What it does
Prerequisites
Installation
What it runs
Default check VS in-depth check
Output files
Usage examples
Report a bug
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.
As of version 4.0.1, PSBlitz is also compatible with Azure SQL DB and Azure SQL Managed Instance.
As of version 4.3.4, PSBlitz can be executed using PowerShell on Linux, the output will default to HTML regardless of the option used.

Compatibility

PSBlitz can be executed with:

Windows PowerShell 5.1
PowerShell 7.x
PowerShell 7.x on Linux

What it does

Outputs the following to an Excel spreadsheet or to an HTML report:

Instance information
Currently opened transactions (if any)
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
Information about Azure SQL DB resources, resource usage, database and database configuration
Information about all databases and their files or for a single database in case of a database-specific check
Query Store information in the case of a database-specific check on an eligible database - from sp_BlitzQueryStore
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

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
Execution plans (as .sqlplan files) - from sp_BlitzLock if any of the execution plans involved in deadlocks are still in the plan cache at the time of the check
Execution plans (as .sqlplan files) - from sp_BlitzQueryStore in the case of a database-specific check on an eligible database

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.
If PSBlitz detects an exclusive lock being held on a table or index it will automatically skip that table/index from the index fragmentation information and will make a note of that in the Execution Log.

Prerequisites

In order to be able to run the PSBlitz.ps1 script, you'll need to unblock it:
```
Unblock-File .\PSBlitz.ps1
```
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.
Sufficient permissions to query DMVs, server state, and get database objects' definitions.

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, Azure SQL DB, and Azure SQL Managed Instance, but not against Amazon RDS.

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
sp_BlitzQueryStore

Aside from the above scripts, PSBlitz also runs the following scripts to return sp_BlitzWho data, instance and resource information, TempDB usage, opened transactions, statistics and index fragmentation info:

GetDbInfo.sql
GetBlitzWhoData.sql
GetInstanceInfo.sql
GetAzureSQLDBInfo.sql
GetTempDBUsageInfo.sql
GetOpenTransactions.sql
GetIndexInfoForWholeDB.sql
GetStatsInfoForWholeDB.sql

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

Back to top

Paramaters

Parameter	Description
`-ServerName`	The name of your SQL Server instance or Azure SQL DB connection info. Accepted input format: `HostName\InstanceID` for named instances. `HostName,Port` when using a port number instead of an instance ID. `HostName` for default instances. For Azure SQL DB the format is: `YourServer.database.windows.net,PortNumber:YourDatabase` if you want to specify the port number. `YourServer.database.windows.net:YourDatabase` if you don't want to specify the port number. If your Azure SQL DB instance doesn't use the `database.windows.net` portion (e.g.: it's configured to use an IP instead) then you should provide the database name via the `-CheckDB` parameter. Other options: If you provide `?` or `Help` as a value for `-ServerName`, the script will return a brief help menu. If no value is provided, the script will go into interactive mode and prompt for the appropriate input
`-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. For Azure SQL DB Can also be used to provide the name of the Azure SQL DB database if you haven't provided it as part of the `-ServerName` paramter. If the database name is not provided here, nor as part of the `-ServerName`, and the environment is detected as Azure SQL DB, then you'll be prompted to provide the database name.
`-CacheTop`	Used to specify if more/less than the default top 10 queries should be returned for the sp_BlitzCache step. Only works for HTML output (`-ToHTM Y`). Has no effect on the `recent compilations` sort order.
`-CacheMinutesBack`	Used to specify how many minutes back to begin plan cache analysis. Defaults to entire contents of the plan cache since instance startup. In order to avoid missing the desired timeframe, the value is dynamically adjusted based on the runtime of PSBlitz up until the plan cache analysis point.
`-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 1000 (16.6 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 spBlitzCache - `[SortOrder][RowNumber].sqlplan`.
for plans obtained through spBlitzIndex (only available in SQL Server 2019 and above) - `MissingIndex[MissingIndexNumber].sqlplan`.
for plans obtained through the open transactions check - OpenTranCurrent_[SPID].sqlplan and/or OpenTranRecent_[SPID].sqlplan.
for plans obtained through spBlitzQueryStore - `QueryStore[RowNumber].sqlplan`
for plans obtained through spBlitzWho - `RunningNow[SPID]_[starttime][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, -OutputDir, -CacheTop, and -MaxTimeout are only available in command line mode.

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

Examples:
1. Print the help menu
```
.\PSBlitz.ps1 ?
```
  or
```
.\PSBlitz.ps1 Help
```
  or (recommended for detailed and well-structured help info)
```
Get-Help .\PSBlitz.ps1 -Full
```
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 
```
11. Run it against the YourDatabase database hosted in Azure SQL DB at yourserver.database.windows.net port 1433 via SQL login and password
```
.\PSBlitz.ps1 yourserver.database.windows.net,1433:YourDatabase -SQLLogin DBA1 -SQLPass SuperSecurePassword
```
12. Run it against the Azure SQL Managed Instance yourserver.database.windows.net
```
.\PSBlitz.ps1 yourserver.database.windows.net -SQLLogin DBA1 -SQLPass SuperSecurePassword
```
13. Run it against the Azure SQL Managed Instance yourserver.database.windows.net with an in-depth check while limiting index, stats, plan cache, and database info to YourDatabase
```
.\PSBlitz.ps1 yourserver.database.windows.net -SQLLogin DBA1 -SQLPass SuperSecurePassword -IsIndepth Y -CheckDB YourDatabase
```
  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