Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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...
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

721 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