SQL Server First Responder Kit for Performance Troubleshoot

Brent Ozark Unlimited

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit#how-to-install-the-scripts

EXEC sp_BlitzCache @SortOrder = 'totals reads' ------------------ use master
EXEC sp_BlitzCache @SortOrder = 'cpu' ------------------ use master
EXEC sp_BlitzCache @SortOrder = 'executions' ------------------ use master
Exec sp_Blitz ------------------ use master
EXEC sp_BlitzFirst @seconds=60 ------------------ use master
EXEC sp_BlitzCache @SortOrder = 'avg reads' --, @ExportToExcel = 1 ------------------ use master
EXEC sp_BlitzIndex  ------------------ use master
EXEC sp_BlitzWho ------------------ use master
EXEC sp_BlitzIndex @BringThePain= 1 ----- Use after hours – Use in the DB 

-- While the database is under load:
EXEC sp_BlitzFirst @Seconds=60 --this takes a 60 second statistic sample, this will not return in 60 seconds due to high cpu utilization. Let it run.

--When the database load has dulled down(it’s safe to run at 100% cpu but may take forever to complete.):
EXEC sp_BlitzCache @SortOrder = 'cpu', @ExportToExcel = 1
EXEC sp_BlitzCache @SortOrder='executions', @ExportToExcel =1
EXEC sp_Blitz
EXEC sp_BlitzCache @SortOrder = 'cpu' --Provide the query plan for the top 1 CPU consuming procedure (This is not excel exportable due to the xml columns).
EXEC sp_BlitzCache @SortOrder = 'executions' --Provide the query plan for the top 1 executed procedure (This is not excel exportable due to the xml columns).

--When the database is not under load
EXEC sp_BlitzIndex @BringThePain = 1

Focus on the top offenders from EXEC sp_BlitzCache @SortOrder = ‘cpu’ as the starting point.

Review Steps

1. Install the first responder toolkit.  Use the Install-All-Scripts.sql installation script in master. https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit
2. Exec sp_Blitz
3. Exec sp_BlitzCache
4. During peak hours run Exec sp_BlitzFirst.  If the DB is already hitting 100% CPU constantly don’t run this at that time.

Install

https://downloads.brentozar.com/FirstResponderKit.zip – Download and install FirstResponderKit.zip

Run Install-All-Scripts.sql