SQL Server Random Masking: Anonymizing Sensitive Numeric Data with DDM

Summary: Learn how to use the random() masking function in SQL Server Dynamic Data Masking. This tutorial explains how to replace sensitive numeric values like salaries and IDs with random data within a custom range.

Implementing Random Masking in SQL Server

The random() function is a powerful component of SQL Server's Dynamic Data Masking (DDM) suite. Unlike string-based masks, Random Masking is designed specifically for numeric data types, allowing you to present "fake" but realistic numeric values to unauthorized users.

---

What is Random Masking?

The random(start, end) function replaces the actual numeric value in a column with a random value chosen from the specified range. This is ideal for masking sensitive financial data, scores, or identifiers while maintaining the numeric integrity of the application.

Function Syntax

MASKED WITH (FUNCTION = 'random([start_range], [end_range])')

---

T-SQL Example: Masking Salaries and Bonus Points

In this example, we apply masks to a Salary column (Money) and an InternalScore column (Int).

-- Create a new table with Random Masking
CREATE TABLE EmployeePayroll (
    EmpID INT IDENTITY(1,1) PRIMARY KEY,
    EmpName VARCHAR(100) NOT NULL,
    -- Masking Salary to show a random value between 30k and 50k
    Salary MONEY MASKED WITH (FUNCTION = 'random(30000, 50000)'),
    -- Masking Score to show a random number between 1 and 10
    PerformanceScore INT MASKED WITH (FUNCTION = 'random(1, 10)')
);

-- Insert real data
INSERT INTO EmployeePayroll (EmpName, Salary, PerformanceScore)
VALUES ('John Doe', 95000, 9), ('Jane Smith', 110000, 10);

-- Query as a masked user
SELECT * FROM EmployeePayroll;
        

Expected Output for Masked Users

Even though John Doe earns 95,000, a user without UNMASK permissions will see something like this:

EmpID	EmpName	Salary	PerformanceScore
1	John Doe	42384.12	4
2	Jane Smith	31205.50	7

---

Important Considerations

• Supported Types: Works on bigint, int, smallint, tinyint, decimal, numeric, float, real, money, and smallmoney.
• Consistency: The random value is generated at query runtime. Multiple executions of the same query may result in different random values being displayed.
• Aggregations: If a masked user runs a SUM() or AVG(), the calculation is performed on the masked values, ensuring total amounts remain hidden.

Pro Tip: Use a range that mimics realistic data. If your actual salaries are 6-figures, masking them with random(1, 10) might break application logic that expects larger values!

Related: SQL Server Security Best Practices

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!

How to Identify Inlineable Scalar UDFs in SQL Server 2019 and later

Summary: Use sys.sql_modules to identify which Scalar User-Defined Functions (UDFs) are eligible for inlining to boost query performance in SQL Server 2019.

Check if Your SQL Scalar Functions are Inlineable

When you're working with SQL Server 2019 (and later), understanding the properties of your functions can be critical for performance optimization. Scalar UDF Inlining is a game-changing feature that automatically transforms scalar functions into relational expressions.

Why Inlining Matters

This query provides insight into whether a scalar UDF can be inlined. If a function is inlineable, SQL Server essentially expands the function's logic directly into the calling query. This avoids the massive performance overhead of row-by-row function calls and context switching.

-- Identify scalar functions and their inlining status
SELECT 
    OBJECT_NAME(m.object_id) AS [Function Name],
    m.is_inlineable,
    m.inline_type,
    efs.total_worker_time,
    efs.execution_count
FROM sys.sql_modules AS m WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_exec_function_stats AS efs WITH (NOLOCK) 
    ON m.object_id = efs.object_id
WHERE efs.type_desc = N'SQL_SCALAR_FUNCTION'
   OR m.object_id IN (SELECT object_id FROM sys.objects WHERE type = 'FN')
OPTION (RECOMPILE);
        

Understanding the Results

• is_inlineable: A value of 1 means the function meets the requirements for inlining.
• inline_type: Indicates whether inlining is currently turned on (1) or off (0) for that specific module.

If your function shows is_inlineable = 0, it might be due to the use of certain non-deterministic functions (like GETDATE()) or specific T-SQL constructs that prevent the optimizer from inlining the code.

---

For more detailed information on requirements and system views, refer to the official Microsoft documentation:

• Scalar UDF Inlining Deep Dive
• sys.sql_modules Reference

Want to speed up your queries? Identifying non-inlineable functions is the first step toward significant performance gains in SQL Server 2019!

How to Recover Unsaved SQL Queries in SSMS (SQL Server Management Studio)

Summary: Lost a SQL script? Learn how to recover unsaved queries in SSMS using AutoRecover files, temporary system folders, and T-SQL script execution history.

How to Recover Unsaved Work in SQL Server Management Studio (SSMS)

Losing unsaved work can be incredibly frustrating, especially after spending hours tuning a complex script in SQL Server Management Studio (SSMS). Fortunately, SSMS has built-in safeguards. Here’s how you can find and recover your lost SQL files.

1. Default Location for Unsaved SQL Files

If SSMS crashes, it often saves temporary copies of your open queries. Depending on your version of Windows, check these directories:

• Windows Vista / 7 / 10 / 11:

%USERPROFILE%\Documents\SQL Server Management Studio\Backup Files
OR
%USERPROFILE%\AppData\Local\Temp

• Windows XP:

C:\Documents and Settings\<YourUsername>\My Documents\SQL Server Management Studio\Backup Files

2. View Recently Executed SQL Queries via T-SQL

If you executed the script before losing it, the code might still be in the SQL Server Plan Cache. Run the following query to retrieve recently executed scripts:

USE <Database_Name>;
GO

SELECT 
    execquery.last_execution_time AS [Date_Time],
    execsql.text AS [Script_Content]
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC;
        

Note: Replace <Database_Name> with the database you were targeting.

3. How to Enable AutoRecover in SSMS

To prevent future data loss, ensure AutoRecover is properly configured. This feature automatically saves your scripts at set intervals.

1. Open SSMS.
2. Go to Tools > Options.
3. Navigate to Environment > AutoRecover.
4. Set "Save AutoRecover information every" to 5 minutes (or your preferred interval).

4. What Happens on Restart?

When SSMS restarts after an unexpected shutdown, it will typically present a "Recover Unsaved Queries" dialog. Always review this list before closing the window to ensure your work is restored.

Conclusion

By keeping AutoRecover enabled and knowing how to query the sys.dm_exec_query_stats DMV, you can significantly reduce the risk of losing critical T-SQL scripts. Always remember to save your work frequently (Ctrl+S) as a best practice!

Found this SQL tip helpful? Share it with your team or subscribe for more SQL Server administration guides!

How to Search SQL Server Error Logs Using T-SQL and Temp Tables

Summary: Learn how to efficiently query the SQL Server Error Log using sp_readerrorlog and temporary tables to filter out noise and identify critical system errors.

Read Specific SQL Server Errors Using a Temp Table

Ever need to quickly check recent errors in your SQL Server logs without scrolling through thousands of lines in the Log File Viewer? This handy T-SQL snippet allows you to query the error log and gather information about various processes and messages. It's a great way to get a snapshot of what's been happening in your environment.

Querying SQL Server Error Logs

The following SQL code creates a temporary table and populates it by reading the last seven SQL Server error logs (from 0 to 6). It then selects all entries where the process is not 'logon', helping you filter out routine login messages and focus on actual errors or system events.

-- Create a temp table to hold log data
CREATE TABLE #t (dt DATETIME, process VARCHAR(200), msg VARCHAR(1000));

-- Populate the table by reading current and archived logs
INSERT INTO #t EXEC sp_readerrorlog 0, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 1, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 2, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 3, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 4, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 5, 1, 'error';
INSERT INTO #t EXEC sp_readerrorlog 6, 1, 'error';

-- Filter results to ignore routine logon messages
SELECT * 
FROM #t 
WHERE process <> 'logon'
ORDER BY dt DESC;

-- Cleanup
DROP TABLE #t;
        

---

Found this T-SQL tip helpful? Share it with your fellow DBAs or subscribe for more SQL Server troubleshooting guides!

How to Analyze SQL Server Execution Plans from Saved XML Files via T-SQL

Summary: Learn how to load saved SQL execution plan (.xml) files into SQL Server, query historical plan handles, and use XQuery to extract performance metrics like CPU and IO costs.

How to Analyze SQL Server Execution Plans from a Saved XML File

If you have an execution plan saved as an XML file (often with a .sqlplan extension), you aren't limited to just viewing it graphically. You can load it into SQL Server and query the XML directly to find specific performance bottlenecks. Here is how to read and analyze it using T-SQL.

1. Load the Execution Plan File

The OPENROWSET function is the most efficient way to pull an external .xml file into a SQL variable for processing.

DECLARE @xml XML;

-- Load file content into XML variable
SELECT @xml = CAST(FileData.BulkColumn AS XML)
FROM OPENROWSET(BULK 'C:\TEMP\MyPlan.XML', SINGLE_CLOB) AS FileData;

-- Verify the content
SELECT @xml AS LoadedPlan;
        

2. Retrieve Historical Queries via DMVs

If you don't have the file but need to find a plan for a recently executed query, use the Dynamic Management Views (DMVs). This snippet searches for a specific text pattern in your plan cache.

SELECT TOP 100 
    execquery.last_execution_time AS [Date_Time],
    execsql.TEXT AS [Script],
    execquery.plan_handle
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
WHERE execsql.TEXT LIKE '%WITH Temp_Accounts (%'
ORDER BY execquery.last_execution_time DESC;
        

3. Deep-Dive Analysis using XQuery

Once your plan is in an XML variable, use XQuery and namespaces to extract specific attributes like PhysicalOp, EstimateCPU, and EstimatedTotalSubtreeCost for every operator in the plan.

-- Define the namespace for SQL Server Showplans
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    c.value('.[1]/@PhysicalOp', 'nvarchar(max)') AS PhysicalOp,
    c.value('.[1]/@LogicalOp', 'nvarchar(max)') AS LogicalOp,
    c.value('.[1]/@TableCardinality', 'nvarchar(max)') AS TableCardinality,
    c.value('.[1]/@EstimatedTotalSubtreeCost', 'float') AS SubtreeCost,
    c.value('.[1]/@EstimateRows', 'float') AS EstimatedRows,
    c.value('.[1]/@EstimateCPU', 'float') AS EstimateCPU,
    c.query('.') AS NodeXml
FROM @xml.nodes('//RelOp') AS T(c)
ORDER BY SubtreeCost DESC;
        

💡 Final Thoughts

By leveraging SQL Server's XQuery capabilities and DMVs, you can gain deep insights into query performance behavior directly from saved files. This method is essential for automated performance auditing or comparing plans across different server environments.

Found this optimization guide helpful? Share it with your SQL community or subscribe for more advanced performance tuning tips!

Monitor Active SQL Server Connections and Sessions using T-SQL

Summary: Quickly audit your SQL Server environment with this T-SQL script that groups active connections by IP address, application name, and login to identify resource usage and connection leaks.

Monitoring Active SQL Server Connections and Sessions

Managing server load requires a clear understanding of who is connected to your database and what applications they are using. Here's a handy SQL query to get an overview of your active SQL Server connections and sessions. It helps you quickly see the source IP, the client hostname, and the total connection count per user. This is an essential tool for monitoring server activity or troubleshooting connection leaks.

SELECT 
    ec.client_net_address AS [IP Address],
    es.[program_name] AS [Application],
    es.[host_name] AS [Client Host],
    es.login_name AS [Login],
    COUNT(ec.session_id) AS [Connection Count]
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK) 
    ON es.session_id = ec.session_id
GROUP BY 
    ec.client_net_address,
    es.[program_name],
    es.[host_name],
    es.login_name
ORDER BY 
    ec.client_net_address,
    es.[program_name]
OPTION (RECOMPILE);
        

How the Query Works

This script performs an inner join between two critical Dynamic Management Views (DMVs):

• sys.dm_exec_connections: Provides information about the physical transport layer and the client IP addresses.
• sys.dm_exec_sessions: Contains high-level metadata about each session, such as the authenticated user and the application name.

By grouping the data, you can pinpoint exactly which application or user is consuming the most connection slots. The OPTION (RECOMPILE) hint ensures that SQL Server generates a fresh execution plan every time you run the script, which is ideal for querying volatile system views that change every second.

Conclusion

Regularly auditing your connections helps identify unauthorized access and application-level bugs where connections aren't being closed properly. For a deeper dive into SQL Server management, ensure your applications are utilizing connection pooling correctly to avoid hitting session limits.

Found this SQL monitoring script useful? Share it with your DBA team or subscribe for more T-SQL performance tuning guides!

How to Check TempDB Version Store Usage per Database in SQL Server

Summary: Use the sys.dm_tran_version_store_space_usage DMV to monitor how much TempDB space is consumed by version store records for Snapshot Isolation and RCSI.

Monitor Version Store Space Usage in TempDB

Looking to understand how much space the version store is using in your SQL Server tempdb? The sys.dm_tran_version_store_space_usage dynamic management view (DMV) is your primary tool for troubleshooting 1458 errors or general TempDB growth. It provides a summarized view of the total space consumed by version store records for each database on your instance.

This view is highly efficient and inexpensive to run because it does not traverse individual version store records. Instead, it provides an aggregated count of the pages used, making it an ideal choice for real-time monitoring and performance dashboards.

Retrieve Version Store Space Usage

Run the following T-SQL script to identify which database is responsible for the largest amount of version store data in TempDB:

SELECT 
    DB_NAME(database_id) AS [Database Name],
    reserved_page_count AS [Reserved Page Count],
    reserved_space_kb / 1024 AS [Version Store Space (MB)]
FROM sys.dm_tran_version_store_space_usage WITH (NOLOCK)
ORDER BY [Version Store Space (MB)] DESC
OPTION (RECOMPILE);
        

This query returns a clear breakdown of space usage, allowing you to quickly pinpoint which database has long-running transactions or high-volume updates under Snapshot Isolation or Read Committed Snapshot Isolation (RCSI).

Why Version Store Usage Matters

The version store is used to support row versioning features. If a transaction remains open for a long time, SQL Server cannot clean up the version store in tempdb, which can lead to disk space exhaustion. Monitoring this DMV helps DBAs proactively manage server health.

---

Related Reading:

• For deep technical details, visit the official Microsoft Documentation.

Found this T-SQL monitoring tip helpful? Share it with your team or subscribe for more SQL Server performance tuning guides!

Track SQL Server CPU Usage History for the Last 256 Minutes

Summary: Use this T-SQL script to extract historical CPU utilization from SQL Server ring buffers, providing a minute-by-minute breakdown of SQL usage vs. system idle time.

Track SQL Server CPU Usage for the Last 256 Minutes

Ever wonder how your SQL Server has been utilizing its CPU over time? This handy T-SQL script allows you to view the CPU utilization history for the last 256 minutes, broken down into one-minute intervals. It's a fantastic way to quickly pinpoint any performance bottlenecks or trends related to CPU usage without needing external monitoring tools.

The CPU History SQL Script

DECLARE @ts_now BIGINT = (
    SELECT cpu_ticks / (cpu_ticks / ms_ticks)
    FROM sys.dm_os_sys_info WITH (NOLOCK)
);

SELECT TOP (256) 
    SQLProcessUtilization AS [SQL Server CPU %],
    SystemIdle AS [System Idle %],
    100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU %],
    DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
    SELECT 
        record.value('(./Record/@id)[1]', 'int') AS record_id,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
        [timestamp]
    FROM (
        SELECT [timestamp], CONVERT(XML, record) AS [record]
        FROM sys.dm_os_ring_buffers WITH (NOLOCK)
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
          AND record LIKE N'%<SystemHealth>%'
    ) AS x
) AS y
ORDER BY record_id DESC
OPTION (RECOMPILE);
        

How It Works

This script queries sys.dm_os_ring_buffers, specifically targeting the RING_BUFFER_SCHEDULER_MONITOR. This internal ring buffer captures system health events, including CPU utilization, directly from the SQLOS scheduler.

By parsing the XML output, we extract:

• SQLProcessUtilization: Percentage of CPU used by the SQL Server process.
• SystemIdle: Percentage of CPU that is currently unused.
• Other Process Utilization: Calculated as the remaining CPU used by Windows or other background applications.

For more in-depth information about how SQL Server manages internal tracking, check out the community resource: Inside sys.dm_os_ring_buffers.

Did this script help you find a bottleneck? Let us know in the comments below or check out our other performance tuning guides!

How to Get SQL Server Hardware Information using sys.dm_os_sys_info

Summary: Use this T-SQL script to extract critical hardware metadata from SQL Server, including CPU core counts, physical memory, NUMA configuration, and virtualization type.

Retrieve SQL Server Hardware and System Information

This query provides valuable hardware information about your database server, which is essential for licensing audits, performance tuning, and capacity planning.

Pro Tip: A 'HYPERVISOR' value for 'virtual_machine_type_desc' doesn't automatically confirm SQL Server is running inside a virtual machine. This merely indicates that a hypervisor is present on the host machine (which could also mean the host has Hyper-V enabled).

Hardware Configuration Script

SELECT 
    cpu_count AS [Logical CPU Count], 
    scheduler_count, 
    (socket_count * cores_per_socket) AS [Physical Core Count], 
    socket_count AS [Socket Count], 
    cores_per_socket, 
    numa_node_count,
    physical_memory_kb / 1024 AS [Physical Memory (MB)], 
    max_workers_count AS [Max Workers Count], 
    affinity_type_desc AS [Affinity Type], 
    sqlserver_start_time AS [SQL Server Start Time],
    DATEDIFF(hour, sqlserver_start_time, GETDATE()) AS [Up Time (hrs)],
    virtual_machine_type_desc AS [Virtual Machine Type], 
    softnuma_configuration_desc AS [Soft-NUMA Config], -- SQL Server 2016+
    sql_memory_model_desc, -- SQL Server 2016 SP1+
    container_type_desc -- SQL Server 2019+
FROM sys.dm_os_sys_info WITH (NOLOCK) 
OPTION (RECOMPILE);
        

Understanding the Results

By querying sys.dm_os_sys_info, you get a snapshot of the resources SQL Server "sees." This is particularly useful for identifying:

• Memory Model: Whether SQL Server is using conventional memory or "Locked Pages in Memory."
• Core Density: The ratio of physical cores to sockets.
• Soft-NUMA: Whether SQL Server is automatically managing NUMA nodes to improve performance on high-core-count machines.

---

Further Reading:

• Soft-NUMA (SQL Server) Official Guide
• sys.dm_os_sys_info Documentation

Need to tune your SQL Server performance? Check out our other scripts for monitoring CPU and memory usage!

How to Check SQL Server Drive Free Space using T-SQL

Summary: Quickly audit available disk space on your SQL Server host using sys.dm_os_enumerate_fixed_drives and xp_fixeddrives to prevent disk fullness issues.

How to Check Available Disk Space in SQL Server

Ever need to quickly check the available space on your SQL Server drives without logging into the OS? Here are two handy methods to query disk space directly from a query window. We'll explore using a modern Dynamic Management View (DMV) and a classic system stored procedure.

Method 1: Using sys.dm_os_enumerate_fixed_drives

This DMV is the preferred modern method. It provides detailed information about fixed drives, including their path, type, and exact free space in bytes. It is highly precise and easy to format into Gigabytes (GB).

-- Querying disk space using a DMV
SELECT 
    fixed_drive_path AS [Drive],
    drive_type_desc AS [Type],
    CONVERT(DECIMAL(18, 2), free_space_in_bytes / 1073741824.0) AS [Available Space (GB)]
FROM sys.dm_os_enumerate_fixed_drives WITH (NOLOCK)
ORDER BY [Available Space (GB)] ASC
OPTION (RECOMPILE);
        

Method 2: Using xp_fixeddrives

For a simpler and quicker overview, the xp_fixeddrives extended stored procedure is a classic standby. While it provides less detail than the DMV, it is excellent for a fast snapshot of MB available on each drive.

-- Simple summary of drive free space (MB)
EXEC xp_fixeddrives;
        

For more technical details on how the engine enumerates these paths, refer to the official Microsoft documentation.

---

Is your TempDB growing too fast? Monitoring your disk space is the first step in maintaining a healthy SQL Server environment!

How to Monitor SQL Server Resource Governor Pools using T-SQL

Summary: Use the sys.dm_resource_governor_resource_pools DMV to monitor memory utilization, IOPS limits, and target memory for your SQL Server resource pools.

Monitoring SQL Server Resource Governor Pools

Ever wondered about the current status of your SQL Server resource pools? When using Resource Governor to manage multi-tenant workloads or to limit internal background processes, it is critical to monitor how much memory and I/O each pool is actually consuming.

This quick query provides valuable insights into performance and memory utilization across all configured pools.

Resource Pool Utilization Script

SELECT
    pool_id,
    [Name],
    statistics_start_time,
    min_memory_percent,
    max_memory_percent,
    max_memory_kb / 1024 AS [max_memory_mb],
    used_memory_kb / 1024 AS [used_memory_mb],
    target_memory_kb / 1024 AS [target_memory_mb],
    min_iops_per_volume,
    max_iops_per_volume
FROM sys.dm_resource_governor_resource_pools WITH (NOLOCK)
OPTION (RECOMPILE);
        

Understanding the Metrics

This query leverages the sys.dm_resource_governor_resource_pools dynamic management view. Key columns to watch include:

• used_memory_mb: The actual amount of memory currently held by the pool.
• target_memory_mb: The amount of memory SQL Server is currently attempting to allocate to the pool based on workload demand and settings.
• max_iops_per_volume: Helps you identify if I/O capping is actively restricting a specific resource pool's performance.

Optimizing your SQL Server resources? Check out our other guides on monitoring CPU trends and TempDB usage!

How to View SQL Server Memory Dump History using T-SQL

Summary: Use the sys.dm_server_memory_dumps DMV to identify the location, creation time, and size of SQL Server memory dump files for crash analysis and troubleshooting.

Retrieve SQL Server Memory Dump Information

Understanding the status of memory dumps in your SQL Server instance is crucial for troubleshooting service crashes, unexpected restarts, and deep performance analysis. This method allows you to quickly identify if any dumps have been generated, where they are stored, and their total size.

Querying sys.dm_server_memory_dumps

You can use the following SQL query to access the sys.dm_server_memory_dumps dynamic management view. This view is highly useful because it prevents you from having to manually browse the SQL Server LOG folder in the file system.

-- Get details of all memory dumps generated by SQL Server
SELECT
    [filename] AS [Dump File Path],
    creation_time AS [Creation Time],
    size_in_bytes / 1048576.0 AS [Size (MB)]
FROM sys.dm_server_memory_dumps WITH (NOLOCK)
ORDER BY creation_time DESC
OPTION (RECOMPILE);
        

This query returns the full file path of the dump, the exact time it was triggered, and the size in MB. The results are ordered by creation time, putting the most recent events at the top of your list.

For more in-depth information about this DMV, refer to the official Microsoft Documentation.

---

Is your SQL Server generating frequent dumps? This usually indicates a serious underlying issue. Check your error logs or consult Microsoft Support for advanced analysis!

How to Identify System Manufacturer and Model via SQL Server Error Log

Summary: Use the xp_readerrorlog extended stored procedure to quickly retrieve hardware manufacturer and model details directly from the SQL Server startup sequences.

Identify System Manufacturer and Model from the SQL Server Error Log

Did you know you can quickly pinpoint your SQL Server's system manufacturer and model number directly from its error log? This is a highly efficient way to gather hardware details or verify server specifications without needing administrative access to the underlying Windows OS or BIOS.

Using a Simple SQL Query

When SQL Server starts, it logs several environmental details. You can use the following command to filter the current log for these specific entries:

-- Search the current error log for system hardware info
EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';
        

This command searches the current SQL Server error log (Log 0) for entries containing the word "Manufacturer." The output typically returns a line detailing the system's make (e.g., Dell, HP, VMware) and the specific model number.

Important Considerations

• Log Recycling: This query only yields results if the error log hasn't been recycled (restarted) since the SQL Server instance last started. If the log has cycled, you may need to change the first parameter from 0 to 1, 2, etc., to search archived logs.
• Virtualization: This method is excellent for confirming if your SQL Server is virtualized. Virtual environments like VMware or Azure will clearly state "VMware, Inc." or "Microsoft Corporation" with "Virtual Machine" in the model details.

---

Need to audit your hardware? Use this script as a quick win for your next server inventory report!

How to Check Database Access Permissions in SQL Server using HAS_DBACCESS

Summary: Quickly identify which databases on a SQL Server instance you have permission to connect to using the HAS_DBACCESS function.

Check Which Databases You Can Access in SQL Server

Ever wonder which databases you actually have permission to enter on a shared instance? Instead of clicking through every database in Object Explorer and waiting for "Access Denied" errors, you can run a simple script to audit your permissions at a glance.

Using the HAS_DBACCESS Function

The HAS_DBACCESS function returns a 1 if the user has access to the database, a 0 if the user does not have access, and NULL if the database name is invalid.

-- List all databases and check current user access
SELECT 
    name AS [Database Name], 
    CASE 
        WHEN HAS_DBACCESS(name) = 1 THEN 'Yes' 
        ELSE 'No' 
    END AS [Has Access]
FROM sys.databases
ORDER BY name;
        

Why Use This?

This statement is particularly handy for a quick security check. It helps you verify:

• If your login has been correctly mapped to a specific database.
• Which databases are visible but restricted to your current credentials.
• Connectivity status across a large instance with dozens of databases.

Working on security auditing? Combine this with our other scripts for a full view of your SQL Server environment!

Retrieve SQL Server Hardware and Registry Details via T-SQL

Summary: Learn how to use xp_instance_regread to query the Windows Registry for hardware details, including VMware PVSCSI parameters, BIOS release dates, and CPU specifications.

Querying Hardware and Registry Details from SQL Server

Ever wonder about the nitty-gritty details of your SQL Server's underlying hardware? While DMVs provide great data, sometimes peeking into the Windows Registry via T-SQL can give you specific environmental insights. Here is how to pull essential hardware information using xp_instance_regread.

1. VMware Storage Driver Parameters (PVSCSI)

For those running SQL Server on VMware, tuning your storage driver settings is critical for high-throughput I/O. You can check the current parameters for your PVSCSI adapter directly:

-- Check PVSCSI driver parameters
EXEC sys.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE', 
    N'SYSTEM\CurrentControlSet\services\pvscsi\Parameters\Device', 
    N'DriverParameter';
        

Performance Note: For intensive workloads, VMware often recommends setting RequestRingPages=32 and MaxQueueDepth=254. For more details, see the official VMware KB article.

---

2. Discovering BIOS Release Date

Identifying the BIOS release date is a quick way to determine if a host needs a firmware update to patch stability or security issues. Run this command to pull it from the registry:

-- Retrieve BIOS release date
EXEC sys.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE', 
    N'HARDWARE\DESCRIPTION\System\BIOS', 
    N'BiosReleaseDate';
        

---

3. Identifying Processor Details

Understanding exactly which CPU is powering your instance is fundamental for SQL Server licensing and performance tuning. This command grabs the full processor string:

-- Get CPU Model and Speed
EXEC sys.xp_instance_regread 
    N'HKEY_LOCAL_MACHINE', 
    N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', 
    N'ProcessorNameString';
        

For deep dives into processor selection and its impact on SQL performance, I highly recommend checking out Glenn Berry's blog on Processor Selection.

Found this SQL monitoring script useful? Share it with your DBA team or subscribe for more T-SQL performance tuning guides!

How to Find Tables Without a Primary Key in SQL Server

Summary: Use this T-SQL script to audit your database schema and identify any tables lacking a primary key, a critical step for ensuring data integrity and performance.

Identify Tables Without a Primary Key

In SQL Server, primary keys play a crucial role in maintaining data integrity and optimizing query performance. However, during development or when working with legacy databases, you may come across tables that lack a primary key—either by design or oversight. Identifying these tables is an essential step in ensuring your database is well-structured and reliable.

📌 SQL Query to Find Missing Primary Keys

The query below leverages sys.tables and sys.key_constraints to retrieve a list of all user tables in your current database that do not have a primary key defined:

-- Find tables without a Primary Key
SELECT 
    s.[name] + N'.' + t.[name] AS [Table Name],
    t.create_date AS [Created Date]
FROM sys.tables AS t WITH (NOLOCK)
INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
WHERE NOT EXISTS (
    SELECT 1
    FROM sys.key_constraints AS kc
    WHERE kc.[type] = N'PK'
      AND kc.parent_object_id = t.[object_id]
)
ORDER BY [Table Name]
OPTION (RECOMPILE);
        

🧩 Why This Matters

Tables without primary keys (often referred to as Heaps if they also lack a clustered index) can lead to significant architectural issues:

• Data Duplication: Without a PK, there is no physical way for the engine to enforce row uniqueness at the schema level.
• Performance Degradation: Heaps can lead to "RID Lookups" and inefficient data retrieval patterns.
• Replication Failure: Many features, like Transactional Replication and certain ETL tools, require a primary key to function.
• Join Complications: It becomes difficult to maintain reliable relationships between tables.

---

Optimizing your schema? Once you've identified these tables, consider adding an IDENTITY column or a natural key to improve your database's health!

How to Audit SQL Server Service Accounts and Status using T-SQL

Summary: Use the sys.dm_server_services DMV to quickly audit SQL Server service accounts, startup types, cluster status, and Instant File Initialization (IFI) settings.

Retrieve SQL Server Service Details and Configurations

Ever wondered about the nitty-gritty details of your SQL Server services? Instead of opening the Windows Services console (services.msc) or Configuration Manager, you can pull a comprehensive overview directly from a query window.

SQL Server Service Audit Script

This query provides a quick overview of essential information for each service associated with your current SQL Server instance.

-- Audit SQL Server services and configuration
SELECT 
    servicename AS [Service Name],
    process_id AS [PID],
    startup_type_desc AS [Startup Type],
    status_desc AS [Current Status],
    last_startup_time AS [Last Start Time],
    service_account AS [Account Name],
    is_clustered AS [Is Clustered?],
    cluster_nodename AS [Active Node],
    [filename] AS [Binary Path],
    instant_file_initialization_enabled AS [IFI Enabled]
FROM sys.dm_server_services WITH (NOLOCK)
OPTION (RECOMPILE);
        

Key Insights from this Query

• Service Account: Quickly verify if your services are running under the correct least-privileged service accounts.
• Instant File Initialization (IFI): Ensure IFI is enabled to speed up data file allocations and restores (SQL Server 2016+).
• Startup Type: Identify services that might fail to start after a reboot because they are set to 'Manual'.
• Cluster Details: Confirm which node in a Failover Cluster Instance (FCI) is currently hosting the service.

For more in-depth information regarding the columns in this DMV, refer to the official Microsoft documentation.

---

Performing a Health Check? Checking your service accounts and IFI status is a foundational step in any SQL Server performance audit!

How to Analyze SQL Server Memory Usage by Clerk Type using T-SQL

Summary: Use the sys.dm_os_memory_clerks DMV to identify which components—such as the Buffer Pool, Plan Cache, or Columnstore—are consuming the most memory in your SQL Server instance.

Identify Top Memory Consumers via SQL Server Memory Clerks

If your SQL Server is experiencing high memory pressure, the first step is to identify which internal component is "hogging" the RAM. SQL Server uses Memory Clerks to manage allocations for different features like the Buffer Pool, Plan Cache, and CLR.

This query provides a clear breakdown of memory usage by various clerk types, ordered by the highest consumption, helping you pinpoint exactly where the memory is going.

Top 10 Memory Clerk Usage Script

-- Get top 10 memory consumers by Clerk Type
SELECT TOP (10) 
    mc.[type] AS [Memory Clerk Type],
    CAST((SUM(mc.pages_kb) / 1024.0) AS DECIMAL(15, 2)) AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type]
ORDER BY SUM(mc.pages_kb) DESC
OPTION (RECOMPILE);
        

Understanding Common Clerk Types

• MEMORYCLERK_SQLBUFFERPOOL: This is usually the largest, representing the data cache.
• CACHESTORE_SQLCP: The SQL Plan Cache (ad-hoc queries).
• CACHESTORE_OBJCP: Object Plans (stored procedures, functions).
• MEMORYCLERK_SQLOPTIMIZER: Memory used during the query optimization process.

This query leverages the sys.dm_os_memory_clerks DMV. For more in-depth exploration of performance tuning, check out these related articles:

---

• Understanding Memory Grants Pending
• Retrieving Hardware Information
• Advanced Memory Clerk Auditing
• Finding SQL Server Memory Dumps

Seeing unexpected memory usage? Large amounts of memory in CACHESTORE_SQLCP might indicate a need to enable "Optimize for Ad-hoc Workloads"!