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?

  • 4
    Looks like different execution plans. You might get different plans running the query from SSMS versus the app. The Profiler duration defaults to milliseconds but is configurable under the Tools-->Options menu.
    – Dan Guzman
    Feb 18, 2018 at 19:34
  • Also "outside of business hours" is not an ideal comparison of something that likely runs under different circumstances during biz hours. Feb 18, 2018 at 23:23
  • You can also get different query plans based on the amount and nature of the data. Many environments have far more data in prod, so the query plans that work nice in test may not be the query plans that get generated in prod, or, as it seems in your case, vice versa. Feb 19, 2018 at 2:14
  • 1
    Do you have query store enabled? Feb 19, 2018 at 6:21

1 Answer 1

  • @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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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