Sql-server – Discrepancy in CPU time and reads in SQL Server profiler

performanceprofilerquery-performancesql serversql-server-2016

I had performance degradation in our production database server which is SQL server 2016 and I saved the long running query using profiler as you can see
in the following picture:

CPU time = 65141, Reads = 36474959, Duration = 114546

I have saved the trace file and open later, so I'm not sure if duration is millisecond or microsecond.

enter image description here

But, now when I run the same query on prod outside of business hours, the query run fast and "set statistics IO on" shows only few data pages being read, my assumption was CPU time and reads of a query should be constant regarding of execution time of a query despite of duration, any thought?
Should I consider this query as inefficient based on the numbers?

Best Answer

  • @DanGuzman correctly pointed you to Sommarskog's excellent article, which you should read.

    • Then, if the below simple steps don't work, try looking at the plan cache and seeing what's in there. Or use the Query Store, since you're on SQL 2016, to see how many different execution plans there are.

  • You're comparing SET STATISTICS (anything) to Profiler. Don't do that. It doesn't work. Profiler and Extended Events capture relatively accurate numbers. SET STATISTICS (anything) does NOT - as a single example, it doesn't capture function cost!

  • You're trying to match plans done at different times, probably with different statistics. Stop that.

  • Go ahead and update statistics for ALL tables involved in the suspect query, preferably with FULLSCAN, bother to see if that changes the behavior, and before your "run it myself" tests.

  • Look at the query itself.

    • Is parameter sniffing involved?
    • Is it thousands of lines long once reasonably formatted, with dozens of joins, often to the same table? If so, quit having Entity Framework try to create complex SQL statements on its own. It does a terrible job.
Related Question