CPU utilization trends

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.

The 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 Process CPU Utilization]
 ,SystemIdle AS [System Idle Process]
 ,100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
 ,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 looking at the `RING_BUFFER_SCHEDULER_MONITOR` type. This ring buffer captures system health events, including CPU utilization. By parsing the XML output from the `record` column, we can extract details like `SystemIdle` (how much CPU is idle), `SQLProcessUtilization` (how much CPU SQL Server is using), and then calculate the CPU usage by other processes.

For more in-depth information about `sys.dm_os_ring_buffers` and how it's used, check out this great resource: Inside sys.dm_os_ring_buffers.

Give this script a try and let us know in the comments if you found it useful for your SQL Server monitoring!

Check all drive space on the server

Ever need to quickly check the available space on your SQL Server drives? Here are a couple of handy SQL queries that can help you out. We'll explore using a dynamic management view and a system stored procedure.

Method 1: Using `sys.dm_os_enumerate_fixed_drives`

This dynamic management view provides detailed information about fixed drives, including their path, type, and free space. It's a robust method for obtaining precise data.

SELECT fixed_drive_path
    ,drive_type_desc
    ,CONVERT(DECIMAL(18, 2), free_space_in_bytes / 1073741824.0) AS [Available Space (GB)]
FROM sys.dm_os_enumerate_fixed_drives WITH (NOLOCK)
OPTION (RECOMPILE);

Method 2: Using `xp_fixeddrives`

For a simpler and quicker overview, the `xp_fixeddrives` extended stored procedure is a great option. It returns a summary of free space on fixed drives.

xp_fixeddrives

You can find more in-depth information about sys.dm_os_enumerate_fixed_drives on the Microsoft documentation.

---

Further Reading on SQL Server Space Management

If you're interested in learning more about managing space in SQL Server, check out these related posts:

• Get TempDB Version Store Space Usage by Database
• Log File Used Space

Get any memory dumps from SQL Server

Understanding the status of memory dumps in your SQL Server instance is crucial for troubleshooting and performance analysis. This post provides a simple method to quickly get details about any memory dumps, including their location, when they occurred, and their size.

Retrieve SQL Server Memory Dump Information

You can use the following SQL query to access data from the sys.dm_server_memory_dumps dynamic management view. This view provides valuable insights into recent memory dump events.

SELECT
    [filename],
    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 will return the **filename** of the dump, its **creation time**, and its **size in megabytes**. The results are ordered by creation time, showing the most recent dumps first.

For more in-depth information about the sys.dm_server_memory_dumps view, you can refer to the official Microsoft documentation: sys.dm_server_memory_dumps on Microsoft Docs

Related SQL Server Memory Management Resources

• Get Memory Clerk Usage
• Get In-Memory OLTP Index Usage
• Check Memory Pressure on the Server
• Memory Grants Pending Value
• Resource Governor Resource Pool Information

Get Memory Clerk Usage

Here's a useful SQL query to help you understand how memory is being utilized by different components within your SQL Server instance. This query provides a clear breakdown of memory usage by various memory clerk types, ordered by the highest consumption.

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);

This query leverages the sys.dm_os_memory_clerks Dynamic Management View (DMV), which is an excellent resource for monitoring SQL Server memory. For more in-depth exploration of SQL Server memory and performance, check out these related articles:

• Understanding Memory Grants Pending in SQL Server
• Retrieving Hardware Information from SQL Server
• Another Look at Getting Memory Clerk Usage
• How to Get Memory Dumps from SQL Server

Feel free to leave a comment if you have any questions or further insights on SQL Server memory management!

How to find log file used space in SQL server

How to find the log file used space in SQL Server

Check the log size in percentage across all databases using the following SQL query:

DBCC SQLPERF(LOGSPACE)

The result of the query provides details about the log space used by different databases. Below is an example of how the output might look:

Database Name   Log Size (MB)   Log Space Used (%)   Status

-----------------------------------------------------------

master          1.992188         43.52941            0

tempdb          15.99219         2.393747            0

model           7.992188         18.23069            0

msdb            28.80469         7.770545            0

This output shows the log size and the percentage of space used for each database. The data is useful for monitoring and maintaining the health of your SQL Server databases.