Post reply

Query compile/runtime issue

  • April 23, 2026 at 4:37 pm

    #4777210

    I have a plan which in sys.query_store_plan shows:

    Last_compile_start_time of 2026-04-23 00:13:00.7670000 +00:00

    Last_execution_time of         2026-04-23 00:30:18.7770000 +00:00

    Last_compile_duration of 17105

    Sys.query_store_runtime_stats for the plan, between 2026-04-23 00:00:00 and 2026-04-23 01:00:00, show an avg_duration of 74429 and execution_count of 1

    sys.query_store_wait_stats shows a CPU and Buffer IO waits totalling 33ms

    So if I follow correctly?:

    The plan compilation started at 00:13:00 and took less than a second

    The query took less than a second and finished at 00:30:00

    In which case there is around 17 minutes between the end of the compilation and the start of the query running and I'm trying to understand why that would happen.

    If I understand correctly then if the CPUs are busy then the schedulers will alternate between the worker threads so that shouldn't prevent a query from starting and any delays would show up as cpu waits?

    Are there situations where for example, if the server is low on memory, the query won't start and won't error? I did some reading around that but didn't find anything.

    I'm not asking for anyone to write out all the possible scenarios I'd just appreciate some advice on whether I've missed something, where to look next or any relevant topic which I've missed and need to go and read about.

    Thanks

    • This topic was modified 2 weeks, 6 days ago by as_1234. Reason: relative changed to relevant

  • April 24, 2026 at 2:33 pm

    This was removed by the editor as SPAM

  • May 7, 2026 at 2:13 pm

    #4817768

    "Blocking" could cause this... and it can be for a LONG time.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 7, 2026 at 5:22 pm

    #4818496

    Low memory may cause the delay but you would see the evidence such as memory grant waits(Resource_Semaphore), compile memory pressure(Resource_Semaphore_Query_Compile). You can look at sys.dm_exec_query_memory_grants dmv to check if memory is the issue.

    You can also look if Sql server runs out of workers by querying sys.dm_os_schedulers.

    Note: last_compile _start_time is the last time the plan compiled and not the compile time associated with this exact execution.

    Deepesh Dhake
    Database Administrator

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply