Q

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 last published in November 2003

Dig Deeper on Microsoft SQL Server Installation

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close