Link to home
Start Free TrialLog in
Avatar of fgren

asked on

Stored procedure in SQL 2005 on Vmware sometimes slow

I have a stored procedure which doesn't do anything particularly advanced, it's basically a select statement selecting a couple of hundred rows from a set of a couple of thousand objects. Data is distributed in a couple of related tables and joined together with subqueries when necessary. All tables are actually views, constisting of a basetable with one add and  one delete table (ArcSDE multiversion views if you're into ESRI geodatabases).  The procedure runs on SQL server 2005 running on vmware. On the same virtual machine a ArcGIS sde server is running. What else is running on the same physical machine is out of my control.

The problem is that sometimes the procedure runs slow. In normal cases it takes 0-2 seconds, but sometimes it takes three minutes. There is no change in statistics between slow and fast performance, and once it has started to run slow it ususally is slow for several hours. It usually goes back to fast again by itself. There is no gradual decrease in performance except for an intermediate level at around 16 seconds. A typical scenario is that when it starts to run slow I can reload the procedure and make it execute in 16 seconds. Subsequent calls to the procedure will take three minutes. Looking at the statistics gives that for the 16 second run it uses some physical reads (<100) and on the three minute run it does not perform any physical reads. Table scans are also kept at a reasonable level and there is no difference between fast and slow. For the 0-2 second run statistics are exactly the same as for the three minute run. Also the execution plan looks the same.

I haven't seen any correlation whatsoever of performance and my input, it just seems like the server is in a bad mood that lasts for hours. The "mood" only seems to affect procedures using a lot of joined views, all other procedures run as usual. Also the ArcGIS sde seems unaffected.
Avatar of slam69
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I agree with slam69 - set up a trace (performance monitor + SQL Profiler) when the slowdown happens and see if there is anything out of the ordinary that could contribute to the slowness.

However, my experience from running SQL Server on a Virtual Machine has led me to not take them too seriously for performance as it's always orders of magnitudes worse than our true SQL Server. If possible, try running it on a non-virtual server as you may well find all is well.