Ask the Expert

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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: