3

A quick background of the issue: We have an application, and we have many instances of this application running for clients. While they may be on slightly different versions, they are fundamentally the same.

Yesterday, one client had an issue with SQL timeouts. Looking at the query we identified an issue with certain tables, and the use of OUTER APPLY and re-wrote it to circumvent the issue.

Inspecting the query plan today, I can clearly see that the stats are bad, as it is expecting circa 2.5 million rows, which is incorrect. I updated the stats and it has resolved the issue, and now expects 30 rows.

My confusion comes from when I inspect the query plan for other clients' databases, and the stats seem off, but, the query is returned in around 1 second, not the 45 seconds seen in the issue that was faced.

Both databases have auto stats turned on. Does this suggest a problem with auto stats on the problem database?

Whilst testing, I did clear the cache DBCC FREEPROCCACHE so the engine had to generate a plan each time. I have not done this on a database that was returning the data in a timely fashion however.

Sorry for the vagueness, I unfortunately cannot share the query plan, due to sensitive information.

Currently, we run only automatic stats updates (no scheduled statistics / index maintenance). This will change; the databases have been neglected somewhat. I should also mention, these databases are in Azure. I am not sure if that changes anything?

Improve this question
0

1 Answer 1

Reset to default
7

Auto-stats and stats per se is only one aspect. Another is the plan, the calculated selectivity, and also the fact that plans can be cached.

So imagine a plan was generated when stats were "ok" (along with other circumstances such as sniffed parameter values). This can now stick around for a while in cache, and even if the stats deteriorate (a bit), your "good plan" can linger in cache.

Then you have the aspect that autostats samples data. The larger the dataset, the smaller the part of that data set that is sampled. So even when auto-stats kicks in, with a larger dataset you may get lower quality stats.

Improve this answer
2
  • Thanks. I did clear the cache (DBCC FREEPROCCACHE) and run again, in case of a bad plan, but it was consistently bad. Which lead me to the estimated number of rows being off. But, I see your point about a cached plan. I will check the other databases and see If I can compare their plans
    – Keith
    Commented Nov 10, 2020 at 14:08
  • 3
    @Keith you might find it helpful in testing Tibor's point by running DBCC FREEPROCCACHE in one of the databases where the stats are currently bad but the query is fast (and looking at the execution plan before and after). If the execution plan changes and the query is running slow now, then it's definitely an order of operations where a good query was cached first and then stats deteriorated over time after.
    – J.D.
    Commented Nov 10, 2020 at 19:07

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.