In previous part of this series, you learnt about checking overall health of Sql Server. Now we will go through, real-time performance monitoring of SQL Server. When you face sudden slowdowns, high CPU usage, or unexplained bottlenecks, having a quick and efficient way to diagnose issues are essential. This is where sp_BlitzFirst, a tool from Brent Ozar’s SQL Server First Responder Kit, comes into play.
sp_BlitzFirst
When there is a performance emergency, sp_BlitzFirst will be the first arrow that you will shoot to the SQL Server. This tool captures a snapshot of various DMVs (such as wait stats, Perfmon counters, and the plan cache), waits for 5 seconds, then takes another snapshot. By analyzing the differences between these samples, it generates a prioritized list of potential performance bottlenecks affecting your SQL Server at that moment. It collects and reports critical information such as:
- CPU, memory, and disk utilization
- Running queries and their impact on performance
- Wait statistics (identifying bottlenecks)
- Blocking and deadlocks
- File I/O latency
- Missing indexes and high-cost queries
Using sp_BlitzFirst
It supports many parameters. Based on the parameters, commonly you can use sp_BlitzFirst in three different modes:
- Helpdesk Mode/No Parameter
- Expert Mode
- Since SQL Server starts mode
Helpdesk Mode/No Parameter
Brent Ozar and his team designed and developed sp_BlitzFirst in such a way that any helpdesk operator can run this without putting any parameter and check the current status of the SQL Server. In case there is any issue, it shows it and also tells if it finds everything fine. For example, in diagram-1 shows no issues of SQL Server.
exec sp_BlitzFirst;
Diagram-1: Output of sp_BlitzFirst without any parameter
Expert Mode
Putting ExpertMode and Seconds parameters, you can run sp_BlitzFirst as expert mode. It will produce a series of outputs one after another. Remember, in a busy SQL Server environments, in might take longer time to come up with all the results.
exec sp_BlitzFirst @expertmode=1, @seconds=60;
Diagram-2: Output of sp_BlitzFirst in expert mode
The major outputs are given below. Let's explore them deeply now.
- Currently running queries
- Headlines/high priority result sets
- Top wait types
- Physical Read/Write
- Perfmon counters
- Plan cache
- Again shows currently running queries
Currently running queries
It shows which queries are running in SQL Server right now and keep it busy. Sometimes, you might see same queries are running again and again. So, you can ask the team to investigate it and most of the time issues will be resolved here.
Headlines/high priority result sets
It shows the same priority list displayed in helpdesk/no parameter mode. First 49 priority list is crucial and you must try to resolve them. There is also a url associated with it. Browse it to learn and resolution of the issue.
Top wait types
It shows the top wait types of your SQL Server which means SQL Server is waiting for which resources. It shows Total Thread Time which means the total amount of time queries are running or waiting. In this case, the value is 5949 seconds meaning, lots of queries are running simultaneously in my 4 core processors.
Diagram-3: Top wait types
Common wait types and reasons
| Wait Type |
Reason |
Resolution |
| CXPACKET/CXCONSUMER/LATCH_EX |
Queries are going parallel to read a lot of data or do a lot of CPU work. |
Sort by CPU and by READS. |
| LCK% |
Locking so look for long-running queries. |
Sort by DURATION, and look for the warning of "Long Running, Low CPU." |
| PAGEIOLATCH |
Reading data pages that aren't cached in RAM. |
Sort by READS. |
| RESOURCE_SEMAPHORE |
Queries can't get enough workspace memory to start running. |
Sort by MEMORY GRANT, although that isn't available in older versions of SQL. |
| SOS_SCHEDULER_YIELD |
CPU pressure |
Sort by CPU |
| WRITELOG |
Writing to the transaction log for delete/update/insert (DUI) work |
Sort by WRITES |
Physical Read/Write
This section shows physical read/write of a SQL Server in MB. If your main wait types are read/write then you need to check this. Otherwise, you can ignore it.
Diagram-4: Physical read/write
Perfmon counters
It shows perfmon counters and their current values. You can ignore this.
Plan cache
You will use sp_BlitzCache instead this plan cache output.
Currently running queries
This is the currently running queries when sp_BlitzFirst completes taking its sample.
Since SQL Server Started Mode
If a SQL Server is new to you and you want to find out what performance issues happened, let say, yesterday, in this scenario, you can run below query and know the top wait types of your SQL Server. In diagram-5, Hours Sample means the SQL Server up time and Thread Time refers to the cumulative amount of time queries are running or waiting.
exec sp_BlitzFirst @SinceStartup=1;
Diagram-5: Output of sp_BlitzFirst in Since SQL Server Start mode
Top Wait types
To identify topmost wait types, you need to put an additional parameter.
exec sp_BlitzFirst @SinceStartup=1, @OutputType='TOP10';
Diagram-6: Top 10 wait types of SQL Server Since Started
Logging sp_BlitzFirst to Table
You can schedule an agent job and write sp_BlitzFirst output to tables. The interval should be 15 minutes with these parameters:
- @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, so you can skip any tables. The data will be auto truncated every 7 days. Remember that BlitzCache results will get large as each execution plan is megabytes in size.
Final Word
That is all about sp_BlitzFirst procedure. In next episode we will explore another tool of First Responder Kits.