First run slowness in a sql server stored procedure

Question

I have a stored procedure that is run nine times a day, just after midnight. It isn't an ideal stored procedure, but you know how it is. No plan survives contact with reality.

This stored procedure normally takes about a minute to run, give or take time for the volume of data it processes. However, on the first run for a given morning, sometimes it will take an inordinate amount of time, sometimes well over an order of magnitude longer than the amount of time that it normally takes (if it finishes at all). If I kill it and start it again, it runs normally.

I'm looking for an elegant fix for this - at least more elegant than my first idea, which is to slap an extra run to go first which doesn't generate data I use and failure of which can be tolerated.

Has anyone seen this behavior before? How did you resolve it?

Answer 1

It's probably compile time and a cold data cache (buffer pool). And if it takes one minute normally, then I guess it's quite chunky too.

Compile time: an execution plan is invalidated on statistics update. If you have bulk processes or overnight maintenance you will probably hit this

Cold cache: data/index page have to come from disk into memory.

To mitigate these:

• A dummy run (as noted)
• faster IO or more RAM
• plan guides

We have the same issues, sometimes, especially on development boxes, to the extent that our websites time out for example. We just click again...

Answer 2

In order to provide a solution, the first thing to do is to investigate the cause. There could be many issues that would manifest as the symptoms you describe. Always start troubleshooting performance issues by following an investigation methodology, like Waits And Queues. this will reveal why is the procedure slow at first run. Likely culprits:

• a cold cache
• contention on some resources (locks?) with another process
• parameter sniffing causing a bad plan

Depending on what you find as being the problem, there will be an appropriate solution.

One thing you should not do is blindly change settings and hope the problem disappears, without ever understanding what was wrong.