3

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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