Solved

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

Posted on 2009-04-07
9
1,112 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to upload /Create/manage SQL database on Azure 3 32
Returning data in a different format 8 46
Query / Window function ? 3 18
Update data using formula 22 22
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

895 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

16 Experts available now in Live!

Get 1:1 Help Now