1

On a production MS SQL Server, the total call time for SQL queries for the last 5 days is (only) 11.5 minutes. (After queries have been optimized and several new indexes have been added).

However, the Task Manager reports that the SQL Server process (sqlservr.exe) has spent 5h:15min CPU time.

Anyone having an educated estimate on what MS SQL Server spends its time on in addition to queries?
Index updates running in the background? FYI: I have quite a large number of indexes in my DB.

Is it possible to ask SQL Server for other time usages than SQL-queries?

PS. It is not that 5h:15min is "dangerously" much time in 5 days, but it would be comfortable to understand more.

FYI. The query I use to get query times, is the following:

-- Returned times are in seconds
SELECT  total_elapsed_time / 1000000.0 as total_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 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 WITH(NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
3
  • 1
    What is CPU time? SQL server is always running, even if it's not processing queries or doing background jobs, it burns CPU cycles just to stay active. So the metric you are worrying about is irrelevant. Dec 10, 2021 at 11:29
  • "Irrelevant" seems to me to be a bit quick to say. 5 hours CPU time in 5 days, i.e. 1 hour CPU time each day is quite a lot. To me, much more than "irrelevant". Anyway, is 1 hour CPU time each day "just to stay active" normal? (The CPU is very fast, an AMD 3950X, så 1 hour CPU time is quite a lot). Dec 10, 2021 at 13:25
  • 1
    A parallel query plan can consume more than 1 sec of CPU time per sec of elapsed time. This is tracked in sys.dm_exec_query_stats.total_worker_time. Dec 10, 2021 at 14:23

3 Answers 3

3

Your query has several limitations:

  • It sums elapsed time. If you have any parallel queries, these may use multiple execution units concurrently (more than one second of CPU per second). You could address this by looking at the total_worker_time column instead.
  • User queries are only tracked by this DMV for as long as the original plan remains in cache. Any mechanism that removes plans from cache will result in an undercount. Some plans are never cached. The cache stores have a maximum size, and respond to instance memory pressure by removing plans. Recompilation can occur for many reasons.

Task Manager reports that the SQL Server process (sqlservr.exe) has spent 5h:15min CPU time.

You might see a number somewhat closer to the Task Manager time by summing total_cpu_usage_ms from sys.dm_os_schedulers. Even this total only includes time managed by SQLOS (cooperative multitasking). Tasks may run pre-emptively for some of their execution time, where scheduling is managed by the operating system. Background tasks tend to execute on hidden schedulers, but this isn't universal.

Anyone having an educated estimate on what MS SQL Server spends its time on in addition to queries?

There are a large number of such tasks. Which you see and how much processing time they consume depends heavily on your system features, configuration, and workload. There are far too many to list, but some examples are:

  • Asynchronous statistics updates
  • Checkpoint
  • Ghost cleanup
  • Lazy writer
  • Columnstore maintenance (tuple mover)
  • Deferred drop
  • Telemetry
  • Query Store aggregation and maintenance
  • Hekaton/CLR garbage collection
  • Deadlock monitor

I would not generally worry about any of this if the instance uses very little CPU when it is 'idle' (from a user workload point of view).

1

Task manager's CPU time is at best an estimate anyway. IIRC it, like top and similar tools on other OSs, counts a single core used 100% as 100% - so if you have many cores in that machine the wall-clock time consumed could be far less than the 5 hours. Also the reading doesn't take into account low power states your CPU might support and be in if the whole machine is relatively quiet - it might take 50% of what a core can do at its current speed for a second and this will be counted as 50% for that time, but that is less half what the core could do if spun up to full speed because there are many tasks needing attention.

In short: the CPU values in task manager don't mean nearly as much as you might think in modern environments.

There are a number of things that SQL server will do in the background constantly or regularly, when otherwise idle. Counting various things and putting out messages to system logging APIs (even if it is doing nothing it keeps telling the OS it is doing nothing in case someone is watching the relevant performance counters in Resource Monitor or similar), there will be regular checks to see if logs need to be cycled, if there are any jobs defined that need to start, and so on, each consuming a small amount of CPU time.If you have any external health monitoring that covers the SQL server instance that doing as little as connecting to make sure the instance is running and able to respond will result in a little CPU time being consumed.

1
  • My server has minimum CPU clock speed set fixed to 100%, so 1 hour CPU time is "real" 1 hour CPU time. Yes, my AMD 3950X server has 32 cores, so the 1 hour CPU time pr day amounts to only a single core full load 1 hour each day. But, still, 1 hour CPU time on a fast CPU each day is still quite a lot. My local/developement MS SQL Server instance (having same SQL Server verion) only consumes some 1.5 minutes single core CPU time each day. I am wondering about other peoples experience. Is 1 hour CPU only to "stay alive" normal? Or should some action be performed? Dec 10, 2021 at 13:39
0

You may be able to log the history of the dm_os_wait_stats view in order to get what is the instance doing over time.
Usually, take a snapshot of the following query, and make a graph of these results in any spreadsheet or data visualisation tool (like PowerBI for example).

SELECT wait_type, wait_time_ms  
FROM sys.dm_os_wait_stats;

You take snapshots over time to get the delta between each snapshot because wait_time_ms is a cumulative counter.

References : https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-ver15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.