Sql-server – High-resolution CPU usage information for batches and statements

extended-eventssql serversql server 2014

I'm working on a tool that I use to evaluate the performance of SQL Server queries in a development environment. I use, and highly recommend, tools like SentryOne's Plan Explorer, but my tool reports on queries submitted by application code. So I use Extended Events.

One of the things I've found is that the resolution of CPU times reported for the sql_batch_completed and sql_statement_completed events is poor. Even though the cpu_time units is microseconds, the numbers reported are multiples of 15 milliseconds. That's much larger than the values I'm trying to measure, for a certain class of queries I work with. (Duration values are fine.)

Does anyone know a way to get higher-resolution CPU times? Maybe a server setting or a different field or event?

Best Answer

Durations made visible by SQL Server vary in their resolution and accuracy.

SqlWorldWide has an excellent answer showing some of the details.

Be aware of over-profiling SQL Server. Just like in quantum physics trying to measure the duration of a query too accurately will decrease the performance of the query itself. In other words, the very act of measuring undermines that which you are attempting to measure. Measure the performance of a group of queries instead of trying to measure the performance of each individual statement. If you need to understand why a single particular statement is running slowly, isolate that statement and evaluate it while letting the rest of the group run normally. Being aware of how SQL Server operates can be tricky enough, let alone trying to see if a query duration was 6 or 600 microseconds.

Related Solutions

Sql-server – Suspended query causing high CPU usage

Check for DBCC OPENTRAN for any transaction on these tables. If NOT then you are free to KILL these sessions as these are only select statements. Find the sessions by querying sp_who2 and kill by syntax KILL :-)

Sql-server – Extended Events, CPU_TIME

The sys.dm_xe_object_columns DMV shows metadata for each of the columns available for each event. Included is a "description" column, which is useful in answering your question.

select 
    name
    , object_name
    , type_name
    , description 
from
    sys.dm_xe_object_columns
where
    name = 'cpu_time' -- any XE column name here

This will show you all of the extended event objects that have a cpu_time column, and the description will tell you what unit of measure the column uses.

You can see here that units may vary depending on the object. For example:

sql_statement_completed: Indicates the CPU time (in microseconds) that is consumed by the statement.
sql_batch_completed: The CPU time (in milliseconds) used by the batch.

Best Answer

Related Solutions

Sql-server – Suspended query causing high CPU usage

Sql-server – Extended Events, CPU_TIME

Related Question