October 7, 2009 at 7:30 am
Hello everyone.
I could use some help with the following issue:
- server 1: physical box with 2x quad core CPUs and 8GB RAM, W2k3 R2 x86 with SQL Server 2005
- server 2: virtual machine with 4x vCPUs, 8GB vRAM, W2k8 R2 Web Edition x64 with SQL Server 2008 Web Edition
Both servers host the same DB (one is live, the second was restored from backup for testing purposes), which is roughly 3GB in size.
Customer runs complex query, which takes approx. 5s on server1 while it utilizes all 8 cores at 70-100%. The same query executed on server2 takes roughly 11s, while utilizing only 1 vCPU at 90-100% with the other vCPUs being idle. Sometimes the query on server 2 spreads load between 2 vCPUs, but overall utilisation is always no more than 25% (ie. 1 vCPU) and the load is not concurrent, but switching between those two vCPUs.
Changing number of vCPUs has no impact on the performance, the time is always roughly the same. Fastest result was with single vCPU, which makes me believe that for some reason the query cannot utilize multiple cores/cpus. Changing CPU/IO affinity on both the SQL server and/or the virtual machine has virtually no impact on the performance.
Can anyone help me identify (and possibly solve) the issue?
Notes: the query generates some disk load, but both well below storage capacity, the virtual machine is the only VM running on the host.
October 7, 2009 at 7:35 am
If you can post the execution plan for the query from both servers, that should highlight any differences.
Bear in mind that virtual machines will run slower than a physical box if they are same spec, even if the virtual is running on wk28 and using hyper-v.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 7, 2009 at 7:47 am
The problem is, because the systems are radically different, as well as different versions of SQL Server, you're comparing apples to cannonballs. Best advice is what the post above says, start tuning the query in place, not worrying about the fact that it runs differently on the new version of SQL (and the all new system config).
"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
October 7, 2009 at 8:48 am
Well, that might be a problem, since I'm not a DBA, I'm a sysadmin with not so deep knowledge of SQL.
I'm aware of the fact, that I'm comparing apples to cannonballs as you say, but apples to apples is impossible as the goal is to migrate the server from physical to virtual. The virtual host specs supercede (or are roughly on par with) that of the physical box except for the fact that the VM has assigned 4 vCPUs instead of 8 in the physical machine.
I have no knowledge of the DB content, functions etc, that's the client's business.
Unfortunately they refuse to optimize the database or their web app based on it, their only claim being that they want the same performance from from the virtual box as the physical has.
Judging by your answers, I guess there's no simple way to tell why the query puts the load on all cpus on the physical box and only one on the virtual? Or is there?
October 7, 2009 at 8:56 am
Rambler (10/7/2009)
Well, that might be a problem, since I'm not a DBA, I'm a sysadmin with not so deep knowledge of SQL.I'm aware of the fact, that I'm comparing apples to cannonballs as you say, but apples to apples is impossible as the goal is to migrate the server from physical to virtual. The virtual host specs supercede (or are roughly on par with) that of the physical box except for the fact that the VM has assigned 4 vCPUs instead of 8 in the physical machine.
I have no knowledge of the DB content, functions etc, that's the client's business.
Unfortunately they refuse to optimize the database or their web app based on it, their only claim being that they want the same performance from from the virtual box as the physical has.
Judging by your answers, I guess there's no simple way to tell why the query puts the load on all cpus on the physical box and only one on the virtual? Or is there?
You can look at the execution plan to see if you're getting parallelism on one server but not the other. But without parallel execution, the query is only ever using one processor.
"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
October 8, 2009 at 12:51 am
Is there an easy way to find that from the execution plan? I've looked at the ex. plan, but it's too complex and I'm not sure what exactly to look for.
October 8, 2009 at 5:57 am
Rambler (10/8/2009)
Is there an easy way to find that from the execution plan? I've looked at the ex. plan, but it's too complex and I'm not sure what exactly to look for.
Yes, execution plans can be difficult to interpret. You're looking for little yellow icons on top of the other icons. These will look kind of like little tridents. You're also likely to see operators called Distribute Streams (follow the link for picture) and Gather Streams.
I'd look for this on the machine that's running well and has multiple processors available. It might help explain why it's running faster on that machine. Parallelism is not automatic. It's based on a bunch of factors including the estimated cost of the execution plan, the cost threshold for parallelism set for the instance, the max degree of parallelism for the instance, the load on the CPU's on the server... But if you're getting it one server and not the other, it might help explain the difference in performance. But really, I'm kind of grasping at straws here.
If you like, you could post the execution plan from both servers. That would be helpful.
"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
October 8, 2009 at 6:00 am
Oh, and I'm not sure this was suggested, have you updated the stats on the new server? If so, did you use the FULL SCAN option? If not, I'd try that. It won't hurt anything.
"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
October 9, 2009 at 5:00 am
Grant, thanks for the responses.
We did further testing and the result was surprising and confusing.
Originally we thought the difference is caused by different OS (W2k3 vs W2k8), architecture (x86 vs x64) and SQL Server versions (2005 vs 2008).
But further testing revealed that when we installed W2k8 x64 with SQL server 2008 on a physical machine of similar spec as the other two, then the query would finish in 1.8s.
When CPU afinity was changed to use only 4 out of 8 cores, it behaved exactly like the virtual machine does, ie. execution slows down to 5+ seconds and only 1 core is utilized.
We plan to repeat the test for the virtual machine (with 8 vCPUs - current licensing doesn't allow us to use more than 4 vCPUs for a VM).
Is it possible (well it seems to be the case based on our testing), that SQL Server behaves differently based on number of CPUs/cores. Up to 4 cores, the query is always running on single core, with more than 4 cores, all of them are utilized.
Edit: in the SQL server feature comparison, it states that Wev edition can use maximum of 4 CPUs (Web workloads only). What exactly does that mean? Does it refer to CPUs or cores?
October 9, 2009 at 6:17 am
Hmmm... Is the cost threshold for parallelism the same on all the servers?
"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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply