SQL Server 2005 Performance on first execution | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Server 2005 Performance on first execution

I’ve install sql server 2005 comercial version. But one thing that i found out is that the sql server 2005 will perform slower on the first execution.
For example, the same sql tht I run for the first time on a fresh new db restored will be having more Reads, longer durations (2X more) compare to the second time I run on the same thing. I’ve clear the cache with this command :
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE before i run the query. Any idea on what is causing this issue?
In my oppinion there is no a issue.
SQL take some time to load information when no one is using. In fact if you monitor the memory, is not growing if is not in use.
BTW: Sql 2000 run in the same way.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
But, I’ve tried with the same thing in SQL2000. It run as usual. This problem just found on Yukon…….any idea[?] [xx(]..as I am benchmarking a program between SQL2000 & Yukon, wondering which result should i take…[?]
After the restore have you attempted to reindex or run optimization jobs or atleast before taking backup on source server run those same optimization jobs for better performance. I have seen no difference in my end restoring database and running a resource intensive query would have longer time for the first execution. It is always best to perform update stats and recompile the SP on a newer installation to catchup the cache. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
yes. I did run the sp_updatestats & dbcc updateusage command. but the result on first run & second run is still showing a big differences.[xx(] Is it possible that there is a cache stored that not been clear when I run for the second time? Stated below is my command to clear the cache, is there any changes on sql2005?[?] CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Nope, I dont’ see any as perhttp://msdn2.microsoft.com/en-us/library/ms187762.aspx link. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
then it is very wiered.. I still having the same problem. Reads remain doubled on first execution, and then I clean the cache, and re-run the same thing, then the Reads dropped…. I’ve trying with other machine with the same installation.. problem still occured… [<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />]
I think the Dynamic Management feature added to SQL 2005 explains the behavior you are seeing. For SQL 2005, search the following in BOL: Execution Related Dynamic Management Views and Functions. From what I can tell, these execution plans are not cleared out using DBCC FREEPROCCACHE.
]]>