Quick to slow execution

Shah 2015-11-20 19:59:48

This query executes instantaneaously during the day (2-3 sec). The same query exceeds 30 seconds during our end of day processing (during this period, some queries still execute within 2 to 5 seconds). Could anyone explain why we may have this variation in performance ?

Aaron Bertrand 2015-11-20 20:01:47
Do you get the exact same plan during the day and at night when the executions take longer? If so, then I don't think it's about the plan itself, but rather about contention. Next time the query is running slow, check in sys.dm_exec_requests for wait type and blocking session id. It could be getting blocked due to concurrency or there could be resource contention that is slowing the operation down.