How to know if your SQL Server are under CPU pressure? Signal Waits Time can help you understand it.

Imagine you’re at a busy coffee shop, and you’re waiting for your coffee. The coffee shop has a barista who is currently making drinks for other customers.

Signal Wait Time is like the time you spend waiting for the barista to finish serving others and call your name so you can get your coffee. You are “ready” to get your coffee (or to complete your task), but you’re waiting for your turn. In the meantime, the barista is still serving other customers, and you can’t do anything until they’re done.

In computing, Signal Wait Time refers to the time that a process or task is ready to run but has to wait for the resources it needs (like CPU time). The task is ready to go, but it can’t proceed until it’s its turn to get access to the resource.

To determine if your SQL Server is under CPU pressure, you should focus on indicators:

1. High CPU Utilization: Monitor overall CPU usage on the server. If SQL Server consistently consumes a high percentage of CPU (above 80–90%), it could indicate CPU pressure.
2. CPU-Related Wait Types: Check for wait types like SOS_SCHEDULER_YIELD, THREADPOOL, and CXPACKET, which are common signs of CPU contention or bottlenecks.
3. Long-Running Queries: Identify queries that consume excessive CPU time, especially those with high cpu_time or total_elapsed_time, which might indicate inefficient or complex queries.
4. Worker Threads: If there are a large number of concurrent worker threads, it could be a sign that SQL Server is under CPU strain.
5. Performance Metrics: Use SQL Server’s DMVs to track internal CPU usage and monitor the overall CPU load caused by SQL Server requests.
6. System Health Session: Check the System Health Extended Event session for CPU-related events, which may highlight underlying issues.

But, where Signal Waits time are?

Signal wait time is a specific type of wait time that occurs in SQL Server when a worker thread is waiting for CPU to be available to execute its tasks. It is an important metric to monitor when investigating CPU pressure, as it indicates a direct relationship between the available CPU resources and SQL Server’s ability to execute queries.

What is Signal Wait Time?

In SQL Server, when a query or task is waiting to be executed, it is assigned to a worker thread. However, if there are not enough available CPUs (due to high CPU utilization or other tasks), the worker thread has to wait for CPU Time to execute its operation. This waiting time is known as signal wait time.

Signal wait time occurs specifically when a worker thread is waiting for the scheduler to assign it CPU time. If the system is under heavy CPU load, worker threads can be delayed because the CPU resources are fully utilized, leading to increased signal wait times.

A high value for Signal waits statistics indicates potential CPU pressure, which is not ideal for the system. In simpler terms, lower values are preferable, while higher values suggest performance issues related to CPU usage.

Relationship Between Signal Wait Time and CPU Pressure

SQL Server has a query life-cycle:

• Running: A query that is currently being executed on the CPU is referred to as a running query. This query is actively consuming CPU time.
• Runnable: A query that is ready to execute but is waiting for its turn on the CPU is called a runnable query. This query contributes to Signal Wait time, meaning it’s prepared to run but is currently being delayed because the CPU is busy with another query.
• Suspended: A query that is waiting for a specific resource or condition before it can become runnable is termed a suspended query. The reasons for suspension can be identified through wait statistics. Suspended queries contribute to wait time, which is a period we typically aim to minimize in order to improve overall system performance.

When signal wait time is high, it generally indicates that there are not enough available CPUs to handle the workload, which is a clear sign of CPU pressure.
High signal wait times suggest that SQL Server is trying to execute many queries or operations, but the available CPUs are being fully utilized, causing delays in execution.

Increased Signal wait time affects performance can lead to slower query performance because the worker threads are waiting to get CPU resources rather than actively processing queries.
If the system is CPU-bound, SQL Server may experience long waits before queries can be scheduled for execution, resulting in poor response times, particularly under heavy query load.

As the CPU becomes saturated, more threads compete for limited CPU time, which increases the signal wait time.

Here’s a query you can use to check Signal Waits time:

SELECT  CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20,2)) AS signal_cpu
FROM    sys.dm_os_wait_stats ;

This measures the percentage of time a thread spends waiting for CPU resources. A high signal wait time across all wait types may indicate CPU overutilization, causing tasks to wait for SQL Server processes to be serviced.

These values are calculated and accumulated since the last server restart. If you want, you can save this collection in a table on SQL Server.

Monitoring and reducing signal wait time involves optimizing queries, managing CPU resources, and potentially increasing the CPU capacity of the server.

Conclusion

Understanding signal wait time is crucial when diagnosing CPU pressure in SQL Server. Just as waiting for your turn at a busy coffee shop can delay your ability to enjoy your coffee, high signal wait time indicate that your SQL Server queries are ready to run but are delayed because the system’s CPU resources are fully utilized.

By monitoring key metrics such as high CPU, CPU related waits and signal wait time, you should determine if your SQL Server is experiencing CPU strain. A significant increase in Signal Wait Time suggests that there aren’t enough CPU resources to meet demand, leading to delays in query execution and poor overall performance.

By proactively monitoring and addressing these signs of CPU bottlenecks, you can ensure that SQL Server operates more efficiently, delivering faster query performance and a smoother experience for your users.