How to Monitor SQL Server Memory and Windows Tasks using T-SQL

Summary: Learn how to query the Windows Task Manager and SQL Server Dynamic Management Views (DMVs) to troubleshoot memory pressure and monitor server-side processes using T-SQL.

Monitoring SQL Server: Task Manager & System Memory Query

In this guide, we’ll break down a SQL query designed to retrieve running tasks from the Windows Task Manager and fetch critical system memory statistics from SQL Server. This combined approach allows system administrators to monitor resource-heavy processes and server health effectively without leaving the SSMS environment.

1. Retrieving Task List Data via xp_cmdshell

To extract processes running on your SQL Server, we use the xp_cmdshell extended stored procedure. This executes the Windows tasklist command in CSV format and inserts the raw output into a temporary table for processing.

-- Check and drop temporary table
DROP TABLE IF EXISTS #TM;

-- Create table for raw Task Manager data
CREATE TABLE #TM (id INT IDENTITY, tsk VARCHAR(1000));

-- Execute shell command and capture output
INSERT INTO #TM (tsk) EXEC xp_cmdshell 'tasklist /v /FO csv';
        

How it works:

• xp_cmdshell: Triggers the Windows command line from within SQL.
• Tasklist /v: Generates a detailed list of tasks including user and memory usage.
• The #TM table acts as a landing zone for this external data.

2. Cleaning and Formatting Raw CSV Data

Raw CSV output often contains unnecessary quotes and commas. We use a Common Table Expression (CTE) and the REPLACE function to sanitize the strings, preparing them for JSON parsing.

WITH ct AS (
    SELECT *, 
           REPLACE(REPLACE(REPLACE(tsk,'","','|'),'"',''),',','') AS ntsk
    FROM #TM WHERE id >= 2
)
        

3. Parsing Process Data with JSON_VALUE

Using SQL Server’s JSON_VALUE and CROSS APPLY, we transform the cleaned strings into a structured result set. This allows you to see PID, Memory Usage, and Window Titles clearly.

SELECT A.id,
       [ImageName] = NULLIF(JSON_VALUE(JS,'$[0]'),''),
       [PID] = NULLIF(JSON_VALUE(JS,'$[1]'),''),
       [SessionName] = NULLIF(JSON_VALUE(JS,'$[2]'),''),
       [MemUsage_KB] = CAST(REPLACE(NULLIF(JSON_VALUE(JS,'$[4]'),''), ' k','') AS INT),
       [UserName] = NULLIF(JSON_VALUE(JS,'$[6]'),''),
       [CPUTime] = NULLIF(JSON_VALUE(JS,'$[7]'),''),
       [WindowTitle] = NULLIF(JSON_VALUE(JS,'$[8]'),'')
FROM ct A
CROSS APPLY (VALUES ('["'+REPLACE(STRING_ESCAPE(ntsk,'json') ,'|','","')+'"]') ) B(JS)
ORDER BY [MemUsage_KB] DESC;
        

4. Analyzing System Memory with DMVs

Finally, we query the sys.dm_os_sys_memory Dynamic Management View (DMV). This provides a high-level view of the physical memory available on the host machine.

-- Get SQL Server Physical Memory Stats
SELECT @@SERVERNAME AS [ServerName],
       total_physical_memory_kb / 1024 AS [Total_Physical_Memory_MB],
       available_physical_memory_kb / 1024 AS [Available_Physical_Memory_MB],
       system_memory_state_desc AS [Memory_Health_Status]
FROM sys.dm_os_sys_memory;
        

Conclusion & Troubleshooting

Monitoring memory and active tasks is vital for maintaining SQL Server performance. By integrating Windows task data with T-SQL, you can identify "noisy neighbor" processes stealing resources from your database.

Security Best Practice: Enabling xp_cmdshell carries security risks. Always disable it when not in use or ensure only authorized sysadmins have execution rights.

---

Enjoyed this SQL tutorial? Bookmark this blog for more T-SQL troubleshooting guides and performance tuning tips!