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
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in November 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation