PSBlitz

A PowerShell-based SQL Server performance diagnostics and health check tool.

<a name="header1"></a>

Navigation

• Intro
• Features overview
• Compatibility
• Prerequisites
• Installation
• What it does
• Default check VS in-depth check
• Output files
• Usage examples
• Acknowledgments
• Contributing
• Support
• 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.
As of version 5.3.0, PSBlitz replaces the non-stred-procedure version of sp_BlitzQuery store with a modified, non-stored-procedure, version of sp_QuickieStore

Features overview

• SQL Server health checks
• Performance diagnostics
• Query analysis
• Deadlock investigation
• Azure SQL DB support
• Cross-platform compatibility

Compatibility

PSBlitz can be executed with:

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

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.

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.

Installation

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

As of version 5.1.0, PSBlitz.ps1 from the PSBlitz.zip archive found in every release is signed with a code-signing certificate.

For versions pre 5.1.0, if your PowerShell execution policy is RemoteSigned, you would need to run the following command (update the path to match yours):

Unblock-File C:\PathToFolder\PSBlitz\PSBlitz.ps1

Do not change the directory structure and/or file names.

Back to top

What it does

PSBlitz.ps1 uses slightly modified, non-stored-procedure versions, of the following components from Brent Ozar's SQL Server First Responder Kit, as well as Erik Darling's sp_QuickieStore.
You can find the all the scripts in the repository's Resources directory.

Note that I'm using the original stored procedure names puerly for example purposes, PSBlitz does not create or require the sp_Blitz stored procedures to exist on the instance.

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_QuickieStore
• 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_QuickieStore 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.

• If the instance has 50 or more user databases, PSBlitz will automatically limit the following checks to the database that appears the most in the data returned by the cache related checks:

  • Index Summary
  • Index Usage Details
  • (Detailed) Index Diagnosis

  The behavior can be controlled via the -MaxUsrDBs parameter, but only change the value if most of those databases don't have too many tables, or you've opted to output to HTML and have enough RAM for PS to handle the data (PSBlitz will limit the output to 10k records if more rows are returned)

• If the database targeted by the "stats info" and "index fragmentation info" steps have lots of tables/indexes/partitions/statistics, the following limits will be applied:

  • Stats Info - Limited to 10k records ordered by modified percent descending.
  • Index Fragmentation Info - Limited to 20k records ordered by avg fragmentation percent descending, size descending.

Limitations

Check targets

• 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.

Excel

• If you're using a 32bit installation of Excel and opt for the xlsx output, you might run into "out of memory" errors.
  That's not an issue with PSBlitz, it's the direct result of opting to still use 32bit software in SELECT DATEPART(YEAR,GETDATE()) AS current_year.

Known issues

When running PSBlitz with the Excel output, if you (open and) close an Excel window in parallel with PSBlitz's execution you'll also cause the Excel session used by PSBlitz to close, leading to the following error message:
You cannot call a method on a null-valued expression.
Solution: Don't do that :)

When running PSBlitz with the Excel output, if the Excel report template was saved with focus on one of the pages that actually has data written to it during PSBlitz's execution, then there is a very noticeable performance degradation for all steps that involve writing to Excel.
Solution: Open PSBlitzOutput.xlsx (found in PSBlitz's Resources folder) click on the first Intro sheet, select cell A1, save and close the file.

Back to top

Paramaters

| Parameter | Description| |-----------|------------| |-ServerName| The name of