MSSQL_XP Wait Type Explained

MSSQL_XP waits manifest when the SQL Server Database Engine scheduler suspends a worker thread during extended stored procedure execution. Extended procedures run in the SQL Server process space but execute external code through DLL calls, creating a bridge between SQL Server's managed environment and unmanaged system resources.

When an extended procedure executes, the calling thread transitions from the SQL Server scheduler to external code execution. The scheduler registers this as an MSSQL_XP wait while the thread blocks on external operations like file system access, registry reads, command execution, or network calls. The wait persists until the external operation completes and control returns to SQL Server.

Extended procedures differ fundamentally from CLR procedures or regular stored procedures because they execute unmanaged code that SQL Server cannot directly monitor or control. This creates potential for indefinite blocking if the external operation hangs, crashes, or encounters resource contention outside SQL Server's visibility.

SQL Server 2019 and later versions show more granular wait statistics for extended procedures compared to earlier versions. SQL Server 2022 introduced additional telemetry for tracking extended procedure performance, though the core MSSQL_XP wait behavior remains consistent across versions. The deprecated nature of many extended procedures in modern SQL Server versions means high MSSQL_XP waits often indicate legacy code patterns that should be modernized.

-- Current MSSQL_XP waits and their durations
SELECT 
    session_id,
    wait_type,
    wait_time_ms,
    wait_resource,
    blocking_session_id,
    command,
    program_name,
    login_name
FROM sys.dm_exec_requests 
WHERE wait_type = 'MSSQL_XP'
ORDER BY wait_time_ms DESC;

-- Historical MSSQL_XP wait statistics since last restart
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / waiting_tasks_count AS avg_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'MSSQL_XP'
    AND waiting_tasks_count > 0;

-- Active extended procedure executions with their SQL text
SELECT 
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time_ms,
    t.text AS sql_text,
    r.start_time,
    DATEDIFF(second, r.start_time, GETDATE()) AS duration_seconds
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'MSSQL_XP'
    OR t.text LIKE '%xp_%'
ORDER BY r.wait_time_ms DESC;

-- Extended stored procedure listing
SELECT 
    o.name,
    o.type_desc,
    o.create_date,
    o.modify_date
FROM master.sys.objects o
WHERE o.type = 'X'
ORDER BY o.name;

-- Session details for processes executing extended procedures
SELECT 
    s.session_id,
    s.login_name,
    s.program_name,
    s.host_name,
    s.is_user_process,
    r.command,
    r.wait_type,
    r.wait_time_ms,
    r.cpu_time,
    r.logical_reads,
    r.writes
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE r.wait_type = 'MSSQL_XP'
    OR r.command LIKE '%XP%';

Kill Long-Running Extended Procedure Sessions This script identifies and terminates sessions with extended procedure waits exceeding a specified threshold. Extended procedures can hang indefinitely on external resources, requiring manual intervention.

-- Kill sessions with MSSQL_XP waits over 5 minutes
-- TEST IN DEV FIRST - This will terminate active sessions
DECLARE @kill_threshold_ms INT = 300000; -- 5 minutes
DECLARE @session_id INT;
DECLARE @sql NVARCHAR(50);

DECLARE kill_cursor CURSOR FOR
SELECT session_id
FROM sys.dm_exec_requests
WHERE wait_type = 'MSSQL_XP' 
    AND wait_time_ms > @kill_threshold_ms
    AND session_id > 50; -- Exclude system sessions

OPEN kill_cursor;
FETCH NEXT FROM kill_cursor INTO @session_id;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Killing session ' + CAST(@session_id AS VARCHAR(10)) + ' due to excessive MSSQL_XP wait';
    SET @sql = 'KILL ' + CAST(@session_id AS VARCHAR(10));
    EXEC sp_executesql @sql;
    FETCH NEXT FROM kill_cursor INTO @session_id;
END

CLOSE kill_cursor;
DEALLOCATE kill_cursor;

Disable Problematic Extended Procedures Disables commonly problematic extended procedures that are frequent sources of MSSQL_XP waits. Only disable procedures not required by your applications.

-- Disable extended procedures known to cause blocking issues
-- Verify these procedures are not needed before disabling
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Disable xp_cmdshell if not required
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;

-- Disable Ole Automation Procedures if not required  
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;

EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

PRINT 'Extended procedures disabled. Monitor for application errors.';

Set Resource Governor for Extended Procedure Workloads Creates a resource pool with limited parallelism for workloads using extended procedures, preventing them from consuming excessive resources during waits.

-- Create resource pool for extended procedure workloads
-- This limits resource consumption during external operations
IF NOT EXISTS (SELECT * FROM sys.resource_governor_resource_pools WHERE name = 'ExtendedProcPool')
BEGIN
    CREATE RESOURCE POOL ExtendedProcPool
    WITH (
        MIN_CPU_PERCENT = 0,
        MAX_CPU_PERCENT = 25,
        MIN_MEMORY_PERCENT = 0, 
        MAX_MEMORY_PERCENT = 25,
        MAX_DOP = 1
    );
END

-- Create workload group for extended procedure sessions
IF NOT EXISTS (SELECT * FROM sys.resource_governor_workload_groups WHERE name = 'ExtendedProcGroup')
BEGIN
    CREATE WORKLOAD GROUP ExtendedProcGroup
    WITH (
        IMPORTANCE = LOW,
        REQUEST_MAX_MEMORY_GRANT_PERCENT = 10,
        REQUEST_MAX_CPU_TIME_SEC = 300,
        REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 60,
        GROUP_MAX_REQUESTS = 5
    ) USING ExtendedProcPool;
END

-- Apply configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
PRINT 'Resource Governor configured for extended procedures.';

Configure extended procedures restrictively through sp_configure, disabling unused procedures like xp_cmdshell and Ole Automation Procedures. Enable only the minimum required extended procedures and regularly audit their usage through query store and extended events.

Implement query timeouts for applications calling extended procedures, preventing indefinite waits when external operations hang. Set QUERY_GOVERNOR_COST_LIMIT at the session level for processes using extended procedures to automatically terminate expensive operations.

Replace legacy extended procedures with modern alternatives where possible. Use PowerShell jobs instead of xp_cmdshell, SQLCLR procedures instead of custom extended procedures, and T-SQL file operations instead of xp_fileexist. This eliminates the unmanaged code execution that causes MSSQL_XP waits.

Monitor extended procedure usage through sys.dm_exec_procedure_stats and alert on procedures with high execution counts or duration. Create Resource Governor pools with conservative CPU and memory limits for workloads requiring extended procedures, preventing resource exhaustion during blocking operations.

Establish connection pooling limits for applications using extended procedures, as these operations cannot be efficiently multiplexed. Consider moving extended procedure calls to dedicated application servers outside the database engine to eliminate MSSQL_XP waits entirely.