Go Premium for a chance to win a PS4. Enter to Win


SQL 2000 Query runs very slow first time, then fast after that

Posted on 2009-04-07
Medium Priority
Last Modified: 2012-05-06
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,
count(*)as number
from dbo.T_Invoice_Surcharge_Hist sh
inner join dbo.T_Invoice_header_HIST hh
on hh.invoicenumber=sh.invoicenumber
where surchargeid='FC' and invoicedate>'1/1/2007'
group by invoicedate

Any thoughts or help would be appreciated.

Question by:baytowel
  • 5
  • 3
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24087723
if you create a stored procedure and use the execution times will be better as SQL server will save the execution path

Author Comment

ID: 24088097
No luck there - same issue, same speeds.  I saved the query as a stored procedure and rebooted and it still takes 2 min. Rerun it immediately 3 or 4 times and it only takes 8 sec each time with DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE.  Without them it takes 0-1 sec (all data cached is still cached as I am the only one using this box). Reboot again - back to 2 minutes again.
LVL 60

Expert Comment

ID: 24090304
I would say almost for sure it is because the VMWare server is being used...
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.


Author Comment

ID: 24091382
It is not VMware in general, because If I create a new VMWare virtual machine and install Windows 2003 server fresh, and then install SQL 2000 fresh and attach or restore the databases I have on my production server, I don't have this problem - the query runs in a very short (faster than the physical box by about 10%) amount of time.

This issue is happening on a VMWare virtual machine that was made with VMWare converter from a physical box to a virtual one (P2V'd).  It has plenty of CPU and memory allocated, all physical drivers (hidden and otherwise) removed, VMWare Tools installed, etc. and seems to run fine otherwise.  We haven't benchmarked it compared to the original as far as Windows performance goes - we are just comparing query run times so far as a quick easy litmus test. That's when we noticed the problem.
LVL 12

Accepted Solution

udaya kumar laligondla earned 1500 total points
ID: 24093455
SQL server will create the execution plan and keeps in memory for future use. as you are actually wiping out all this info by creating new virtual machine, SQL will execute for the first stime without any plan, for later executions the same plan is used. so, first time execution you will face this issue.

Author Comment

ID: 24095762
I don't think I am understanding your meaning. Are you talking about the first time that it was ever run, or the first time after every reboot?  As I mentioned above, this issue happens *every time* the machine is restarted.  I can restart my physical box  just as many times, and it *never* does this when run there - the first run after a reboot takes the same amount of time as any other run where I clear the cache first (in this query's case, 30 seconds on the physical machine)
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24095930
Creating a VM is destroying the database fully with all optimization info. but in case of physical box the data about optimization is present with SQL server.
SQL is createing the optimal execution plan every first time you execute the code on VM. but not on the physical box as it already has it.

Author Comment

ID: 24096334
To look at your idea more closely, I went back to do some more time testing on the freshly installed 'from scratch' SQL virtual machine. A second look does seem to show that I am having the same issue there, but the speeds are dramatically different form the converted box, so there is still a major issue to solve.  

On the fresh (new installs) VM, the query takes 10 sec after a reboot (compared to 105-120 seconds for the converted VMand 30 seconds on the physical box) and a re-run with cleaned cache takes a noticeable and measureable 1 second (versus 30 seconds on the converted VM and 30 seconds on the pysical box).  A run with no cache cleaning takes an immediate screen refresh (0 sec) on both VM machines and 1 second on the physical box.

So while the fresh VM is an order of magnitude faster than the converted VM (and also way faster than the original machine, which is awesome and the speed I would like to get all the time) it does look like there is the same issue as the converted box, which would follow your suggested answer.

So II have some followup questions:

Why does it need to do it every time the VM is restarted?  Why doesn't it (or can't it) save the info the same way the physical box does?

Doesn't the DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE clean out the plan as well?  I would expect that after clearing the cache that it would need to recalc the plan as well.

Is there a way to make is save the execution plan so that it doesn't need to figure it out every time?

Even with apparently the same issue, why am I getting such a dramatic speed difference between my converted VM and my fresh install VM.  I would gladly take the re-plan perfomance hit if I could get all my queries to run as fast as they do on that machine.  This may be fodder for a new thread on a VM forum, not here, but it may still be a SQL issue as well.

Author Closing Comment

ID: 31567527
This answered my original question of why it takes so long to run a query the first time on a VM, but doe snot answer the other issues that came up while investigating this - namely why the queries have such different run times on a converted VM compared to a newly installed from scratch VM

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question