Monitoring Sql Server Cpu Usage: A Comprehensive Guide

Monitoring CPU usage on your SQL Server is one of the most important tasks in performance monitoring and troubleshooting. A high rate of CPU usage may indicate the need to upgrade the CPU or add multiple processors. It could also be a sign of a poorly tuned or designed application.

There are several ways to monitor CPU usage on a SQL Server. One way is to use the Processor: % Processor Time counter in Performance Monitor. This counter tracks the amount of time the CPU spends executing a non-idle thread. A consistent state of 80-90% may indicate the need to upgrade the CPU or add processors.

Another way to monitor CPU usage is through Dynamic Management Views (DMVs). DMVs are system views that provide information about CPU usage. For example, the sys.dm_os_ring_buffers view can be used to find CPU usage.

Additionally, there are third-party tools and scripts that can be used to monitor CPU usage on a SQL Server. These tools often use CPU utilisation as one of their fundamental metrics.

By monitoring CPU usage, you can identify potential issues and optimise your SQL Server's performance.

Identify high CPU usage

To identify high CPU usage in SQL Server, you can follow these steps:

Step 1: Check if SQL Server is causing high CPU usage

Use tools like Task Manager, Performance and Resource Monitor (perfmon), or Performance Dashboard in SQL Server Management Studio to verify if the SQL Server process is contributing to high CPU usage. For example, in Task Manager, check if the CPU column value for SQL Server Windows NT-64 Bit is close to 100%.

Step 2: Identify queries contributing to CPU usage

If the Sqlservr.exe process is causing high CPU usage, it is likely due to SQL Server queries performing table or index scans, sort operations, hash operations, or loops. To understand the current CPU usage by queries, you can run the following statement:

DECLARE @init_sum_cpu_time int, @utilizedCpuCount int

-get CPU count used by SQL Server

SELECT @utilizedCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'

-calculate the CPU usage by queries OVER a 5 sec interval

SELECT @init_sum_cpu_time = SUM(cpu_time) FROM sys.dm_exec_requests

WAITFOR DELAY '00:00:05'

SELECT CONVERT(DECIMAL(5,2), ((SUM(cpu_time) - @init_sum_cpu_time) / (@utilizedCpuCount * 5000.00)) * 100) AS [CPU from Queries as Percent of Total CPU Capacity] FROM sys.dm_exec_requests;

Step 3: Update statistics

Once you've identified the queries with the highest CPU consumption, update the statistics of the tables used by these queries. You can use the sp_updatestats system stored procedure to update statistics for all user-defined and internal tables in the current database.

Step 4: Add missing indexes

Missing indexes can lead to slower queries and high CPU usage. Identify queries with high CPU usage and missing indexes in the query plan, then review and tune the queries accordingly.

Step 5: Address parameter-sensitive issues

Parameter-sensitive issues can also contribute to high CPU usage. You can use the DBCC FREEPROCCACHE command to clear the plan cache and determine if this resolves the issue. If it does, consider using query hints like RECOMPILE, OPTIMIZE FOR, OPTIMIZE FOR UNKNOWN, DISABLE_PARAMETER_SNIFFING, or KEEPFIXED PLAN to mitigate parameter-sensitive issues.

Step 6: Resolve SARGability issues

SARGability refers to the ability to use an index seek to speed up query execution. Queries that are not SARGable may lead to table or index scans and higher CPU usage. Rewrite queries to make them SARGable, or consider creating computed columns with indexes to improve performance.

By following these steps, you can effectively identify and address high CPU usage issues in your SQL Server environment.

Monitor CPU usage on your instance

Monitoring CPU usage on your SQL Server instance is one of the most important tasks in performance monitoring and troubleshooting. Here are some methods and tools to help you monitor CPU usage on your instance:

SQL Server Management Studio

SQL Server Management Studio provides easy-to-use reports where you can monitor the usage of various system parameters. To monitor CPU usage, you can use the Performance Dashboard report.

Once you connect to your SQL Server instance, simply select Reports > Performance Dashboard to view the current and historical values of CPU usage. You can also find the top CPU consumers by selecting another report: Reports > Standard Reports > Performance - Top Queries by Average CPU Time.

Dynamic Management Views

The SQL Server Database Engine provides a set of system views that you can use to find CPU usage. For example, you can use the `sys.dm_os_ring_buffers` view to query CPU usage:

Sql

SELECT cpu_idle = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int'),

Cpu_sql = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')

FROM (

SELECT TOP 1 CONVERT(XML, record) AS record

FROM sys.dm_os_ring_buffers

WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE '% %'

ORDER BY TIMESTAMP DESC

As cpu_usage;

Performance Monitor (SQL Server-only)

Perf Mon is a tool that allows you to track CPU usage on the server or virtual machine where your SQL Server is running. Some of the parameters you can monitor include:

• Processor: % Privileged Time
• Processor: % User Time
• System: Processor Queue Length

Azure Portal (Azure SQL-only)

If you are using Azure SQL, the Azure portal provides an easy-to-use interface to monitor performance. You can navigate to your Managed Instance or Single Database in the Azure portal to view CPU usage on the main blade.

PowerShell Script

You can also use a PowerShell script to monitor CPU and memory usage for all your SQL Server instances. Here is an example script that gathers CPU and memory usage information from the specified instances:

Powershell

$server = "XXX"

$inventoryDB = "XXX"

$resourcesUsageTable = "

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'CPU_Memory_Usage' AND xtype = 'U')

CREATE TABLE CPU_Memory_Usage(

[server] [varchar](128) NOT NULL,

[max_server_memory] [int] NOT NULL,

[sql_memory_usage] [int] NOT NULL,

- More table columns...

ON [PRIMARY]

"

$instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $resourcesUsageTable

Foreach ($instance in $instances) {

Write-Host "Fetching CPU/RAM information for instance $($instance.instance)"

$results = Invoke-Sqlcmd -Query $resourcesQuery -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30

# Build the INSERT statement and insert data into the central table

}

Write-Host "Done!"

The above script will insert the CPU and memory usage data into a table named `CPU_Memory_Usage`. You can then create a job to run this script periodically to monitor CPU usage over time.

Use Dynamic Management Views

Dynamic Management Views (DMVs) are queries that return internal data about the state of the database or the instance. They are a great way to monitor database performance and troubleshoot slow queries. DMVs can be used to monitor CPU usage and identify queries that might be causing high CPU usage.

Sql

SELECT cpu_idle = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int'),

Cpu_sql = record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')

FROM (

SELECT TOP 1 CONVERT(XML, record) AS record

FROM sys.dm_os_ring_buffers

WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

AND record LIKE '% %'

ORDER BY TIMESTAMP DESC

As cpu_usage

This DMV queries the `sys.dm_os_ring_buffers` view, which provides a set of useful system views that you can use to find CPU usage. The `WHERE` clause specifies that we are interested in the `RING_BUFFER_SCHEDULER_MONITOR` ring buffer type and only want records that contain some data (`record LIKE '% %'). The `ORDER BY` clause orders the records by timestamp in descending order, so we get the most recent record first.

The `SELECT` statement inside the `FROM` clause converts the record to XML format and assigns it to the `cpu_usage` alias. Then, the main `SELECT` statement extracts the `cpu_idle` and `cpu_sql` values from the XML record using the `value()` function. These values represent the idle time and process utilization of the CPU, respectively.

Another useful DMV for monitoring CPU usage is `sys.dm_exec_query_stats`, which returns aggregate performance statistics for cached query plans in SQL Server. This DMV can be used to find queries with a high number of recompiles and expensive queries that may be causing high CPU usage.

Sql

SELECT TOP 10 creation_time,

Last_execution_time,

Total_elapsed_time,

SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,

CASE statement_end_offset

THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset) / 2 + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st

ORDER BY total_worker_time DESC;

This query selects the creation time, last execution time, total elapsed time, and the statement text of the top 10 queries with the highest `total_worker_time`, which essentially returns the queries ranked by processor time.

In addition to these DMVs, there are also other system views and counters that can be used to monitor CPU usage, such as `sys.dm_os_performance_counters` and `sys.dm_os_schedulers`.

Use Performance Monitor

Performance Monitor is a useful tool for tracking CPU usage on the server or virtual machine where your SQL Server is running. It is one of the most important monitoring tasks in performance monitoring and troubleshooting.

Performance Monitor has a counter called Processor:% Processor Time, which is an efficient way to determine CPU usage. This counter monitors the amount of time the CPU spends executing a thread that is not idle. A consistent state of 80-90% might indicate the need to upgrade your CPU or add more processors. For multiprocessor systems, monitor a separate instance of this counter for each processor. This value represents the sum of the processor time on a specific processor. To determine the average for all processors, use the System: %Total Processor Time counter instead.

Additionally, you can monitor the following counters to keep track of processor usage:

• Processor: % Privileged Time—This corresponds to the percentage of time the processor spends executing Microsoft Windows kernel commands, such as processing SQL Server I/O requests.
• Processor: %User Time—This corresponds to the percentage of time the processor spends executing user processes, such as SQL Server.
• System: Processor Queue Length—This corresponds to the number of threads waiting for processor time. A processor bottleneck may occur when threads of a process require more processor cycles than are available.

Monitor Processor Usage

Monitoring processor usage is a crucial aspect of performance monitoring and troubleshooting for any SQL server. By tracking CPU usage, administrators can identify potential bottlenecks and fine-tune the system for optimal performance. Here are some methods and tools to monitor processor usage effectively:

SQL Server Management Studio

The Performance Dashboard in SQL Server Management Studio provides an accessible way to monitor processor usage. After connecting to your SQL Server or Azure SQL instance, navigate to Reports > Performance Dashboard to view current and historical CPU usage values. This report offers valuable insights into the current state of your system. Additionally, you can identify top CPU-consuming queries by selecting Reports > Standard Reports > Performance – Top Queries by Average CPU time. This report helps pinpoint queries that may require optimisation to reduce CPU load.

Dynamic Management Views

Dynamic Management Views (DMVs) are a powerful tool provided by the SQL Server Database Engine. These system views allow administrators to monitor CPU usage and gain deeper insights into system performance. For example, the sys.dm_os_ring_buffers view can be queried to retrieve CPU usage data. DMVs offer a flexible and efficient way to monitor processor usage at a granular level.

Performance Monitor (SQL Server-only)

Perf Mon is a valuable tool for tracking CPU usage on the server or virtual machine running your SQL Server. It provides insights into specific parameters, such as Processor: % Privileged Time, Processor: %User Time, and System: Processor Queue Length. Monitoring these parameters can help identify potential issues related to processor usage and guide optimisation strategies.

Azure Portal (Azure SQL-only)

For those using Azure SQL, the Azure portal offers a user-friendly interface to monitor processor usage. By navigating to your Managed Instance or Single Database, you can easily access CPU usage information on the main blade. This centralised view makes it convenient to keep track of CPU utilisation and identify any potential performance bottlenecks.

PowerShell Scripting

PowerShell scripting provides a versatile way to monitor CPU and memory usage across multiple SQL Server instances. By using PowerShell scripts, administrators can automate the collection of CPU utilisation data, making it easier to track resource consumption over time. This approach is particularly useful when managing a large number of instances.

Third-Party Tools

Various third-party tools and community scripts are available to monitor processor usage in SQL databases. Many of these tools, such as Redgate SQL Monitor, Idera SQL Diagnostic Manager, and Apex SQL Monitor, provide in-depth metrics and offer free trials. These tools often include additional features and customisation options to meet specific monitoring needs.

Frequently asked questions