Claude Code and MSSQL MCP Server – Part 2

(DBA Assistant)

In this new series of blog posts, we are going to look into hooking Claude Code into MSSQL MCP Server. I am going to leave most of the conclusions up to the audience; however, I will do a summary at the end. A small forewarning, this is a screenshot-heavy post, and direct screenshots of Claude Code do not ‘read’ nicely. This series is inspired by Oli Flindall’s last few posts on Claude. In addition, due to the ever-increasing advancement of the models, this post will likely age poorly.

In the previous post, we looked at asking Claude via the MSSQL MCP server to give us some basic info around the following:

• List of databases
• Size of a database
• Resource-consuming queries
• Anti-patterns by poking through some plan XML
• Missing indexes
• etc

We also introduced the following

• Context7
• Sub-Agents
  • Collection of sub-agents

In this part, we are going to see what Claude Code makes of a running workload on our test box. As you will discover, Claude Code is not designed for this use case, but I thought it would be fun to see what it makes of things. All prompts will encourage the use of context7 and sub-agents.

What is running right now?

I set off a workload using HammerDB to generate some queries and asked Claude to take a look.

I think it misunderstood my question:

It just gave us a list of tables in the Stackoverflow2010 database:

Not entirely helpful. Let’s try again:

This time, the output was more helpful:

Reviewing sp_whoisactive shows us this:

Both the above prompts took a good 60 seconds to run. In a busy production environment, the activity you wanted to track may have gone by the time Claude comes back with a result.

Checking out wait stats

I asked Claude to analyse wait stats over the next five minutes. I had to cheat and ensure the workload I was running ran for at least ten minutes to account for Claude’s thinking time!

Claude tried to run WAITFOR but was not allowed:

It ran a different query and now waited for the five minutes…

It didn’t bother with the sub-agents at this stage.

Here is the result:

Looking at Grafana for the wait categories, I get the following:

CPU is busy but not maxed out:

Batch requests at 7k ish:

There are several issues with the above work; the first is that I did not set off sp_blitzfirst to give us waits over the next five minutes as a comparison. The second issue is that I didn’t give Claude a list of potentially benign wait stats to ignore. Let’s rectify this.

Doing a proper job

This time, I asked Claude to reference a text file with a list of possibly benign waits courtesy of Paul Randal’s script. In addition, I also kicked off the following command in SSMS when Claude took its first cut of data:

sp_blitzfirst @seconds = 300, @expertmode=1

Cool.

The second cut of waits was taken, and the ‘data-scientist’ sub-agent started getting involved:

Here are the results:

And:

Looking at sp_blitzfirst:

Claude roughly plots the first 5 wait stats in line with sp_blitzfirst. The numbers look slightly different to sp_blitzfirst, but do look proportionate to each other, as are the number of tasks.

We can see Claude has had a reasonable attempt, but the counts of waiting tasks and wait times seem a bit high compared to both sp_blitzfirst and Grafana:

Claude has mentioned the disk as a bottleneck; this is correct in that it is being driven to its limit, but the disk is not under-performing or in need of further analysis (it is a Samsung 990 Pro, commodity class but still fast).

Here is the data disk:

Here is the transaction log disk:

These latency numbers look fine to me, being <1ms.

As an aside, I noticed Claude did not reference Context7, so I asked it to repeat the exercise where it did use it (I also trimmed MAXDOP to 2, as this is a 4-core VM, to see a difference in the top ten and if it would pick up on it).

Interestingly, it wants me to increase MAXDOP to 4 (after reducing it to 2). CTFP was still at default 5 on this test box.

I had seen enough. Moving on to the final part…

Can Claude help in a (contrived) performance crisis

I mused about throwing in some locking, but it would be too easy. Therefore, I decided to be disgusting (catastrophically so) and run Prime95 on the SQL Server to obliterate the CPU, impacting performance. As Prime95 is not a database-connected application, Claude will be unaware of it.

Maxxed.

I came up with a sob story to see if Claude can help me out.

Claude thinking:

It can see some concerning patterns already…

Claude is about to highlight an unfolding disaster zone…

Looks like we have CPU SCHEDULER COLLAPSE!

Claude also output some diagnostic queries it used (or wants me to use?):

-- EMERGENCY SQL SERVER PERFORMANCE DIAGNOSTICS
-- For SKSQS25DV\EH_DV Performance Degradation Analysis
-- Execute in order of priority during crisis

-- ============================================================================
-- PRIORITY 1: IMMEDIATE CPU AND SYSTEM RESOURCE ANALYSIS
-- ============================================================================

-- 1A. Current CPU Utilization and Signal Waits
SELECT 
    'CPU_UTILIZATION' AS metric_type,
    signal_wait_time_ms,
    wait_time_ms,
    waiting_tasks_count,
    CAST(100.0 * signal_wait_time_ms / wait_time_ms AS DECIMAL(5,2)) AS signal_wait_percentage
FROM sys.dm_os_wait_stats 
WHERE wait_type IN ('DISPATCHER_QUEUE_SEMAPHORE', 'SOS_SCHEDULER_YIELD')
    AND wait_time_ms > 0
ORDER BY wait_time_ms DESC;

-- 1B. Current Scheduler Status - CRITICAL for DISPATCHER_QUEUE_SEMAPHORE analysis
SELECT 
    scheduler_id,
    cpu_id,
    status,
    is_online,
    is_idle,
    preemptive_switches_count,
    context_switches_count,
    current_tasks_count,
    runnable_tasks_count,
    current_workers_count,
    active_workers_count,
    work_queue_count,
    pending_disk_io_count,
    load_factor,
    yield_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255  -- Only CPU schedulers
ORDER BY load_factor DESC, runnable_tasks_count DESC;

-- 1C. Thread Pool Exhaustion Analysis - for SLEEP_TASK diagnosis
SELECT 
    'WORKER_THREAD_STATUS' AS analysis,
    SUM(current_workers_count) AS total_current_workers,
    SUM(active_workers_count) AS total_active_workers,
    SUM(runnable_tasks_count) AS total_runnable_tasks,
    SUM(work_queue_count) AS total_work_queued,
    SUM(pending_disk_io_count) AS total_pending_io
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255;

-- ============================================================================
-- PRIORITY 2: SERVICE BROKER ANALYSIS (BROKER_EVENTHANDLER degradation)
-- ============================================================================

-- 2A. Service Broker Queue Status
SELECT 
    'SERVICE_BROKER_QUEUES' AS metric_type,
    name,
    is_activation_enabled,
    activation_procedure,
    is_enqueue_enabled,
    is_dequeue_enabled,
    OBJECT_NAME(object_id) AS queue_name
FROM sys.service_queues
WHERE is_ms_shipped = 0;

-- 2B. Service Broker Transmission Queue - Often source of BROKER_EVENTHANDLER waits
SELECT 
    'TRANSMISSION_QUEUE_STATUS' AS metric_type,
    COUNT(*) AS messages_in_transmission_queue,
    MIN(enqueue_time) AS oldest_message,
    MAX(enqueue_time) AS newest_message
FROM sys.transmission_queue;

-- 2C. Active Service Broker Conversations
SELECT 
    'ACTIVE_CONVERSATIONS' AS metric_type,
    state_desc,
    COUNT(*) AS conversation_count
FROM sys.conversation_endpoints
WHERE state NOT IN ('CD', 'DI')  -- Not CLOSED or DISCONNECTED_INBOUND
GROUP BY state_desc;

-- ============================================================================
-- PRIORITY 3: I/O PRESSURE ANALYSIS (PAGEIOLATCH waits context)
-- ============================================================================

-- 3A. Current I/O File Stats
SELECT 
    'IO_FILE_STATS' AS metric_type,
    DB_NAME(database_id) AS database_name,
    file_id,
    io_stall_read_ms,
    io_stall_write_ms,
    io_stall,
    num_of_reads,
    num_of_writes,
    CASE WHEN num_of_reads = 0 THEN 0 
         ELSE io_stall_read_ms / num_of_reads END AS avg_read_latency_ms,
    CASE WHEN num_of_writes = 0 THEN 0 
         ELSE io_stall_write_ms / num_of_writes END AS avg_write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
WHERE io_stall > 0
ORDER BY io_stall DESC;

-- 3B. Memory Pressure Indicators
SELECT 
    'MEMORY_PRESSURE' AS metric_type,
    counter_name,
    cntr_value,
    cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Memory Manager'
    AND counter_name IN ('Memory Grants Pending', 'Memory Grants Outstanding', 
                         'Target Server Memory (KB)', 'Total Server Memory (KB)',
                         'Page life expectancy');

-- ============================================================================
-- PRIORITY 4: CURRENT BLOCKING AND ACTIVE SESSIONS
-- ============================================================================

-- 4A. Complete Active Session Analysis
SELECT 
    'ACTIVE_SESSIONS' AS metric_type,
    s.session_id,
    s.status,
    s.cpu_time,
    s.memory_usage,
    s.total_scheduled_time,
    s.total_elapsed_time,
    s.reads,
    s.writes,
    s.logical_reads,
    CASE WHEN r.request_id IS NOT NULL THEN r.wait_type ELSE NULL END AS current_wait_type,
    CASE WHEN r.request_id IS NOT NULL THEN r.wait_time ELSE NULL END AS current_wait_time_ms,
    CASE WHEN r.request_id IS NOT NULL THEN r.blocking_session_id ELSE NULL END AS blocking_session_id,
    CASE WHEN r.request_id IS NOT NULL THEN r.command ELSE NULL END AS current_command,
    CASE WHEN r.request_id IS NOT NULL THEN r.percent_complete ELSE NULL END AS percent_complete,
    s.program_name,
    s.host_name,
    s.login_name
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.session_id > 50  -- User sessions only
    AND s.status IN ('running', 'runnable', 'suspended')
ORDER BY s.cpu_time DESC, s.logical_reads DESC;

-- ============================================================================
-- PRIORITY 5: EMERGENCY MONITORING - Run every 30 seconds during crisis
-- ============================================================================

-- 5A. Real-time Wait Stats Delta (run repeatedly to see trends)
SELECT 
    GETDATE() AS sample_time,
    'CRITICAL_WAITS_CURRENT' AS metric_type,
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    CAST(100.0 * signal_wait_time_ms / NULLIF(wait_time_ms, 0) AS DECIMAL(5,2)) AS signal_wait_pct
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
    'DISPATCHER_QUEUE_SEMAPHORE',
    'BROKER_EVENTHANDLER', 
    'SLEEP_TASK',
    'PAGEIOLATCH_SH',
    'PAGEIOLATCH_EX',
    'CXCONSUMER',
    'LCK_M_X',
    'WRITELOG'
)
ORDER BY wait_time_ms DESC;

In addition, Claude also provided some ‘remediation’ scripts:

-- EMERGENCY REMEDIATION PLAN FOR SKSQS25DV\EH_DV
-- Performance Crisis Response - Execute in Priority Order
-- Current Status: System-level resource exhaustion with thread pool starvation

-- ============================================================================
-- IMMEDIATE ACTIONS (Execute within 5 minutes)
-- ============================================================================

-- STEP 1: CHECK CURRENT SYSTEM STATE
-- Before making changes, capture current state
PRINT 'EMERGENCY REMEDIATION STARTING AT: ' + CONVERT(VARCHAR(25), GETDATE(), 121);

-- Check if we can even execute queries (if this fails, OS-level intervention needed)
SELECT 
    @@SERVERNAME AS server_name,
    @@VERSION AS sql_version,
    GETDATE() AS current_time,
    (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE session_id > 50) AS active_user_sessions;

-- STEP 2: KILL PROBLEMATIC SESSIONS (if any long-running or blocking found)
-- First, identify sessions to kill (DO NOT EXECUTE KILLS WITHOUT CONFIRMATION)
SELECT 
    'SESSIONS_TO_REVIEW_FOR_TERMINATION' AS action_type,
    s.session_id,
    s.status,
    s.cpu_time,
    s.total_elapsed_time,
    s.reads,
    s.writes,
    s.logical_reads,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.command,
    s.program_name,
    s.host_name,
    s.login_name,
    'KILL ' + CAST(s.session_id AS VARCHAR(10)) AS kill_command
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.session_id > 50
    AND (
        s.total_elapsed_time > 1800000 OR  -- 30 minutes
        s.cpu_time > 300000 OR             -- 5 minutes CPU
        s.logical_reads > 100000000 OR     -- 100M logical reads
        (r.wait_type IS NOT NULL AND r.wait_time > 300000) -- 5 minute waits
    )
ORDER BY s.cpu_time DESC;

-- STEP 3: REDUCE SERVICE BROKER LOAD (Address BROKER_EVENTHANDLER crisis)
-- Disable Service Broker activation if not critical
-- WARNING: Only execute if Service Broker is not business-critical
/*
ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
-- Or to completely disable:
-- ALTER DATABASE [YourDatabaseName] SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE;
*/

-- STEP 4: CHECK AND ADJUST MAX DEGREE OF PARALLELISM
-- Reduce MAXDOP to limit CXCONSUMER waits during crisis
DECLARE @current_maxdop INT;
SELECT @current_maxdop = CAST(value AS INT) FROM sys.configurations WHERE name = 'max degree of parallelism';
PRINT 'Current MAXDOP: ' + CAST(@current_maxdop AS VARCHAR(10));

-- If MAXDOP > 4, consider temporary reduction during crisis
IF @current_maxdop > 4
BEGIN
    PRINT 'Consider reducing MAXDOP temporarily:';
    PRINT 'EXEC sp_configure ''max degree of parallelism'', 2;';
    PRINT 'RECONFIGURE WITH OVERRIDE;';
END

-- STEP 5: CHECK MAX WORKER THREADS
DECLARE @current_workers INT;
SELECT @current_workers = CAST(value AS INT) FROM sys.configurations WHERE name = 'max worker threads';
PRINT 'Current Max Worker Threads: ' + CAST(@current_workers AS VARCHAR(10));

-- Auto-configured (0) is usually best, but check if manually set too low
IF @current_workers > 0 AND @current_workers < 512
BEGIN
    PRINT 'Consider setting max worker threads to 0 (auto):';
    PRINT 'EXEC sp_configure ''max worker threads'', 0;';
    PRINT 'RECONFIGURE WITH OVERRIDE;';
END

-- ============================================================================
-- MONITORING DURING CRISIS (Run every 2-3 minutes)
-- ============================================================================

-- Create a monitoring procedure for crisis tracking
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_emergency_monitor' AND type = 'P')
    DROP PROCEDURE sp_emergency_monitor;
GO

CREATE PROCEDURE sp_emergency_monitor
AS
BEGIN
    DECLARE @sample_time DATETIME = GETDATE();
    
    PRINT 'EMERGENCY MONITORING SAMPLE: ' + CONVERT(VARCHAR(25), @sample_time, 121);
    PRINT '================================================================';
    
    -- Critical wait stats
    SELECT 
        'CRITICAL_WAITS' AS metric_type,
        wait_type,
        waiting_tasks_count,
        wait_time_ms,
        max_wait_time_ms,
        CAST(wait_time_ms / 1000.0 / 60.0 AS DECIMAL(10,2)) AS wait_time_minutes
    FROM sys.dm_os_wait_stats
    WHERE wait_type IN ('DISPATCHER_QUEUE_SEMAPHORE', 'BROKER_EVENTHANDLER', 'SLEEP_TASK')
        AND wait_time_ms > 0
    ORDER BY wait_time_ms DESC;
    
    -- Worker thread status
    SELECT 
        'WORKER_THREADS' AS metric_type,
        SUM(current_workers_count) AS total_workers,
        SUM(active_workers_count) AS active_workers,
        SUM(runnable_tasks_count) AS runnable_tasks,
        SUM(work_queue_count) AS work_queued,
        AVG(load_factor) AS avg_load_factor
    FROM sys.dm_os_schedulers
    WHERE scheduler_id < 255;
    
    -- Active problematic sessions
    SELECT 
        'PROBLEM_SESSIONS' AS metric_type,
        COUNT(*) AS session_count
    FROM sys.dm_exec_sessions s
    WHERE s.session_id > 50
        AND s.status IN ('running', 'runnable', 'suspended')
        AND (s.cpu_time > 60000 OR s.logical_reads > 1000000);
    
    PRINT '================================================================';
END
GO

-- ============================================================================
-- ESCALATION ACTIONS (If immediate actions don't help within 15 minutes)
-- ============================================================================

-- LEVEL 2: More Aggressive Measures
PRINT 'LEVEL 2 ESCALATION ACTIONS (Use if Level 1 insufficient):';
PRINT '1. Consider SQL Server service restart (requires downtime)';
PRINT '2. Check Windows Task Manager for non-SQL processes consuming CPU';
PRINT '3. Review Windows Event Logs for hardware/OS errors';
PRINT '4. Check disk space on all drives (tempdb, data, logs)';
PRINT '5. Verify no Windows Updates or antivirus scans running';

-- LEVEL 3: System-Level Investigation
PRINT 'LEVEL 3 SYSTEM INVESTIGATION:';
PRINT '1. Check NUMA node configuration: SELECT * FROM sys.dm_os_nodes;';
PRINT '2. Verify CPU affinity settings: SELECT * FROM sys.dm_os_schedulers;';
PRINT '3. Check for CPU throttling or thermal issues in hardware monitoring';
PRINT '4. Review SQL Server error log for critical errors';
PRINT '5. Check perfmon counters: % Processor Time, Context Switches/sec';

-- ============================================================================
-- RECOVERY VERIFICATION (After remediation)
-- ============================================================================

-- Create recovery verification script
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'sp_verify_recovery' AND type = 'P')
    DROP PROCEDURE sp_verify_recovery;
GO

CREATE PROCEDURE sp_verify_recovery
AS
BEGIN
    PRINT 'RECOVERY VERIFICATION: ' + CONVERT(VARCHAR(25), GETDATE(), 121);
    PRINT '================================================================';
    
    -- Check critical wait improvements
    SELECT 
        'RECOVERY_CHECK' AS status,
        wait_type,
        waiting_tasks_count,
        wait_time_ms,
        max_wait_time_ms,
        CASE 
            WHEN wait_type = 'DISPATCHER_QUEUE_SEMAPHORE' AND wait_time_ms < 3000000000 THEN 'IMPROVED'
            WHEN wait_type = 'BROKER_EVENTHANDLER' AND wait_time_ms < 800000000 THEN 'IMPROVED'
            WHEN wait_type = 'SLEEP_TASK' AND max_wait_time_ms < 300000 THEN 'IMPROVED'
            ELSE 'STILL_CRITICAL'
        END AS recovery_status
    FROM sys.dm_os_wait_stats
    WHERE wait_type IN ('DISPATCHER_QUEUE_SEMAPHORE', 'BROKER_EVENTHANDLER', 'SLEEP_TASK')
    ORDER BY wait_time_ms DESC;
    
    -- Check scheduler health
    SELECT 
        'SCHEDULER_HEALTH' AS status,
        COUNT(*) AS total_schedulers,
        AVG(load_factor) AS avg_load_factor,
        MAX(runnable_tasks_count) AS max_runnable_tasks,
        SUM(work_queue_count) AS total_work_queued
    FROM sys.dm_os_schedulers
    WHERE scheduler_id < 255;
    
    PRINT '================================================================';
    PRINT 'Recovery criteria:';
    PRINT '- DISPATCHER_QUEUE_SEMAPHORE < 3B ms (currently was 4.8B)';
    PRINT '- BROKER_EVENTHANDLER < 800M ms (currently was 1.6B)';
    PRINT '- SLEEP_TASK max_wait < 300000ms (5 min) (currently was 1.17B)';
    PRINT '- Average load_factor < 50 across schedulers';
    PRINT '================================================================';
END
GO

-- ============================================================================
-- PREVENTION MEASURES (Implement after crisis resolved)
-- ============================================================================

PRINT 'POST-CRISIS PREVENTION CHECKLIST:';
PRINT '1. Implement proactive monitoring for DISPATCHER_QUEUE_SEMAPHORE';
PRINT '2. Set up alerts for Service Broker transmission queue growth';
PRINT '3. Configure Resource Governor if appropriate for workload isolation';
PRINT '4. Review and optimize top resource-consuming queries';
PRINT '5. Implement connection pooling if not already configured';
PRINT '6. Schedule regular maintenance windows for CHECKDB, index maintenance';
PRINT '7. Document crisis response procedures and contact escalation';

-- Sample alert thresholds for ongoing monitoring
PRINT 'RECOMMENDED ALERT THRESHOLDS:';
PRINT '- DISPATCHER_QUEUE_SEMAPHORE wait_time_ms > 1,000,000,000 (1B ms)';
PRINT '- BROKER_EVENTHANDLER wait_time_ms > 500,000,000 (500M ms)';
PRINT '- SLEEP_TASK max_wait_time_ms > 300,000 (5 minutes)';
PRINT '- Worker thread utilization > 80%';
PRINT '- Service Broker transmission queue > 1000 messages';

Amongst all the madness, I think Claude gave some usable insight, as checking Task Manager would be useful (it would). I will let you read the output and draw your conclusions as to the rest…

To finish up this post, I decided to chastise Claude on its insane and worrying rhetoric (the server was a little slower but not dead in the water).

Claude wrote out reams of diag queries and frameworks for handling incidents. Here is a small excerpt:

Summary

• Just use the tried and tested tools for troubleshooting running queries or performance issues
  • sp_whoisactive
  • First Responder kit
  • Erik Darling stored procedures
  • Monitoring tools
  • etc
  • etc
• You can interrogate DMVs using Claude, but be wary of these:
  • There is a variable thinking time, which is not ideal
  • It comes up with different answers and insights for every prompt
    • Things like this need to be consistent
  • It burns through your tokens whilst doing its thing
• It was still a fun exercise

Resources

https://www.brentozar.com/first-aid

How I Accidentally Built a Better Database Admin Than Myself