SQL Server First Responder Kit
Navigation
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_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.
How to Install the Scripts
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.
How to Get Support
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.
Back to top
sp_Blitz: Overall Health Check
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.
Note: sp_Blitz requires sp_ineachdb (also shipped in this repo) to be installed in the same database. Install-All-Scripts.sql installs both automatically, with sp_ineachdb placed first. If you’re installing scripts individually, install sp_ineachdb.sql before sp_Blitz.sql, or sp_Blitz will fail at runtime on its per-database checks. See the sp_ineachdb section below for details.
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.
Azure SQL DB support: sp_Blitz runs on Azure SQL Database (SERVERPROPERTY('EngineEdition') = 5) and skips ~40 checks that rely on things Azure SQL DB does not expose: backup history in msdb, SQL Agent jobs and alerts, xp_* extended procs (drive space, error log, registry reads), sys.master_files, sp_validatelogins, cross-database DBCC commands, linked servers, and database mirroring / AGs. When sp_Blitz detects Azure SQL DB, a CheckID 223 “Some Checks Skipped” informational row is returned so you know coverage is reduced. Email output via @EmailRecipients (Database Mail is unavailable) and remote output via @OutputServerName (no linked servers) are also not supported on Azure SQL DB - sp_Blitz prints a not-supported message and continues instead of erroring. Managed Instance (EngineEdition = 8) and Amazon RDS are unaffected and continue to use their existing code paths.
Advanced tips:
Back to top
Advanced sp_Blitz Parameters
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
Back to top
Writing sp_Blitz Output to a Table
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.
Back to top
Skipping Checks or Databases
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
Back to top
sp_BlitzCache: Find the Most Resource-Intensive Queries
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.
By default, SQL Server caches the estimated plan only. If you want the most recent actual query plan, you can enable the database-level option for last query plan stats with the following statement, but that does come with a CPU performance overhead, especially on database servers that run thousands of batch requests per second.
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;
Common sp_BlitzCache Parameters
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, and dbatools.
- @IgnoreReadableReplicaDBs = 0 - if you want to analyze the plan cache on an Availability Group readable replica. You will also have to connect to the replica using ApplicationIntent = ReadOnly, since SQL Server itself will abort queries that try to do work in readable secondaries.
- @MinimumExecutionCount = 0 - in servers like data warehouses where lots of queries only run a few times, you can set a floor number for examination.
Back to top
Advanced sp_BlitzCache Parameters
In addition to the parameters common to many of the stored procedures, here are the ones specific to sp_BlitzCache:
- @AI - when set to 2, returns a prompt you can copy into your favorite LLM to get advice on the query. When set to 1, we’ll actually call the LLM for you on SQL Server 2025 or newer, or Azure SQL DB. Learn more about using AI with the First Responder Kit.
- @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.
sp_BlitzCache Known Issues
- 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”.
Back to top
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.
- @EmergencyMode = 1 by default. When set to 0, it runs potentially slow checks that are skipped otherwise.
Logging sp_BlitzFirst to Tables
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
Back to top
sp_BlitzIndex: Tune Your Indexes
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.
- 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.
Back to top
Advanced sp_BlitzIndex Parameters
In addition to the parameters common to many of the stored procedures, here are the ones specific to sp_BlitzIndex:
- @AI - when set to 2, returns a prompt you can copy into your favorite LLM to get index advice for a table (requires @TableName). When set to 1, we’ll actually call the LLM for you on SQL Server 2025 or newer, or Azure SQL DB. Learn more about using AI with the First Responder Kit.
- @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.
- @UsualStatisticsSamplingPercent = 100 (default) - By default, @SkipStatistics = 0 with either @Mode = 0 or @Mode = 4 does not inform you of persisted statistics sample rates if that rate is 100. Use a different float if you usually persist a different sample percentage and do not want to be warned about it. Use NULL if you want to hear about every persisted sample rate.
- @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.
Back to top
sp_BlitzLock: Deadlock Analysis
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:
- @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:
- In Azure SQL DB, Microsoft doesn’t seem to be running system health sessions for you. If you get the error “A session with the name system_health does not exist or is not currently active”, you’ll need to start your own Extended Events session to capture deadlocks. We don’t have documentation on that, unfortunately, but we’d love to get help from someone else writing that!
- 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.
Back to top
sp_BlitzWho: What Queries are Running Now
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.
Back to top
sp_BlitzPlanCompare: Compare a Query Plan Across Two Servers
When the same query runs differently on two SQL Servers (fast on dev, slow on prod), the cause is almost always a difference in environment: stats, indexes, row counts, sniffed parameters, compat level, cardinality estimator version, hardware, MAXDOP, or live contention. sp_BlitzPlanCompare diffs two servers along every dimension that affects a specific query plan and returns a prioritized list of differences, the two plans side by side, and - when applicable - a copy/paste reproducer for parameter sniffing.
Installation: sp_BlitzPlanCompare ships in the OptionalScripts folder and is not included in Install-All-Scripts.sql or Install-Azure.sql. To use it, open OptionalScripts/sp_BlitzPlanCompare.sql in SSMS, switch to the database where you want it installed (typically master so it’s callable from any DB via EXEC master.dbo.sp_BlitzPlanCompare), and run the script. For linked-server mode, install it on both servers.
Parameters
Plan identifiers — at least one required for modes 1 and 3 (mode 2 reads identity from the XML):
| Parameter |
Type |
Default |
Purpose |
@QueryPlanHash |
BINARY(8) |
NULL |
The query_plan_hash of a cached plan. Most specific identifier. |
@QueryHash |
BINARY(8) |
NULL |
The query_hash (logical query fingerprint). Stable across servers; usually narrows to 1–few plans. When >1 you get a disambiguation result set to pick a @QueryPlanHash. |
@StoredProcName |
NVARCHAR(400) |
NULL |
Proc name — bare (usp_Foo), schema-qualified (dbo.usp_Foo), or three-part ([db].[schema].[usp_Foo]). Resolved via OBJECT_ID(). Multi-statement procs typically return multiple plans; use the disambiguation result set to pick one. |
Narrower — must accompany an identifier (cannot stand alone):
| Parameter |
Type |
Default |
Purpose |
@DatabaseName |
SYSNAME |
NULL |
Scopes plan lookup (and @StoredProcName resolution) to this database. On Azure SQL DB you must be connected to this database — cross-DB OBJECT_ID() isn’t supported. |
Comparison source (exactly one, or none for mode 1):
| Parameter |
Type |
Default |
Purpose |
@CompareToXML |
XML |
NULL |
Snapshot XML produced by a prior mode-1 run on the other server. Triggers mode 2. |
@LinkedServer |
SYSNAME |
NULL |
Name of a configured linked server (with RPC OUT). Combine with a plan identifier for mode 3. |
Misc:
| Parameter |
Type |
Default |
Purpose |
@Help |
BIT |
0 |
Prints usage and parameter docs and returns. |
@Debug |
BIT |
0 |
Prints the dynamic SQL used for plan lookup and linked-server invocation, plus per-DB iteration notes. |
Three operating modes
| Mode |
You supply |
What it does |
Output |
| 1. Emit |
Any plan identifier(s) |
Snapshots the local plan + its environment. |
A single CallStack cell with a ready-to-run EXEC sp_BlitzPlanCompare @CompareToXML = N'...'; — paste into the other server. |
| 2. Compare from XML |
@CompareToXML only |
Resolves the local plan by query_hash from the snapshot, then diffs. |
Full diff result set (below). |
| 3. Linked server |
Plan identifier(s) + @LinkedServer |
Calls sp_BlitzPlanCompare on the remote over RPC, shreds the returned snapshot, diffs locally. Requires the proc installed on both sides. |
Full diff result set (below). |
Plan identifier examples
/* 1. You know the plan hash exactly */
EXEC dbo.sp_BlitzPlanCompare @QueryPlanHash = 0xABCD1234567890EF;
/* 2. You only know the query hash (usually enough) */
EXEC dbo.sp_BlitzPlanCompare @QueryHash = 0x1234567890ABCDEF;
/* 3. You know the proc name */
EXEC dbo.sp_BlitzPlanCompare
@StoredProcName = 'dbo.usp_GetTopPosts',
@DatabaseName = 'StackOverflow';
/* Multi-statement proc: use the disambiguation result set to pick one statement */
EXEC dbo.sp_BlitzPlanCompare @StoredProcName = 'dbo.big_proc';
/* -> returns one row per cached plan with set_options + query_text_snippet.
Copy a query_plan_hash_text value and re-run with @QueryPlanHash. */
Typical copy/paste workflow (most common)
/* Step 1 — on the server where you noticed the slowness */
EXEC dbo.sp_BlitzPlanCompare @QueryPlanHash = 0xABCD1234567890EF;
You get back a single CallStack column — a complete EXEC dbo.sp_BlitzPlanCompare @CompareToXML = N'...'; with the snapshot XML already embedded and single quotes escaped. Click the cell, copy it, paste it into a query window connected to the OTHER server, hit F5. The comparison runs there and returns the prioritized diff plus both plans as clickable XML.
How cross-server plan resolution works: the snapshot carries the stable QueryHash attribute (not just the QueryPlanHash). When the other server runs the compare, it looks up its own cached plan by query_hash — because the whole point of comparing is that the plan on the other server is probably different. That divergence is the mystery you’re investigating.
Linked-server one-call workflow
/* Needs RPC OUT and the proc installed on both sides. */
EXEC dbo.sp_BlitzPlanCompare @QueryPlanHash = 0xABCD1234567890EF,
@LinkedServer = 'OtherSrv';
The local side passes its query_hash (not query_plan_hash) to the remote — since plan hashes almost always differ, while query hashes are stable. The remote resolves its own matching plan and hands back its snapshot; the local side shreds it and emits the diff. If the two servers compiled different plans (the usual case when one is slow), both plans come back as clickable XML in the second result set.
Output result sets
First result set — the diff (one row per difference, ordered by Priority):
| Column |
Description |
Priority |
0 = header metadata, 1 = plan-breaking / setup errors, 5 = operator row-count divergence (same-shape plans), 10 = parameter sniffing, 15 = CE drift / operator divergence (different-shape plans), 20 = major perf drivers (DOP, memory grant, hardware, wait stats), 25 = index quality, 30 = plan warnings / UDF time, 35 = live state, 55 = config drift, 100 = cosmetic / informational (PlanShape, versions), 200 = informational metadata echo. |
Category |
Server, Hardware, SpConfigure, TraceFlag, Database, DatabaseScopedConfig, Object, Index, Statistics, RowCount, PlanAttribute, PlanWarning, PlanRuntime, PlanWait, Parameter, PlanShape, OperatorVariance, LiveState, ForcedPlan, Meta. |
Setting |
Specific knob or metric within the category (e.g. max degree of parallelism, CompiledValue, CardinalityEstimationModelVersion, Reproducer). |
Object |
Scoped identifier: db.schema.table, db.schema.table.index, NodeId N: OpName on ..., etc. |
LocalValue, RemoteValue |
Side-by-side values. |
Finding |
One-line explanation of the diff. |
URL |
Link to more info. |
Details |
Extra context - plan-source flags, shape-match verdict, operator metadata. |
CallStack |
Typed XML — populated only on the Parameter.Reproducer row with a click-to-view reproducer containing two blocks of copy/paste-ready EXECs (one per server) that re-compile the proc/ad-hoc SQL with each side’s sniffed parameter values. |
Second result set — the two plans (one row per server):
| Column |
Description |
Server |
Local or Remote. |
ServerName |
@@SERVERNAME of the source. |
PlanSource |
Actual (LAST_QUERY_PLAN_STATS), Cached estimated, (unknown). |
QueryPlan |
Typed XML ShowPlanXML — click it in SSMS to open the plan diagram. |
What’s compared
- Server and hardware:
@@SERVERNAME, edition, version, CPU count, scheduler count, physical memory, max server memory, committed target.
- sp_configure allowlist: MAXDOP, CTFP, max/min server memory, optimize for ad-hoc workloads, priority boost, lightweight pooling, query governor cost limit, max worker threads.
- Trace flags:
DBCC TRACESTATUS(-1) (box SQL + MI only; silently skipped on Azure SQL DB).
- Database config (per plan-referenced database): compat level, collation, RCSI, auto create/update stats, page verify, recovery model, Query Store on/off, parameterization, containment.
- Database-scoped configurations: the full row from
sys.database_scoped_configurations, including LEGACY_CARDINALITY_ESTIMATION, MAXDOP, LAST_QUERY_PLAN_STATS.
- Objects, indexes, statistics: presence, index definitions (type, unique, filter, key + included columns), stats headers (last update, modification counter, rows sampled), row counts and page counts.
- Forced plans: Query Store forced plans +
sys.plan_guides.
- Parameter sniffing: compiled values per parameter. When any value differs, a
Parameter.Reproducer row is emitted whose CallStack column is a clickable XML cell with copy/paste-ready EXEC [db].[schema].[proc] @p = v blocks for each server. For ad-hoc parameterized SQL, the reproducer uses sp_executesql form instead.
- Plan-level attributes: DOP, memory grant, CompileTime/CompileMemory, CachedPlanSize, CardinalityEstimationModelVersion, OptimizerHardwareDependentProperties (estimated memory grant, pages cached, available DOP, max compile memory), StatementOptmLevel, RetrievedFromCache. Numeric deltas ≤ 25% are suppressed as noise;
CardinalityEstimationModelVersion is exact-match regardless (70 vs 160 vs 170 all change optimizer behavior).
- Plan warnings: spills, memory grant warnings, convert warnings, NoStats, WaitStats.
- Runtime totals (PlanRuntime): elapsed time, CPU time, granted memory, max used memory, UDF elapsed/CPU, spill count (exact-match — 0 vs 1 matters). Populated when both plans are actual (
LAST_QUERY_PLAN_STATS). Sub-25% deltas suppressed except for spill count.
- Wait stats (PlanWait): pulled from
sys.query_store_wait_stats (not the plan XML — dm_exec_query_plan_stats doesn’t populate <WaitStats>). Bucketed by wait_category_desc. Silently skipped when Query Store is off.
- Operator-by-operator row variance (OperatorVariance): if both plans have the same shape (same operators on the same objects at the same NodeIds), we walk the operators in post-order starting from the top-right leaf, working right-to-left and top-to-bottom toward the root, and report the first operator where actual rows (or estimated rows, if runtime data isn’t available) differ by 25% or more. If shapes differ, only the root operator is compared - per-operator diffs aren’t meaningful when operators don’t line up. A
PlanShape informational row always accompanies this analysis and tells you whether shapes match and which metric was used.
- Live state: point-in-time snapshot of runnable tasks, worker count, blocked sessions, and top 5 wait types for the plan’s query hash. Sub-25% deltas suppressed.
Scope is limited to objects actually referenced in the plan — only databases, tables, indexes, and statistics that appear in the plan XML are compared.
- If
@QueryPlanHash doesn’t match any cached query_plan_hash on the local server, sp_BlitzPlanCompare automatically retries as query_hash. If exactly one plan matches, it uses that plan and prints a note explaining the fallback. If multiple plans match, you get the candidate query_plan_hash values and a RAISERROR asking you to pick one.
- If
@QueryPlanHash matches multiple cached plans (same hash, different compile contexts), the proc returns the matches with their set_options and a query text snippet so you can disambiguate, then RAISERRORs.
Plan source (actual vs estimated)
sp_BlitzPlanCompare resolves plans via sys.dm_exec_query_plan_stats first (the actual plan with runtime memory grant, real spill warnings, and per-operator runtime counters) and falls back to sys.dm_exec_query_plan (the cached estimated plan) when the actual isn’t available.
To get the actual plan:
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;
Requires SQL Server 2019+ or Azure SQL DB and adds a small per-execution CPU overhead. The diff surfaces a PlanAttribute.PlanSource row when the two servers used different sources. When both sides are actual, OperatorVariance uses ActualRows and PlanRuntime rows (elapsed, CPU, memory grant, spill count) are populated. When either side is estimated-only, OperatorVariance falls back to EstimateRows.
Compatibility
SQL Server 2017+, Azure SQL DB, Managed Instance, Hyperscale, Amazon RDS.
- Linked-server mode (3) needs box SQL or Managed Instance on the local server. Azure SQL DB and Synapse don’t support linked servers - use the copy/paste workflow (modes 1 + 2) instead.
- On Azure SQL DB, sessions are bound to a single user database, so the comparison is scoped to that database. Trace flags are unavailable and silently skipped.
- Managed Instance and Amazon RDS support all three modes with no additional restrictions beyond the permissions listed below.
Known limitations of v0.01
- Statistics comparison uses header data only (no histogram comparison).
- Plan lookup uses the plan cache only (no Query Store fallback when the plan isn’t cached). Wait-stat comparison does use Query Store.
- Resource Governor, triggers, FK/check constraints, collation drift, varchar/nvarchar datatype drift, fragmentation, fill factor, and index compression are not compared yet.
- Operator variance walks
RelOp/*/RelOp nesting - subquery RelOps nested inside predicate ScalarOperator trees may not get parented correctly, but the overall first-variance call is still accurate for conventional plans.
sp_BlitzPlanCompare Permissions Required
The proc only reads from DMVs and catalog views - no writes, no DDL. On the local server (any mode), the caller needs:
VIEW SERVER STATE - for plan cache and OS DMVs (sys.dm_exec_query_stats, sys.dm_os_sys_info, sys.dm_exec_query_memory_grants, etc.).VIEW DATABASE STATE on each plan-referenced database - for sys.database_scoped_configurations, sys.dm_db_partition_stats, sys.dm_db_stats_properties, sys.query_store_plan, sys.plan_guides.VIEW ANY DEFINITION (or VIEW DEFINITION on the specific objects) - so sys.tables, sys.indexes, sys.index_columns, sys.columns, sys.stats return rows.CONNECT to each plan-referenced database - the cross-database iteration uses USE [db] inside dynamic SQL.EXECUTE on dbo.sp_BlitzPlanCompare.
The simplest grant for most shops:
USE master;
GRANT VIEW SERVER STATE TO [YourLogin];
GRANT VIEW ANY DEFINITION TO [YourLogin];
GRANT EXECUTE ON dbo.sp_BlitzPlanCompare TO [YourLogin];
GO
/* Then in each plan-referenced database: */
USE [YourDatabase];
GRANT VIEW DATABASE STATE TO [YourUser];
GO
One gotcha: DBCC TRACESTATUS(-1) traditionally requires sysadmin (or ALTER SETTINGS on newer versions). The script wraps it in TRY/CATCH, so without the permission you don’t get an error - the TraceFlag category just reports as unsupported and trace-flag differences won’t be detected.
On Azure SQL DB: there’s no VIEW SERVER STATE. Use the database-scoped equivalents (VIEW DATABASE STATE plus the Azure-specific permissions that go with it). Trace flags are unsupported on Azure SQL DB anyway, so no loss there.
For linked-server mode (mode 3): the remote login mapped via the linked server needs the same permissions on the remote server - because the remote is running the same code path (mode 1, emit XML). It also needs EXECUTE on dbo.sp_BlitzPlanCompare on the remote (the proc must be installed on both sides).
The linked server itself must be configured with RPC OUT enabled. Without it, EXEC LinkedServer.master.dbo.sp_BlitzPlanCompare returns “Server ‘…’ is not configured for RPC.”
/* Enable RPC and RPC OUT on an existing linked server */
EXEC sp_serveroption @server = 'OtherSrv', @optname = 'rpc', @optvalue = 'true';
EXEC sp_serveroption @server = 'OtherSrv', @optname = 'rpc out', @optvalue = 'true';
GO
/* Verify */
SELECT name, is_rpc_out_enabled
FROM sys.servers
WHERE name = 'OtherSrv';
If the linked-server call fails with error 8509 (Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION)), the linked server is trying to promote the remote call to a distributed transaction and MSDTC isn’t reachable. Turn off remote proc transaction promotion on the linked server:
EXEC master.dbo.sp_serveroption
@server = N'OtherSrv',
@optname = N'remote proc transaction promotion',
@optvalue = N'false';
sp_BlitzPlanCompare detects error 8509 and emits a Priority-1 Setup.DtcPromotionFailed row with this fix command inline.
The login mapping (sp_addlinkedsrvlogin) decides which remote login your local session executes as. Whoever that login is on the remote server needs the same VIEW SERVER STATE / VIEW DATABASE STATE / VIEW ANY DEFINITION / EXECUTE grants listed above.
Back to top
sp_kill: Emergency Session Killer
When the server is on fire and you’re about to restart it out of desperation, run sp_kill instead. It collects all running session data, recommends which queries to kill (with reasons), optionally executes the kills for you, frees their plan cache entries, and logs everything.
By default, sp_kill runs in read-only mode - it just shows you what’s happening and suggests what to kill. You have to explicitly opt in to actually killing sessions.
/* Just show me what's running - no killing: */
EXEC sp_kill;
/* Kill all lead blockers: */
EXEC sp_kill @LeadBlockers = 'Y', @ExecuteKills = 'Y';
/* Kill a specific session: */
EXEC sp_kill @SPID = 55, @ExecuteKills = 'Y';
/* Kill sleeping sessions with open transactions idle for 5+ minutes: */
EXEC sp_kill @HasOpenTran = 'Y', @SPIDState = 'S',
@RequestsOlderThanSeconds = 300, @ExecuteKills = 'Y';
/* Show what we'd kill for a login, sorted by CPU: */
EXEC sp_kill @LoginName = 'DOMAIN\TroublesomeUser', @OrderBy = 'cpu';
Parameters for targeting sessions:
- @ExecuteKills = ‘Y’ or ‘N’ (default ‘N’) - whether to actually kill, or just show recommendations. When set to ‘Y’ with no targeting filters, all non-system sessions are killed - so always specify at least one filter to avoid killing everything.
- @SPID - target a specific session ID.
- @LoginName - kill sessions belonging to this login.
- @AppName - kill sessions from this application (supports LIKE wildcards).
- @DatabaseName - kill sessions using this database.
- @HostName - kill sessions from this host.
- @LeadBlockers = ‘Y’ - kill only lead blockers (sessions blocking others but not blocked themselves).
- @ReadOnly = ‘Y’ - only kill read-only queries (SELECTs with no writes).
- @SPIDState - ‘S’ for sleeping sessions only, ‘R’ for running only, NULL for both (default).
- @HasOpenTran = ‘Y’ - only target sessions with open transactions. Combine with @SPIDState = ‘S’ to catch sleeping sessions with forgotten transactions.
- @RequestsOlderThanSeconds - only target sessions whose last request started at least this many seconds ago.
- @OmitLogin - exclude sessions belonging to this login from kill recommendations.
- @OrderBy - sort order for kill recommendations: duration (default), cpu, reads, writes, tempdb, transactions. Useful when you want to kill the worst offenders one by one.
Safety features:
- Never kills system sessions (session_id <= 50) or its own session.
- Never kills sessions that are already rolling back.
- Before each kill, verifies the session still has the same query it had when captured. If the query changed (because the original finished and a new one started on the same session ID), the kill is skipped.
- Frees the plan cache entry for each killed query via DBCC FREEPROCCACHE.
- When @ExecuteKills = ‘Y’, shows progress messages like “Killing query 3 of 12 (SPID 78)…” so you can follow along.
Output table logging:
- @OutputDatabaseName, @OutputSchemaName, @OutputTableName - pass all three to log results to a persistent table. The table is created automatically if it doesn’t exist, and each run appends new rows. Includes who ran sp_kill, when, what parameters they used, what sessions were running, which ones were killed, and any errors.
Back to top
sp_BlitzAnalysis: Query sp_BlitzFirst output tables
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
EXEC sp_BlitzAnalysis
@StartDate = NULL,
@EndDate = NULL,
@OutputDatabaseName = 'DBAtools',
@OutputSchemaName = 'dbo',
@OutputTableNameFileStats = N'BlitzFirst_FileStats',
@OutputTableNamePerfmonStats = N'BlitzFirst_PerfmonStats',
@OutputTableNameWaitStats = N'BlitzFirst_WaitStats',
@OutputTableNameBlitzCache = N'BlitzCache',
@OutputTableNameBlitzWho = N'BlitzWho';
Exclude specific tables e.g lets exclude PerfmonStats by setting to NULL, no lookup will occur against the table and a skipped message will appear in the resultset
EXEC sp_BlitzAnalysis
@StartDate = NULL,
@EndDate = NULL,
@OutputDatabaseName = 'DBAtools',
@OutputSchemaName = 'Blitz',
@OutputTableNameFileStats = N'BlitzFirst_FileStats',
@OutputTableNamePerfmonStats = NULL,
@OutputTableNameWaitStats = N'BlitzFirst_WaitStats',
@OutputTableNameBlitzCache = N'BlitzCache',
@OutputTableNameBlitzWho = N'BlitzWho';
Known issues:
We are likely to be hitting some big tables here and some of these queries will require scans of the clustered indexes as there are no nonclustered indexes to cover the queries by default, keep this in mind if you are planning on running this in a production environment!
I have noticed that the Perfmon query can ask for a big memory grant so be mindful when including this table with large volumes of data:
SELECT
[ServerName]
, [CheckDate]
, [counter_name]
, [object_name]
, [instance_name]
, [cntr_value]
FROM [dbo].[BlitzFirst_PerfmonStats_Actuals]
WHERE CheckDate BETWEEN @FromDate AND @ToDate
ORDER BY
[CheckDate] ASC
, [counter_name] ASC;
Back to top
sp_BlitzBackups: How Much Data Could You Lose
Checks your backups and reports estimated RPO and RTO based on historical data in msdb, or a centralized location for [msdb].dbo.backupset.
Parameters include:
- @HoursBack – How many hours into backup history you want to go. Should be a negative number (we’re going back in time, after all). But if you enter a positive number, we’ll make it negative for you. You’re welcome.
- @MSDBName – if you need to prefix dbo.backupset with an alternate database name.
- @AGName – If you have more than 1 AG on the server, and you don’t know the listener name, specify the name of the AG you want to use the listener for, to push backup data. This may get used during analysis in a future release for filtering.
- @RestoreSpeedFullMBps, @RestoreSpeedDiffMBps, @RestoreSpeedLogMBps – if you know your restore speeds, you can input them here to better calculate your worst-case RPO times. Otherwise, we assume that your restore speed will be the same as your backup speed. That isn’t likely true - your restore speed will likely be worse - but these numbers already scare the pants off people.
- @PushBackupHistoryToListener – Turn this to 1 to skip analysis and use sp_BlitzBackups to push backup data from msdb to a centralized location (more the mechanics of this to follow)
- @WriteBackupsToListenerName – This is the name of the AG listener, and MUST have a linked server configured pointing to it. Yes, that means you need to create a linked server that points to the AG Listener, with the appropriate permissions to write data.
- @WriteBackupsToDatabaseName – This can’t be ‘msdb’ if you’re going to use the backup data pushing mechanism. We can’t write to your actual msdb tables.
- @WriteBackupsLastHours – How many hours in the past you want to move data for. Should be a negative number (we’re going back in time, after all). But if you enter a positive number, we’ll make it negative for you. You’re welcome.
An example run of sp_BlitzBackups to push data looks like this:
EXEC sp_BlitzBackups @PushBackupHistoryToListener = 1, -- Turn it on!
@WriteBackupsToListenerName = 'AG_LISTENER_NAME', -- Name of AG Listener and Linked Server
@WriteBackupsToDatabaseName = 'FAKE_MSDB_NAME', -- Fake MSDB name you want to push to. Remember, can't be real MSDB.
@WriteBackupsLastHours = -24 -- Hours back in time you want to go
In an effort to not clog your servers up, we’ve taken some care in batching things as we move data. Inspired by Michael J. Swart’s Take Care When Scripting Batches, we only move data in 10 minute intervals.
The reason behind that is, if you have 500 databases, and you’re taking log backups every minute, you can have a lot of data to move. A 5000 row batch should move pretty quickly.
Back to top
sp_DatabaseRestore: Easier Multi-File Restores
If you use Ola Hallengren’s backup scripts, DatabaseRestore.sql helps you rapidly restore a database to the most recent point in time.
Parameters include:
- @Database - the database’s name, like LogShipMe
- @RestoreDatabaseName
- @BackupPathFull - typically a UNC path like '\\FILESERVER\BACKUPS\SQL2016PROD1A\LogShipMe\FULL' that points to where the full backups are stored. Note that if the path doesn’t exist, we don’t create it, and the query might take 30+ seconds if you specify an invalid server name.
- @BackupPathDiff, @BackupPathLog - as with the Full, this should be set to the exact path where the differentials and logs are stored. We don’t append anything to these parameters.
- @MoveFiles, @MoveDataDrive, @MoveLogDrive - if you want to restore to somewhere other than your default database locations.
- @FileNamePrefix - Prefix to add to the names of all restored files. Useful when you need to restore different backups of the same database into the same directory.
- @RunCheckDB - default 0. When set to 1, we run Ola Hallengren’s DatabaseIntegrityCheck stored procedure on this database, and log the results to table. We use that stored proc’s default parameters, nothing fancy.
- @TestRestore - default 0. When set to 1, we delete the database after the restore completes. Used for just testing your restores. Especially useful in combination with @RunCheckDB = 1 because we’ll delete the database after running checkdb, but know that we delete the database even if it fails checkdb tests.
- @RestoreDiff - default 0. When set to 1, we restore the ncessary full, differential, and log backups (instead of just full and log) to get to the most recent point in time.
- @ContinueLogs - default 0. When set to 1, we don’t restore a full or differential backup - we only restore the transaction log backups. Good for continuous log restores with tools like sp_AllNightLog.
- @RunRecovery - default 0. When set to 1, we run RESTORE WITH RECOVERY, putting the database into writable mode, and no additional log backups can be restored.
- @ExistingDBAction - if the database already exists when we try to restore it, 1 sets the database to single user mode, 2 kills the connections, and 3 kills the connections and then drops the database.
- @Debug - default 0. When 1, we print out messages of what we’re doing in the messages tab of SSMS.
- @StopAt NVARCHAR(14) - pass in a date time to stop your restores at a time like ‘20170508201501’. This doesn’t use the StopAt parameter for the restore command - it simply stops restoring logs that would have this date/time’s contents in it. (For example, if you’re taking backups every 15 minutes on the hour, and you pass in 9:05 AM as part of the restore time, the restores would stop at your last log backup that doesn’t include 9:05AM’s data - but it won’t restore right up to 9:05 AM.)
- @SkipBackupsAlreadyInMsdb - default 0. When set to 1, we check MSDB for the most recently restored backup from this log path, and skip all backup files prior to that. Useful if you’re pulling backups from across a slow network and you don’t want to wait to check the restore header of each backup.
- @EnableBroker - default 0. When set to 1, we run RESTORE WITH ENABLE_BROKER, enabling the service broker. Unless specified, the service broker is disabled on restore even if it was enabled when the backup was taken.
For information about how this works, see Tara Kizer’s white paper on Log Shipping 2.0 with Google Compute Engine.
Back to top
sp_ineachdb: Run a Command in Each Database
sp_ineachdb is a drop-in replacement for the undocumented sp_MSforeachdb with saner filtering and error handling. Pass a command string using ? as a placeholder for the database name:
EXEC sp_ineachdb @command = N'SELECT DB_NAME() AS db, COUNT(*) AS tables FROM [?].sys.tables;';
Useful parameters:
@command - the T-SQL to run. ? (or whatever you set @replace_character to) gets replaced with the quoted database name.@database_list, @exclude_list - comma-separated lists of databases to include or exclude. Bracket-quote names that contain special characters.@name_pattern, @exclude_pattern - LIKE patterns applied to database names.@system_only, @user_only - limit to system DBs (master/model/msdb/tempdb/distribution) or user DBs.@recovery_model_desc, @compatibility_level, @is_read_only, @is_auto_close_on, @is_auto_shrink_on, @is_broker_enabled, @is_query_store_on, @user_access, @state_desc - filter by database property.@is_ag_writeable_copy = 1 - skip Availability Group secondaries.@print_dbname, @print_command, @print_command_only, @select_dbname - diagnostics for debugging your command string without running it.
Azure SQL DB support
Azure SQL DB forbids cross-database calls and 3-part object names, so “run in each database” collapses to “run in the current database” (Azure SQL DB sessions are bound to one user database anyway). sp_ineachdb detects Azure SQL DB via SERVERPROPERTY('EngineEdition') = 5 and adapts automatically:
- Seeds the database list with just the current database.
- Executes
@command via EXEC sys.sp_executesql instead of a 3-part dynamic call.
- Rewrites common
sp_MSforeachdb-style patterns in @command so they don’t have to be changed:
USE [?]; and USE ?; (with or without brackets or semicolons) are stripped - you can’t change database context in Azure SQL DB.[?].schema.object and ?.schema.object are collapsed to schema.object, turning 3-part names into 2-part names.
This means callers that already work with sp_MSforeachdb on box SQL (like sp_Blitz internals) can call sp_ineachdb on Azure SQL DB and Just Work.
Things to look out for on Azure SQL DB:
- Placeholders inside string literals get rewritten too.
PRINT 'See [?].sys.tables' will have [?]. stripped out - avoid putting the placeholder inside quoted strings. (This caveat also applies to box SQL, since @command text substitution happens everywhere.)
- Non-canonical whitespace isn’t matched.
USE [?]; (double space) or [?] . sys . tables (spaces between name parts) won’t be rewritten. Stick to the canonical USE [?]; and [?].sys.tables forms.
- Managed Instance is not affected. Azure SQL Managed Instance reports
EngineEdition = 8 and supports cross-database calls, so it uses the same code path as box SQL.
- Filter parameters still apply - the current database is added to the list, then the usual
@name_pattern, @user_only, property filters, etc. can still exclude it. If nothing matches, you get the normal No databases to process. message.
Back to top
Parameters Common to Many of the Stored Procedures
- @Help = 1 - returns a result set or prints messages explaining the stored procedure’s input and output. Make sure to check the Messages tab in SSMS to read it.
- @ExpertMode = 1 - turns on more details useful for digging deeper into results.
- @OutputDatabaseName, @OutputSchemaName, @OutputTableName - pass all three of these in, and the stored proc’s output will be written to a table. We’ll create the table if it doesn’t already exist. @OutputServerName will push the data to a linked server as long as you configure the linked server first and enable RPC OUT calls.
To check versions of any of the stored procedures, use their output parameters for Version and VersionDate like this:
DECLARE @VersionOutput VARCHAR(30), @VersionDateOutput DATETIME;
EXEC sp_Blitz
@Version = @VersionOutput OUTPUT,
@VersionDate = @VersionDateOutput OUTPUT,
@VersionCheckMode = 1;
SELECT
@VersionOutput AS Version,
@VersionDateOutput AS VersionDate;
Back to top
License
The SQL Server First Responder Kit uses the MIT License.
Back to top
评论