Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  First run of a query take much longer time

Author  Topic  luggg Starting Member 1 Post Posted - 2009-08-26 : 16:33:37 I have a query of about 6000 character long, and join 9 tables. When I ran it first time, it took about 15 seconds, and return 656 rows. I ran it again and this time it took 6 seconds. All successive runs take 6 seconds, unless I do some operation like drop and create indexes. I collected statistics by set statistics io and time and also monitored with profiler. I observed the followings 1st run, 2nd run 1) Physical reads, <100, 0 2) Read ahead ~4000, ~2000 3) CPU time 5719 ms, 5593 ms 4) CPU time from profiler 12344 ms, 5593 ms 5) Observed elapsed 16 s, 6 s 6) elapsed in stats 8059 ms, 6542 ms 6)profiler duration 14828 ms, 6541 ms 7) parse/compile 0 ms, 0 ms To summarize, compile took no time in both case,logical reads is the same, physical/ahead reads differ by ~2000, not very large. Times collected by SET TIME ON are similar in the two case. Profiler and the user observed 8-10 seconds more in 1st case. Can someone tell me what SQL Server spends this 8 seconds on? Thanks.