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!
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