Working Together

Before Our Consulting Call

Welcome aboard, and thanks for choosing to work with me!

I’m just as anxious to get started figuring out your SQL Server problems as you are.

To make sure we can get the call started without a hitch, please read through the sections below. Ideally, you’ll set up monitoring and install the diagnostic scripts, but even doing one or the other puts us in a great position.

If it’s been a while since we last spoke, please make sure you check the scripts below for more recent versions than what we installed last time.

The Meetings:

  • I use Zoom for meeting with clients. If you don’t already use it, make sure it’s downloaded and set up to run using your preferred devices.
  • It has to be the downloaded/installed version, not the web client, because I can’t take remote control via the web client.
  • We’re going to need elevated access to SQL Server. Using the sa account is easiest, but make sure you have appropriate access to avoid false starts.
  • While you’re doing that, make sure you have the latest version of SSMS installed. Old are buggy, missing information and unable to open certain things.

Set Up Monitoring:

The single best thing you can do before our call is get my free, open-source SQL Server Performance Monitor running on the servers we’ll be looking at. It collects exactly the data I look at during engagements: wait stats, query performance, blocking, deadlocks, memory grants, CPU utilization, and more.

The longer it runs before our call, the more useful the data. Even a day or two gives us historical trends instead of just point-in-time snapshots.

There are two editions to choose from (don’t choose both, just one is fine):

  • Full Dashboard (recommended): Installs a small PerformanceMonitor database on each monitored server. 30 T-SQL collectors run via SQL Agent jobs, giving you 24/7 data collection with real-time alerts for blocking, deadlocks, and CPU spikes. Best for production servers where you have SA or sysadmin access.
  • Lite Edition: A standalone executable with no server-side installation at all. 20 in-app collectors store data locally in an embedded DuckDB database. Great for quick triage, restricted environments, or Azure SQL Database (which the Full Dashboard doesn’t support).

You can download either edition from GitHub. If you can’t get monitoring set up, no worries! We can still work together effectively using just the scripts below.

Tools I use:

  • To do my analysis, these are the scripts I use most frequently, along with an explanation
    • First Responder Kit: Useful health check scripts. This will install 5-6 stored procedures at once.
    • sp_WhoIsActive: Great for assessing currently executing queries.
  • These are scripts from my repository, which you can install all from one place here
    • sp_HealthParser: Breaks down the system health extended event to get relevant performance data
    • sp_HumanEventsBlockViewer: For parsing the blocked process report data out
    • sp_LogHunter: Digs through the error log for any sticky situations that have arisen recently.
    • sp_PressureDetector: For quickly diagnosing CPU and memory issues.
    • sp_QuickieStore: Searching and analyzing Query Store data. If you don’t have Query Store enabled for your important databases, go here.
    • sp_IndexCleanup: Clean up unused, duplicative indexes, generate page compression scripts, etc.
    • sp_PerfCheck: Check high value performance metrics for your server

Even if you’ve installed these scripts before, they get updated pretty frequently. It’s worth making sure they are fully up to date before our meeting.

Ahead Of The Game:

To make sure we have as much detail as possible, it’s a good idea to get Extended Events set up to capture blocking and deadlocking.

If you’ve set up the Performance Monitor above, it already collects this data, but these Extended Event sessions give us the raw detail that’s useful for deep-dive analysis.

Note that if you’re using Managed Instance or Azure SQL DB, the Extended Event stuff below won’t work. We’ll have to set up something custom to your server. Microsoft did not make using Extended Events very easy there.

But first, get Query Store set up to capture long term query performance data. The monitoring tools will pull from here too.

Query Store:

This will enable Query Store for a single database, with a maximum size of 1GB, using a low-overhead collection mode.

Just replace [YourDatabaseName] with the one you care about.

ALTER DATABASE 
    [YourDatabase]
SET QUERY_STORE 
(
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 1024,
    QUERY_CAPTURE_MODE = AUTO
);

If you have multiple databases you’re concerned about performance in, run the command for each.

If you have hundreds of databases on your server, try to pick the top 3 or so by size/performance complaints.

Blocking:

This will enable the blocked process report, and set up an Extended Event session for us to review.

It will capture any blocking that occurs for more than 5 seconds, which is the lowest duration available.

/*Enable the blocked process report*/
EXEC sys.sp_configure
    N'show advanced options',
    1;
RECONFIGURE;
GO
EXEC sys.sp_configure
    N'blocked process threshold',
    5; --Seconds
RECONFIGURE;
GO

/*Create a target for it in Extended Events*/
CREATE EVENT SESSION 
    blocked_process_report
ON SERVER
    ADD EVENT 
        sqlserver.blocked_process_report
    ADD TARGET 
        package0.event_file
    (
        SET filename = N'bpr'
    )
WITH
(
    MAX_MEMORY = 4096KB,    
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO

/*Start the session up*/
ALTER EVENT SESSION
    blocked_process_report
ON SERVER 
    STATE = START;
GO

Deadlocking:

This will set up an Extended Event session to monitor specifically for deadlocks.

CREATE EVENT SESSION 
    deadlock
ON SERVER
    ADD EVENT 
        sqlserver.xml_deadlock_report
    ADD TARGET 
        package0.event_file
    (
        SET filename = N'deadlock'
    )
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);
GO

ALTER EVENT SESSION
    deadlock
ON SERVER 
    STATE = START;
GO

Running The Scripts

This is how I start off running each script. I’ll save off any work we do together, but this is a handy reference for some people.

/*Overall health check*/
EXECUTE dbo.sp_Blitz 
    @CheckServerInfo = 1;

/*Performance-focused checks*/ 
EXECUTE dbo.sp_PerfCheck;

/*Review wait stats, file stats, CPU and memory, etc.*/
EXECUTE dbo.sp_PressureDetector;

/*Parse system_health extended event XML*/
EXECUTE dbo.sp_HealthParser
    @skip_locks = 1,
    @warnings_only = 1;

/*Go through your error log for high value messages*/
EXECUTE dbo.sp_LogHunter;

/*Review Query Store data, if available*/
EXECUTE dbo.sp_QuickieStore
    @database_name = 'YourDatabase';

/*Index analysis*/
EXECUTE dbo.sp_BlitzIndex
    @Mode = 4,
    @DatabaseName = 'YourDatabase';

/*Script out changes to clean up duplicates, unused, etc*/ 
EXECUTE dbo.sp_IndexCleanup 
    @database_name = 'YourDatabase';

/*If you set up the blocked process report XE above, this will analyze it*/
EXECUTE dbo.sp_HumanEventsBlockViewer
    @session_name = 'blocked_process_report';

/*Parse either system_health or dedicated XML Deadlock Report XE*/
EXECUTE dbo.sp_BlitzLock
    @EventSessionName = 'deadlock';

/*What's running now, general performance*/
EXECUTE dbo.sp_WhoIsActive
    @get_transaction_info = 1,
    @get_outer_command = 1,
    @get_plans = 1,
    @get_task_info = 2,
    @get_additional_info = 1,
    @get_memory_info = 1;

/*What's running now, focus on blocking*/
EXECUTE dbo.sp_WhoIsActive
    @get_transaction_info = 1,
    @get_outer_command = 1,
    @get_plans = 1,
    @get_task_info = 2,
    @get_additional_info = 1,
    @get_memory_info = 1,
    @find_block_leaders = 1,
    @sort_order = '[blocked_session_count] DESC';

There may be other things we use along the way, but this is the core set of tools I use for everything.

If you set up the Performance Monitor, we’ll start the call by reviewing what it’s collected. That often surfaces the biggest issues right away. From there, we’ll dig in with these scripts for live analysis.

If you’re running scripts only, we’ll work through them together to build a picture of what’s going on.

Either way, come with your questions and pain points. The more specific you can be about what’s bothering you, the more targeted our time together will be.

Talk to you soon!