11

One of our customers has just upgraded to a new server.

For a particular stored procedure the first time you execute it, is taking over three minutes to run. Subsequent runs are less than 1 second.

This leads me to believe that the initial three minutes are primarily taken up calculating the execution plan. Subsequent runs then just use the cached plan and run instantaneously.

On our test databases it is taking about 5 seconds to calculate the plan for the same procedure.

I don't see anything terrible in the plan itself - although I don't imagine its relevant as the plan shows how long it takes to run the query, not calculate itself.

The server is a 16 core with 24 gb memory. No heavy CPU or memory load occurs.

What is it that could be causing such a slow calculation only on a particular database?

What steps can I take to find the cause of the problem?

Edit

So I have managed to access the server and have run the query with SET SHOWPLAN_XML ON.

I can confirm that the CompileTime for the query is taking up 99% of the query execution time. The StatementOptmEarlyAbortReason is "TimeOut", on our test database with a copy of their database the reason is MemoryLimitExceeded.

Improve this question
10
  • 1
    Are you sure its the plan compilation that's taking a long time, not just reading data into cache from disk?
    – Mark Storey-Smith
    Feb 1, 2012 at 11:10
  • 1
    Compile Time does show up in the plan XML itself. Have you identified it is definitely compile time and not something else e.g. synchronous auto update of statistics? What version of SQL Server are you on?
    – Martin Smith
    Feb 1, 2012 at 11:14
  • @MarkStorey-Smith Well I'm not really sure about anything. I do know that running DBCC FREEPROCCACHE, which only clears out that plan cache, will take the next query time back up to 3 minutes.
    – Mongus Pong
    Feb 1, 2012 at 11:16
  • @MartinSmith Ah yes very useful, I have been using the visual query plan. Yes the compile time is roughly the entire execution time of the query. We are on SQL Server 2008 R2.
    – Mongus Pong
    Feb 1, 2012 at 11:27
  • @MongusPong - If you create a statistics only copy of the production database can you reproduce this in your test environment? What does the plan say for StatementOptmEarlyAbortReason?
    – Martin Smith
    Feb 1, 2012 at 11:29

1 Answer 1

Reset to default
11

I hate to answer my own question, especially since I have had so much help from others to work out the solution, but here goes.

The problem was due to some dud statistics in the database. Looking at the execution plan, the optimiser was expecting 11.5tb of data returned from the query. In reality is was receiving 87kb. I now know that huge mismatches between expected and actual rows returned is a sign that the statistics are out of date.

Simply running

exec sp_updatestats

forces the database to update the statistics for all tables.

This has taken the query execution time down from 3 minutes to 6 seconds. Everyones a winner!

Thanks for all the help guys. :0)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.