Solved

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

Posted on 2009-04-07
9
1,099 Views
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

Query:  

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.

0
Comment
Question by:baytowel
  • 5
  • 3
9 Comments
 
LVL 12

Expert Comment

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

Author Comment

by:baytowel
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.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24090304
I would say almost for sure it is because the VMWare server is being used...
0
 

Author Comment

by:baytowel
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Accepted Solution

by:
udayakumarlm earned 500 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.
0
 

Author Comment

by:baytowel
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)
0
 
LVL 12

Expert Comment

by:udayakumarlm
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.
0
 

Author Comment

by:baytowel
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.
0
 

Author Closing Comment

by:baytowel
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now