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 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 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 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"!