SQL 2000 Query runs very slow first time, then fast after that
Posted on 2009-04-07
I am executing a query in SQL 2000 on a Windows 2003 server running on a VMWare virtual serverand it always runs very slowly the first time it is run after the server has restarted (about 120 sec) and occasionally after that also(when it has not been run in a long tme). After that first run, it only takes 8 seconds to run. This would be expected as the plan has been figured out, the data read, and it is all in the cache. On the physical machine that the virtual machine was converted from the query always takes about 10 seconds to run.
The problem is that even when I clear the cache and buffers on the virtual machine with DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS it still runs in 8 seconds. Even stopping and restarting SQL Server itself and then going back into the Query Analyzer to run the query - it takes 8 seconds. I am very happy that it takes only 8 seconds (it is an aggregate query on two very large tables with invoicing data) but I need to come up with an explanation/fix for the times it takes over two minutes. Either my cache and buffer clearing isn't doing the job, or there is some other factor making it run long that first time.
PS - I know that the query and tables are not optimized and indexed as well as they could be. I have no control over changing the tables, but the activity is consistent - if I optimized it it might run in 60 seconds and then 3 or 4 seconds thereafter, but that's the same problem
select invoicedate as FOM,
from dbo.T_Invoice_Surcharge_Hist sh
inner join dbo.T_Invoice_header_HIST hh
where surchargeid='FC' and invoicedate>'1/1/2007'
group by invoicedate
Any thoughts or help would be appreciated.