July 29, 2008 at 12:16 pm
Hi,
I have test machines, with similar configurations, running the same database, with same data.
But when i run the same query on these two machines, profiler shows me different cpu, reads, writes & duration of this query. Why is that so?
The only difference is the query with less cpu, read, write, duration is running on the VM (that is sql server on one test machine is on VM).
Thanks,
Usman
July 29, 2008 at 12:35 pm
Key word and tricky phrase in the question is "similar"
Variations in memory, cpu, disk I/O, cache, cache size, statistics, index fragmentation, yada, yada, yada, will result in differences in execution times, I/O and even the execution plan. If you want precisely exactly the same, point-by-point comparisons, you need to be testing identical databases using identical systems with identical parameters under identical load. All it takes is a checkpoint to run during your query to get a variance between servers in terms of execution time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2008 at 12:37 pm
The optimizer will process things differently on different hardware. Additionally it may select a different execution plan for the same query on the same hardware depending on the amount of resources that are available.
If at the time of one execution there is a considerable amount of free memory, it may choose a path that uses less CPU cycles and more memory, while at another time when the CPU is idle but memory is in short supply, it may choose a plan that is more CPU intensive and less memory intensive.
Since one is running in a VM and one is not, perhaps the additional over head of the VM is causing the difference. Also are you just running each query once, or are you doing it a few times? Are you clearing the cache first to eliminate the effects of the cache?
How substantial is the difference you are seeing?
July 29, 2008 at 1:02 pm
To my knowledge, the machines, databases, workloads, configurations are exactly similar. I took the backup from VM machine and restore it on to the other machine. (Wanted to compare performance of VM environment with a standard environment)
Grant what to you mean by
"All it takes is a checkpoint to run during your query to get a variance between servers in terms of execution time. "
Same execution plan has been generated on boht machines for the query with following stats.
CPU Read Write Duration
VM Machine 1109 16689 3 980
Non- VM 3828 28693 1 1287
Luke
The VM machine is giving better performance than the normal machine, i m running the query several times, no i m clearing the cache (how to do dat?)
July 29, 2008 at 1:12 pm
What do you mean you took a backup from the VM machine tot he other one? did you do a bare metal restore from the VM or did you just take a backup from the SQL database?
As to clearing the Procedure cache see Grants replies to your other thread...
http://www.sqlservercentral.com/Forums/Topic542948-360-1.aspx
July 29, 2008 at 1:18 pm
What I meant was, automated processes, such as a checkpoint, can pop up in the middle of a query and affect the overall performance (not by much, I'll grant you) but when you're comparing one system to another, it can make the difference.
As to your issue, you're comparing apples to hammers. The VM is running faster, but how much memory does it have compared to the physical box? How fast are the CPU's? How many other processes are running? You've run the query multiple times, does it involve manipulating the data in such a way that the indexes have changed from one server to the other? There are so many variations, it's hard to know precisely what the issue might be.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply