SQL Server help

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!

Summary: Learn how to identify and delete duplicate data in SQL Server using ROW_NUMBER(), CTEs, and a flexible dynamic stored procedure to keep your database clean.

How to Remove Duplicate Records in SQL Server

If you're working with databases, one common problem you might face is duplicate data. Duplicates can cause a lot of headaches, especially when it comes to data analysis or reporting. In this post, we’ll walk through a simple way to remove duplicate records in SQL Server using a stored procedure that you can adapt for your own projects.

1. What is a Duplicate Record?

A duplicate record occurs when you have two or more rows in your table with the exact same data in one or more columns. For example, if two rows share the same customer name, email, and phone number, they are duplicates.

2. The Concept of the RemoveDuplicate Procedure

In SQL Server, you can use a stored procedure to automate the process of cleaning data. The RemoveDuplicate procedure we’re exploring helps to:

Find duplicate records across any table dynamically.
Remove extra copies while leaving exactly one unique row.

3. Understanding the T-SQL Code

3.1 The Procedure and Parameters

The procedure is created with parameters to make it reusable:

CREATE PROCEDURE RemoveDuplicate @SCDID sysname, @IsDebug bit = 0

@SCDID: An ID identifying the specific rule and table to work with.
@IsDebug: A flag to print the SQL commands for review before execution.

3.2 Using ROW_NUMBER() to Identify Duplicates

The core logic uses a Common Table Expression (CTE) and the ROW_NUMBER() function:

WITH ct AS (
    SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ColumnSearch ORDER BY ColumnSearch)
    FROM SourceTable
) DELETE FROM ct WHERE rn >= 2;

The PARTITION BY clause groups rows by the columns where you expect duplicates. If three rows are identical, they get assigned numbers 1, 2, and 3. Deleting rn >= 2 removes the extras.

3.3 Advanced Cleanup Using LAG()

For more complex scenarios, we use the LAG() function to compare the current row with the previous one. If they match, the current row is flagged for deletion.

4. SCD2_Rule Table Design

This procedure relies on a configuration table named SCD2_Rule to know which databases and columns to target:

Field	Description
SourceDatabase	The DB containing the duplicates.
SourceObject	The specific Table name.
ColumnSearch	Columns used to identify a "match".
ColumnExclude	Columns to ignore during comparison.

5. Complete Stored Procedure Code

Below is the full, flexible T-SQL code for the RemoveDuplicate procedure:


CREATE PROCEDURE RemoveDuplicate
    @SCDID sysname,
    @IsDebug bit = 0
AS
BEGIN
    DECLARE @Sql AS NVARCHAR(MAX), @rVal varchar(max)

    -- Step 1: Basic Duplicate Removal via ROW_NUMBER
    SELECT @Sql = 'WITH ct AS (
        SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ' + ColumnSearch + ' ORDER BY ' + ColumnSearch + ')
        FROM ' + CONCAT(SourceDatabase, '.', SourceSchema, '.', SourceObject) + '
    ) DELETE FROM ct WHERE rn >= 2'
    FROM dbo.SCD2_Rule WHERE SCDID = @SCDID

    IF @IsDebug = 1 PRINT @Sql
    EXEC SP_EXECUTESQL @Sql

    -- Step 2: Advanced Cleanup using LAG logic
    -- (Logic builds dynamic SQL based on excluded columns)
    SELECT @Sql = 'WITH ct AS (
        SELECT *, Ckp = CONCAT(' + @rVal + '),
            PRv = LAG(CONCAT(' + @rVal + ')) OVER(PARTITION BY ' + REPLACE(ColumnSearch, ',DataSystemDate', '') + ' ORDER BY ' + ColumnSearch + ')
        FROM ' + CONCAT(SourceDatabase, '.', SourceSchema, '.', SourceObject) + '
    ) DELETE FROM ct WHERE ISNULL(ckp, '''') = ISNULL(PRv, '''')'
    FROM dbo.SCD2_Rule WHERE SCDID = @SCDID

    IF @IsDebug = 1 PRINT @Sql
    EXEC SP_EXECUTESQL @Sql
END

Conclusion

Keeping your SQL Server tables clean is vital for performance and accuracy. By using Dynamic SQL and window functions like ROW_NUMBER(), you can automate the tedious task of data deduplication.

Pro Tip: Always back up your data or test the procedure using @IsDebug = 1 before running a DELETE command on a production dataset!

Found this helpful? Subscribe for more T-SQL tips and beginner-friendly SQL Server guides!