First execution of SP 10 seconds longer than rest

I measured the execution time of a stored procedure for different test members. The execution times of all tests are about 2 seconds, except the first execution of the day that took 12 seconds. I observed this 10 seconds delay for the first execution with different test members everyday. We're running SQL 6.5 on NT4.0 SP6 server. Normally, we don't reboot the SQL Server.

You are dealing with data caching. In particular the limited scope of data caching. In essence what you are seeing...

is that data gets aged out of your data cache and needs to be reloaded. Once the data is in the cache, the access to it is simply a call to a memory structure instead of the much more expensive disk access. My best guess without any more information is that you have processes that run during the night that essentially utilize all or almost all of your data cache so that the first test during the day requires disk access for data since it doesn't exist in the data cache at that point.


For More Information

This was first published in November 2003

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: