CODEWITHSUNDEEP
sqldatabaseperformance
Ranga N
Ranga N

Reputation: 19

query slow once an hour or so, takes less than 100 ms 99% of the time

I am troubleshooting a slow query, it runs in less than 100 ms 99% of the time, but once in an hr (or two no pattern, i guess), goes bad and does 6 million reads and takes 11 seconds! I saw the query plan, it does do a clustered index scan, I noticed the cached_plans dynamic management view use counts column keeps increasing every time the query executes, so i am thinking its the same plan, just wondering why at one point it goes out-of-whack! any pointers will be helpful. I haven't tried anything as it runs pretty fast most of the time.

Upvotes: 0

Views: 802

Answers (1)

HLGEM
HLGEM

Reputation: 96600

First something could easily be blocking the query to make it run slow. Otr there could be other things happening onthe server at the same time that are consuming most of its resources.

Next, the parameters of the query might be bad for the saved execution plan.

Or the statistics might be out of date

Or if the query is an action query as opposed to a select, the particular parameters may be causing a problem in a trigger that makes it take longer.

Or teh query might be returning significanlty more results at times. If you run it at 10 and return 10 results and an import puts more records inteh table that meet the query conditions, at 10:30 you might return a million results which would clearly be slower.

One thing I like to do in such circumstances is set up logging so that the exact query is logged with the time at the time of execution. Then you can see what the query that ran sloweractaully was if you have varaible , than might be differnt from run to run.

Upvotes: 1

Related Questions