First run slowness in a sql server stored procedure

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?

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...

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.

What else is happening at the same time the proc runs the first time each day? Could it be getting locks from some other process (backups, statistics updates, other scheduled jobs)?

stored procedures are chached in sqlserver.

if you use DBCC FREEPROCCACHE it will recompile the sql statement.

if your server is getting restarted (say on a nightly or weekly basis) it could possibly be having its cache cleared which would cause the query to run slow on its first try.

you can test that by running the above command and running the query to see if the same thing happens... if so i would say your cache is getting cleared.

Is AUTO_UPDATE_STATISTICS or AUTO_UPDATE_STATISTICS_ASYNC for the database on? It could be that the first run notices that stats need to be updated and does so. In the former set option, it waits for the the stats update to complete and for the latter, it doesn't wait for the stats update but may choose a suboptimal plan thus leading to bad performance.