Getting Started with the SQL Server First Responder Kit

Your SQL Server database is slowly grinding to a halt, your DBA is on vacation, and you don’t know where to start. This is when you break out the SQL Server First Responder Kit. This open source project consists of a set of scripts to help the DBA, or accidental DBA, fix and tune a SQL Server instance.

These scripts are usually installed as stored procedures in the “master” database on your server. This, combined with the “sp_” prefix, ensures that they are available no matter which database you happen to be looking at.

Side note: SQL Server always looks in master first for stored procedures that begin with “sp_”. So if you use that prefix for normal, database specific stored procedures, you’ll slow down the server slightly as it looks in the wrong place.

sp_BlitzWho – Who is causing the immediate problem?

The first tool you generally use when something goes wrong. It will tell you who is connected, what they are running, and how badly it is affecting the database.

[Click on the image to enlarge it]

If you discover a runaway process that just needs to stop, you can use the “kill” command along with the relevant session id.

If the problem isn’t clear, you can then move on to sp_BlitzFirst.

sp_BlitzFirst – What are you waiting for?

The sp_BlitzFirst tool helps you discover what your database is waiting for. In the example, you can see the #1 problem is that things other than SQL Server are consuming too much of the CPU’s time.

[Click on the image to enlarge it]

Unless you are testing out the scripts on a developer’s machine, that’s a pretty unusual diagnosis. What’s more common is that you’ll find one or more “wait stats” to blame.

In SQL Server, everything that could possibly slow down a query is tracked as a wait stat. This includes disk and network I/O, locks on tables/rows, waiting for CPU or memory resources, etc. The link in the output will help with common wait types, but with hundreds of different wait types being tracked it can often be hard to find information on the specific wait stat that is affecting you.

sp_Blitz – Is this database even setup correctly?

Another tool that you should consider when first taking ownership of a database server is sp_Blitz. This tool will identify common problems with the way SQL Server databases are setup. Each issue includes information on how to solve the problem and a priority score indicating what order the issues should be addressed.

[Click on the image to enlarge it]

Here you see that many of the databases haven’t been backed up or checked for corruption in an unacceptably long time.

Other problems it can detect include:

• Bad configuration settings, especially the ones that are “wrong by default” such as the cost threshold for parallelism.
• Dangerous file locations such as storing transaction logs on the OS drive.
• Non-production licenses being used.
• Missing alerts for database corruption, low memory, etc.
• Common security setup errors such as incorrect database owners.

sp_BlitzCache – Which queries should be tuned?

With the immediate problems solved, you can start looking at ways to proactively improve performance. One tool for this is sp_BlitzCache. This looks at SQL Servers query plan cache to determine which queries are having the biggest impact on the database over time. It can also warn you about common problems in queries such as computed columns with scalar operators and implicit casts.

The main difference between sp_BlitzFirst and sp_BlitzCache is that sp_BlitzFirst looks at what is happening in real time. By contrast, sp_BlitzCache looks at historical data to help you identify trends, so it doesn’t require you to catch the problem query in the act.

sp_BlitzIndex – How are my indexes doing?

If performance problems seem to be systemic rather than isolated to specific queries, the next place to look is the indexes. It is a well-known fact that missing indexes can have a profoundly negative effect on performance, causing queries to easily take ten, one hundred, even more than a thousand times longer than it should.

An equally important problem is too many indexes. Besides telling you about missing indexes, sp_BlitzIndex can tell you when more time is being spent updating an index than actually using it. Unnecessarily updating indexes not only slow down writes, but can push other data out of the cache, effectively slowing down unrelated queries.

The SQL Server First Responder Kit was originally developed by Brent Ozar Unlimited and is now an open source project with an MIT license.