Execution Plan Creation Time > Query Execution Time

  • Hello all!

    I have a query(from a third party application) where the time to create the execution plan takes 23 seconds and the actual execution of the SQL takes 20 seconds.

    Is there any chance to improve this, eg. limit the amount of time for execution plan generation or to find out why the creation takes so much time?

    Thanks in advance!

  • Post the query? That's not a normal situation.

    Nested views? Nested functions?

    Memory pressure on the server? Contention on the plan cache?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It really depends on the query. I had one that took 3 minutes to compile but ran in about 500ms. It was an abnormal situation (86 table join), but easily identifiable. I suspect you might be in a similar situation.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

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