Navigation
- How to Install the Scripts
- How to Get Support
- Common Scripts:
- Performance Tuning:
- Backups and Restores:
- Parameters Common to Many of the Stored Procedures
- License MIT
You're a DBA, sysadmin, or developer who manages Microsoft SQL Servers. It's your fault if they're down or slow. These tools help you understand what's going on in your server.
- When you want an overall health check, run sp_Blitz.
- To learn which queries have been using the most resources, run sp_BlitzCache.
- To analyze which indexes are missing or slowing you down, run sp_BlitzIndex.
- To find out why the server is slow right now, run sp_BlitzFirst.
To install, download the latest release ZIP, then run the SQL files in the master database. (You can use other databases if you prefer.)
The First Responder Kit runs on:
- SQL Server on Windows - all versions that Microsoft supports. For end of support dates, check out the "Support Ends" column at https://sqlserverupdates.com.
- SQL Server on Linux - yes, fully supported except sp_AllNightLog and sp_DatabaseRestore, which require xp_cmdshell, which Microsoft doesn't provide on Linux.
- Amazon RDS SQL Server - fully supported.
- Azure SQL DB - not supported. Some of the procedures work, but some don't, and Microsoft has a tendency to change DMVs in Azure without warning, so we don't put any effort into supporting it. If it works, great! If not, any changes to make it work would be on you. See the contributing.md file for how to do that.
If you're stuck with versions of SQL Server that Microsoft no longer supports, like SQL Server 2008, check the Deprecated folder for older versions of the scripts which may work, depending on your versions and compatibility levels.
For SQL Server, to install all of the scripts at once, open Install-All-Scripts.sql in SSMS or Azure Data Studio, switch to the database where you want to install the procs, and run it. It will install the stored procedures if they don't already exist, or update them to the current version if they do exist.
For Azure SQL DB, use Install-Azure.sql, which will only install the procs that are compatible with Azure SQL DB.
If you hit an error when running Install-All-Scripts, it's likely because you're using an older version of SQL Server that Microsoft no longer supports. In that case, check out the Deprecated folder. That's where we keep old versions of the scripts around as a last-ditch effort - but really, if Microsoft won't support their own old stuff, you shouldn't try to do it either.
We recommend installing these stored procedures in the master database, but if you want to use another one, that's totally fine - they're all supported in any database - but just be aware that you can run into problems if you have these procs in multiple databases. You may not keep them all up to date, and you may hit an issue when you're running an older version.
There are a couple of Install-Core scripts included for legacy purposes, for folks with installers they've built. You can ignore those.
Everyone here is expected to abide by the Contributor Covenant Code of Conduct.
When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. If you need a free invite, hit SQLslack.com. Be patient - it's staffed with volunteers who have day jobs, heh.
When you find a bug or want something changed, read the contributing.md file.
When you have a question about what the scripts found, first make sure you read the "More Details" URL for any warning you find. We put a lot of work into documentation, and we wouldn't want someone to yell at you to go read the fine manual. After that, when you've still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes us!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you're working with.
Run sp_Blitz daily or weekly for an overall health check. Just run it from SQL Server Management Studio, and you'll get a prioritized list of issues on your server right now.
Output columns include:
- Priority - 1 is the most urgent, stuff that could get you fired. The warnings get progressively less urgent.
- FindingsGroup, Findings - describe the problem sp_Blitz found on the server.
- DatabaseName - the database having the problem. If it's null, it's a server-wide problem.
- URL - copy/paste this into a browser for more information.
- Details - not just bland text, but dynamically generated stuff with more info.
Commonly used parameters:
- @CheckUserDatabaseObjects = 0 - by default, we check inside user databases for things like triggers or heaps. Turn this off (0) to make checks go faster, or ignore stuff you can't fix if you're managing third party databases. If a server has 50+ databases, @CheckUserDatabaseObjects is automatically turned off unless...
- @BringThePain = 1 - required if you want to run @CheckUserDatabaseObjects = 1 with over 50 databases. It's gonna be slow.
- @CheckServerInfo = 1 - includes additional rows at priority 250 with server configuration details like service accounts.
- @IgnorePrioritiesAbove = 50 - if you want a daily bulletin of the most important warnings, set @IgnorePrioritiesAbove = 50 to only get the urgent stuff.
Advanced tips:
In addition to the parameters common to many of the stored procedures, here are the ones specific to sp_Blitz:
- @Debug default 0. When 1, we print out messages of what we're doing. When 2, we print the dynamic queries as well
EXEC sp_Blitz @OutputDatabaseName = 'DBAtools', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzResults';Checks for the existence of a table DBAtools.dbo.BlitzResults, creates it if necessary, then adds the output of sp_Blitz into this table. This table is designed to support multiple outputs from multiple servers, so you can track your server's configuration history over time.
CREATE TABLE dbo.BlitzChecksToSkip (
ServerName NVARCHAR(128),
DatabaseName NVARCHAR(128),
CheckID INT
);
GO
INSERT INTO dbo.BlitzChecksToSkip (ServerName, DatabaseName, CheckID)
VALUES (NULL, 'SalesDB', 50)
sp_Blitz @SkipChecksDatabase = 'DBAtools', @SkipChecksSchema = 'dbo', @SkipChecksTable = 'BlitzChecksToSkip';Checks for the existence of a table named Fred - just kidding, named DBAtools.dbo.BlitzChecksToSkip. The table needs at least the columns shown above (ServerName, DatabaseName, and CheckID). For each row:
- If the DatabaseName is populated but CheckID is null, then all checks will be skipped for that database
- If both DatabaseName and CheckID are populated, then that check will be skipped for that database
- If CheckID is populated but DatabaseName is null, then that check will be skipped for all databases
sp_BlitzCache looks at your plan cache where SQL Server keeps track of which queries have run recently, and how much impact they've had on the server.
By default, it includes two result sets:
- The first result set shows your 10 most resource-intensive queries.
- The second result set explains the contents of the Warnings column - but it only shows the warnings that were produced in the first result set. (It's kinda like the most relevant glossary of execution plan terms.)
Output columns include:
- Database - the database context where the query ran. Keep in mind that if you fully qualify your object names, the same query might be run from multiple databases.
- Cost - the Estimated Subtree Cost of the query, what Kendra Little calls "Query Bucks."
- Query Text - don't copy/paste from here - it's only a quick reference. A better source for the query will show up later on.
- Warnings - problems we found.
- Created At - when the plan showed up in the cache.
- Last Execution - maybe the query only runs at night.
- Query Plan - click on this, and the graphical plan pops up.
The @SortOrder parameter lets you pick which top 10 queries you want to examine:
- reads - logical reads
- CPU - from total_worker_time in sys.dm_exec_query_stats
- executions - how many times the query ran since the CreationDate
- xpm - executions per minute, derived from the CreationDate and LastExecution
- recent compilations - if you're looking for things that are recompiling a lot
- memory grant - if you're troubleshooting a RESOURCE_SEMAPHORE issue and want to find queries getting a lot of memory
- writes - if you wanna find those pesky ETL processes
- You can also use average or avg for a lot of the sorts, like @SortOrder = 'avg reads'
- all - sorts by all the different sort order options, and returns a single result set of hot messes. This is a little tricky because:
- We find the @Top N queries by CPU, then by reads, then writes, duration, executions, memory grant, spills, etc. If you want to set @Top > 10, you also have to set @BringThePain = 1 to make sure you understand that it can be pretty slow.
- As we work through each pattern, we exclude the results from the prior patterns. So for example, we get the top 10 by CPU, and then when we go to get the top 10 by reads, we exclude queries that were already found in the top 10 by CPU. As a result, the top 10 by reads may not really be the top 10 by reads - because some of those might have been in the top 10 by CPU.
- To make things even a little more confusing, in the Pattern column of the output, we only specify the first pattern that matched, not all of the patterns that matched. It would be cool if at some point in the future, we turned this into a comma-delimited list of patterns that a query matched, and then we'd be able to get down to a tighter list of top queries. For now, though, this is kinda unscientific.
- query hash - filters for only queries that have multiple cached plans (even though they may all still be the same plan, just different copies stored.) If you use @SortOrder = 'query hash', you can specify a second sort order with a comma, like 'query hash, reads' in order to find only queries with multiple plans, sorted by the ones doing the most reads. The default second sort is CPU.
Other common parameters include:
- @Top = 10 - by default, you get 10 plans, but you can ask for more. Just know that the more you get, the slower it goes.
- @ExportToExcel = 1 - turn this on, and it doesn't return XML fields that would hinder you from copy/pasting the data into Excel.
- @ExpertMode = 1 - turn this on, and you get more columns with more data. Doesn't take longer to run though.
- @IgnoreSystemDBs = 0 - if you want to show queries in master/model/msdb. By default we hide these. Additionally hides queries from databases named
dbadmin,dbmaintenance, anddbatools. - @MinimumExecutionCount = 0 - in servers like data warehouses where lots of queries only run a few times, you can set a floor number for examination.
In addition to the parameters common to many of the stored procedures, here are the ones specific to sp_BlitzCache:
- @OnlyQueryHashes - if you want to examine specific query plans, you can pass in a comma-separated list of them in a string.
- @IgnoreQueryHashes - if you know some queries suck and you don't want to see them, you can pass in a comma-separated list of them.
- @OnlySqlHandles, @IgnoreSqlHandles - just like the above two params
- @DatabaseName - if you only want to analyze plans in a single database. However, keep in mind that this is only the database context. A single query that runs in Database1 can join across objects in Database2 and Database3, but we can only know that it ran in Database1.
- @SlowlySearchPlansFor - lets you search for strings, but will not find all results due to a bug in the way SQL Server removes spaces from XML. If your search string includes spaces, SQL Server may remove those before the search runs, unfortunately.
- We skip databases in an Availability Group that require read-only intent. If you wanted to contribute code to enable read-only intent databases to work, look for this phrase in the code: "Checking for Read intent databases to exclude".
When performance emergencies strike, this should be the first stored proc in the kit you run.
It takes a sample from a bunch of DMVs (wait stats, Perfmon counters, plan cache), waits 5 seconds, and then takes another sample. It examines the differences between the samples, and then gives you a prioritized list of things that might be causing performance issues right now. Examples include:
- Data or log file growing (or heaven forbid, shrinking)
- Backup or restore running
- DBCC operation happening
If no problems are found, it'll tell you that too. That's one of our favorite features because you can have your help desk team run sp_BlitzFirst and read the output to you over the phone. If no problems are found, you can keep right on drinking at the bar. (Ha! Just kidding, you'll still have to close out your tab, but at least you'll feel better about finishing that drink rather than trying to sober up.)
Common sp_BlitzFirst parameters include:
- @Seconds = 5 by default. You can specify longer samples if you want to track stats during a load test or demo, for example.
- @ShowSleepingSPIDs = 0 by default. When set to 1, shows long-running sleeping queries that might be blocking others.
- @ExpertMode = 0 by default. When set to 1, it calls sp_BlitzWho when it starts (to show you what queries are running right now), plus outputs additional result sets for wait stats, Perfmon counters, and file stats during the sample, then finishes with one final execution of sp_BlitzWho to show you what was running at the end of the sample. When set to 2, it does the same as 1, but skips the calls to sp_BlitzWho.
You can log sp_BlitzFirst performance data to tables by scheduling an Agent job to run sp_BlitzFirst every 15 minutes with these parameters populated:
- @OutputDatabaseName = typically 'DBAtools'
- @OutputSchemaName = 'dbo'
- @OutputTableName = 'BlitzFirst' - the quick diagnosis result set goes here
- @OutputTableNameFileStats = 'BlitzFirst_FileStats'
- @OutputTableNamePerfmonStats = 'BlitzFirst_PerfmonStats'
- @OutputTableNameWaitStats = 'BlitzFirst_WaitStats'
- @OutputTableNameBlitzCache = 'BlitzCache'
- @OutputTableNameBlitzWho = 'BlitzWho'
All of the above OutputTableName parameters are optional: if you don't want to collect all of the stats, you don't have to. Keep in mind that the sp_BlitzCache results will get large, fast, because each execution plan is megabytes in size.
You can also log your own activities like tuning queries, adding indexes, or changing configuration settings. To do it, run sp_BlitzFirst with these parameters:
- @OutputDatabaseName = typically 'DBAtools'
- @OutputSchemaName = 'dbo'
- @OutputTableName = 'BlitzFirst' - the quick diagnosis result set goes here
- @LogMessage = 'Whatever you wanna show in your monitoring tool'
Optionally, you can also pass in:
- @LogMessagePriority = 1
- @LogMessageFindingsGroup = 'Logged Message'
- @LogMessageFinding = 'Logged from sp_BlitzFirst' - you could use other values here to track other data sources like DDL triggers, Agent jobs, ETL jobs
- @LogMessageURL = 'https://OurHelpDeskSystem/ticket/?12345' - or maybe a Github issue, or Pagerduty alert
- @LogMessageCheckDate = '2017/10/31 11:00' - in case you need to log a message for a prior date/time, like if you forgot to log the message earlier
SQL Server tracks your indexes: how big they are, how often they change, whether they're used to make queries go faster, and which indexes you should consider adding. The results columns are fairly self-explanatory.
By default, sp_BlitzIndex analyzes the indexes of the database you're in (your current context.)
Common parameters include:
- @DatabaseName - if you want to analyze a specific database
- @SchemaName, @TableName - if you pass in these, sp_BlitzIndex does a deeper-dive analysis of just one table. You get several result sets back describing more information about the table's current indexes, foreign key relationships, missing indexes, and fields in the table.
- @GetAllDatabases = 1 - slower, but lets you analyze all the databases at once, up to 50. If you want more than 50 databases, you also have to pass in @BringThePain = 1.
- @ThresholdMB = 250 - by default, we only analyze objects over 250MB because you're busy.
- @Mode = 0 (default) - returns high-priority (1-100) advice on the most urgent index issues.
- @Mode = 4: Diagnose Details - like @Mode 0, but returns even more advice (priorities 1-255) with things you may not be able to fix right away, and things we just want to warn you about.
- @Mode = 1: Summarize - total numbers of indexes, space used, etc per database.
- @Mode = 2: Index Usage Details - an inventory of your existing indexes and their usage statistics. Great for copy/pasting into Excel to do slice & dice analysis. This is the only mode that works with the @Output parameters: you can export this data to table on a monthly basis if you need to go back and look to see which indexes were used over time.
- @Mode = 3: Missing Indexes - an inventory of indexes SQL Server is suggesting. Also great for copy/pasting into Excel for later analysis.
sp_BlitzIndex focuses on mainstream index types. Other index types have varying amounts of support:
- Fully supported: rowstore indexes, columnstore indexes, temporal tables.
- Columnstore indexes: fully supported. Key columns are shown as includes rather than keys since they're not in a specific order.
- In-Memory OLTP (Hekaton): unsupported. These objects show up in the results, but for more info, you'll want to use sp_BlitzInMemoryOLTP instead.
- Graph tables: unsupported. These objects show up in the results, but we don't do anything special with 'em, like call out that they're graph tables.
- Spatial indexes: unsupported. We call out that they're spatial, but we don't do any special handling for them.
- XML indexes: unsupported. These objects show up in the results, but we don't include the index's columns or sizes.
In addition to the parameters common to many of the stored procedures, here are the ones specific to sp_BlitzIndex:
- @SkipPartitions = 1 - add this if you want to analyze large partitioned tables. We skip these by default for performance reasons.
- @SkipStatistics = 0 - right now, by default, we skip statistics analysis because we've had some performance issues on this.
- @Filter = 0 (default) - 1=No low-usage warnings for objects with 0 reads. 2=Only warn for objects >= 500MB
- @OutputDatabaseName, @OutputSchemaName, @OutputTableName - these only work for @Mode = 2, index usage detail.
Checks either the System Health session or a specific Extended Event session that captures deadlocks and parses out all the XML for you.
Parameters you can use:
- @Top: Use if you want to limit the number of deadlocks to return. This is ordered by event date ascending.
- @DatabaseName: If you want to filter to a specific database
- @StartDate: The date you want to start searching on.
- @EndDate: The date you want to stop searching on.
- @ObjectName: If you want to filter to a specific table. The object name has to be fully qualified 'Database.Schema.Table'
- @StoredProcName: If you want to search for a single stored procedure. Don't specify a schema or database name - just a stored procedure name alone is all you need, and if it exists in any schema (or multiple schemas), we'll find it.
- @AppName: If you want to filter to a specific application.
- @HostName: If you want to filter to a specific host.
- @LoginName: If you want to filter to a specific login.
- @EventSessionPath: If you want to point this at an XE session rather than the system health session.
Known issues:
- If your database has periods in the name, the deadlock report itself doesn't report the database name correctly. More info in closed issue 2452.
This is like sp_who, except it goes into way, way, way more details.
It's designed for query tuners, so it includes things like memory grants, degrees of parallelism, and execution plans.
Retrieves data from the output tables where you are storing your sp_BlitzFirst output.
Parameters include:
- @StartDate: When you want to start seeing data from , NULL will set @StartDate to 1 hour ago.
- @EndDate: When you want to see data up to, NULL will get an hour of data since @StartDate.
- @Databasename: Filter results by database name where possible, Default: NULL which shows all.
- @Servername: Filter results by server name, Default: @@SERVERNAME.
- @OutputDatabaseName: Specify the database name where where we can find your logged sp_BlitzFirst Output table data
- @OutputSchemaName: Schema which the sp_BlitzFirst Output tables belong to
- @OutputTableNameBlitzFirst: Table name where you are storing sp_BlitzFirst @OutputTableNameBlitzFirst output, we default to BlitzFirst - you can Set to NULL to skip lookups against this table
- @OutputTableNameFileStats: Table name where you are storing sp_BlitzFirst @OutputTableNameFileStats output, we default to BlitzFirst_FileStats - you can Set to NULL to skip lookups against this table.
- @OutputTableNamePerfmonStats: Table name where you are storing sp_BlitzFirst @OutputTableNamePerfmonStats output, we default to BlitzFirst_PerfmonStats - you can Set to NULL to skip lookups against this table.
- @OutputTableNameWaitStats: Table name where you are storing sp_BlitzFirst @OutputTableNameWaitStats output, we default to BlitzFirst_WaitStats - you can Set to NULL to skip lookups against this table.
- @OutputTableNameBlitzCache: Table name where you are storing sp_BlitzFirst @OutputTableNameBlitzCache output, we default to BlitzCache - you can Set to NULL to skip lookups against this table.
- @OutputTableNameBlitzWho: Table name where you are storing sp_BlitzFirst @OutputTableNameBlitzWho output, we default to BlitzWho - you can Set to NULL to skip lookups against this table.
- @MaxBlitzFirstPriority: Max priority to include in the results from your BlitzFirst table, Default: 249.
- @BlitzCacheSortorder: Controls the results returned from your BlitzCache table, you will get a TOP 5 per sort order per CheckDate, Default: 'cpu' Accepted values 'all' 'cpu' 'reads' 'writes' 'duration' 'executions' 'memory grant' 'spills'.
- @WaitStatsTop: Controls the Top X waits per CheckDate from your wait stats table, Default: 10.
- @ReadLatencyThreshold: Sets the threshold in ms to compare against io_stall_read_average_ms in your filestats table, Default: 100.
- @WriteLatencyThreshold: Sets the threshold in ms to compare against io_stall_write_average_ms in your filestats table, Default: 100.
- @BringThePain: If you are getting more than 4 hours of data from your BlitzCache table with @BlitzCacheSortorder set to 'all' you will need to set BringThePain to 1.
- @Maxdop: Control the degree of parallelism that the queries within this proc can use if they want to, Default = 1.
- @Debug: Show sp_BlitzAnalysis SQL commands in the messages tab as they execute.
Example calls:
Get information for the last hour from all sp_BlitzFirst output tables