SQL Server Task Manager using TSQL

SQL Server Task Manager & System Memory Query

In this post, we’ll break down a SQL query that retrieves the running tasks from the Windows Task Manager and fetches important system memory statistics from SQL Server. This combined approach helps system administrators monitor running tasks and memory usage on the server effectively. Let's dive right in!

1. Retrieving Task Information from the Task Manager

The first part of the query extracts the list of tasks running on your SQL Server. It uses the xp_cmdshell extended stored procedure to execute the Windows tasklist command, which lists the running processes in CSV format. This data is then inserted into a temporary table.

-- Drop the temporary table if it exists
drop table if exists #TM

-- Create the temporary table for storing task data
Create table #TM (id int identity, tsk varchar(1000))

-- Insert tasklist output into the temporary table
insert into #TM (tsk) exec xp_cmdshell 'tasklist /v /FO csv'
    

Here’s a quick explanation of the steps in this code:

• xp_cmdshell runs the tasklist command with the /v option for detailed information and /FO csv for CSV formatting.
• The task list is inserted into the temporary table #TM</>, where each row contains the task details like process name, PID, memory usage, etc.

2. Cleaning and Formatting the Task Data

Once we have the raw task data, we need to clean it up. A Common Table Expression (CTE) is used here to remove unwanted characters like commas and double quotes from the CSV output, making it easier to extract individual task details later.

;with ct as (
    select *, 
           replace(replace(replace(tsk,'","','|'),'"',''),',','') ntsk
    from #TM where id >= 2
)
    

What’s happening here:

• The replace function is applied multiple times to remove unwanted characters like quotes and commas from the task data.
• The cleaned data is stored in a new column ntsk</>, which will be processed further.

3. Parsing the Data and Extracting Specific Information

Next, we use SQL Server’s JSON_VALUE function to parse the cleaned task data and extract specific fields, such as the task name, PID, memory usage, and others. We also use CROSS APPLY to convert the cleaned data into a JSON array format that’s easier to work with.

Select A.id,
       ImageName = nullif(JSON_VALUE(JS,'$[0]'),''),
       PID = nullif(JSON_VALUE(JS,'$[1]'),''),
       SessionName = nullif(JSON_VALUE(JS,'$[2]'),''),
       SessionNo = nullif(JSON_VALUE(JS,'$[3]'),''),
       MemUsage = cast(replace(nullif(JSON_VALUE(JS,'$[4]'),''), ' k','') as int),
       Status = nullif(JSON_VALUE(JS,'$[5]'),''),
       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 desc
    

Here’s how the data is processed:

• The string_escape function prepares the data for JSON formatting by escaping special characters, and the replace function replaces pipes with commas to create a valid JSON array.
• JSON_VALUE is then used to extract specific fields from the JSON array. For example, $[0] extracts the image name (task name), $[1] gets the PID, and so on.
• We use nullif to handle empty strings and replace them with NULL.
• The result is ordered by memory usage in descending order to highlight the processes that are consuming the most resources.

4. Retrieving System Memory Information

Finally, we fetch system memory details using the sys.dm_os_sys_memory DMV (Dynamic Management View). This provides crucial information about the physical memory available on the server.

-- Retrieve system memory information
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
FROM     sys.dm_os_sys_memory;
    

What this query does:

• total_physical_memory_kb and available_physical_memory_kb give the total and available physical memory in kilobytes. We divide these by 1024 to convert them into megabytes (MB).
• system_memory_state_desc describes the current state of the system’s memory (whether it’s under stress, healthy, etc.).
• @@servername Returns the name of the SQL Server instance where this query is running.

5. Conclusion

This SQL query provides an effective way to monitor running tasks and system memory on your server. It allows you to:

• Retrieve detailed task/process information from the Windows Task Manager.
• Format and clean up raw task data into a readable structure.
• Get important memory statistics to keep your server healthy and optimized.

By using this query, you can quickly identify high-memory processes and track overall system performance in a simple, easy-to-understand format.

Note: The xp_cmdshell extended stored procedure must be enabled for this query to work. Make sure your environment allows the execution of this command.

If you found this tutorial helpful, be sure to share it with your fellow SQL enthusiasts or subscribe for more beginner-friendly guides!