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!

How to Remove Duplicate Records in SQL Server Using T-SQL

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!

SQL Server PIVOT and UNPIVOT Tutorial: Syntax and Practical Examples

Summary: Master the SQL Server PIVOT and UNPIVOT operators. This guide provides the general syntax and real-world examples using AdventureWorks to transform rows into columns and back again.

SQL Server PIVOT and UNPIVOT: Transforming Data

The PIVOT operator in SQL Server is a powerful tool for data analysis. It allows you to rotate a table-valued expression by turning unique values from one column into multiple columns in the output, while performing aggregations on any remaining column values.

---

1. General PIVOT Syntax

Understanding the structure is key to mastering pivot operations. The query requires a source subquery, an aggregate function, and an IN clause for the new headers.

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that retrieves the data>) AS <source alias>  
PIVOT  
(  
    <aggregate function>(<column to aggregate>)  
    FOR [<column with values to become headers>]  
    IN ([first pivoted column], [second pivoted column], ..., [last pivoted column])  
) AS <pivot table alias>  
<optional ORDER BY clause>;
        

2. Basic PIVOT Example

This example calculates the average cost of products, sorted by the number of days it takes to manufacture them.

-- This example creates a pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,  
[0], [1], [2], [3], [4]  
FROM  
(SELECT DaysToManufacture, StandardCost FROM Production.Product) AS SourceTable  
PIVOT  
(  
AVG(StandardCost)  
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])  
) AS PivotTable;
        

3. Advanced PIVOT Query

Using the AdventureWorks database to count purchase orders per employee, pivoted by specific Employee IDs.

USE AdventureWorks2014;  
GO  
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5  
FROM  
(SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) AS p  
PIVOT  
(  
COUNT(PurchaseOrderID)  
FOR EmployeeID IN ([250], [251], [256], [257], [260])  
) AS pvt  
ORDER BY pvt.VendorID;
        

4. The UNPIVOT Operation

The UNPIVOT operator performs the opposite operation, transforming columns back into rows for normalized reporting.

-- Creating the base table and inserting sample values
CREATE TABLE #pvt (
    VendorID INT, 
    Emp1 INT, 
    Emp2 INT, 
    Emp3 INT, 
    Emp4 INT, 
    Emp5 INT
);  

INSERT INTO #pvt VALUES (1,4,3,5,4,4), (2,4,1,5,5,5), (3,4,3,5,4,4);  

-- Applying the UNPIVOT operator
SELECT VendorID, Employee, Orders  
FROM  
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM #pvt) AS p  
UNPIVOT  
(Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5)) AS unpvt;  
GO
        

---

Need Dynamic Columns? Standard PIVOT requires hardcoded values in the IN clause. For variable headers, you'll need to use Dynamic SQL to build the column list at runtime.