question

sqlLearner 1 avatar image
sqlLearner 1 asked

Extended Event Question

I am setting up an extended event to track CPU_TIME using the sql_statement_completed event. What is a good baseline number to use as a filter for CPU_TIME? The description of cpu_time is it indicates the CPU time (in microseconds) that is consumed by the statement. Any thoughts? Thank You!
sql-server-2008sql-server-2012tsqlextended-events
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
It really depends on what you're trying to capture. What does a "long running" query look like on your system? I had systems where anything more than 8 seconds raised a red flag. I've had systems where anything over 2 minutes was too slow. I had one system that was calling a query so frequently that when we tuned it from 8ms to 5ms it was a giant win. There just isn't a single right answer here. It's totally system dependent. To get a ballpark figure, just as a starting point, let's go from the middle of the range of what's currently in cache. You can get the average of max_elapsed_time from sys.dm_exec_query_stats. It's a starting point: SELECT AVG(deqs.max_elapsed_time) FROM sys.dm_exec_query_stats AS deqs
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlLearner 1 avatar image sqlLearner 1 commented ·
Great thanks for the tip, I am new with performance metrics and trying my best to absorb all the information I am coming across.
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
The main thing is, make the bosses happy. Anything beyond that is gravy. But, in general, always get a measuring stick, what your systems are doing, to measure against. Don't sweat too much what some other person's systems say is best.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.