asked
Physical vs virtual conundrum
We have been tinkering with a virtual to physical project for several months. The initial driver for the project was to extract the greatest performance possible from our infrastructure to coincide with a re-release of our bespoke Crm application. The existing Db is a virtualised (Esx 5.5) Ws2008 x64/Sql2008 Enterprise. We have built several versions now of a physical server both clustered and not clustered on the same hardware platform as the Esx host (Cisco Ucs B200 M2) with storage from the same Netapp Sas aggregate as the Vm, but have never been able get the same performance from the physical environment. The Vm was P2Veed about 5 years ago. At present the physical box is running 2012R2 datacenter/Sql 2014 Enterprise, but have seen exactly same results with 2008R2/Sql 2008R2 and Sql 2012. Storage i/o tests show us no real differences between the 2 servers getting to the Netapp. For performance measurement we have been using 3 queries (based on typical user interactions with the app) that we have running within a monitoring system. For all 3 queries the physical box performs much the poorer. Typically 50% or more in terms of time elapsed for the queries. One curiosity that has had me even more baffled during the last few months is a select * from a table that has ~1m rows in it. If I run the query from Ssms on the Vm it returns the rows in say 8secs. If I do the same from Ssms on the physical box it returns the rows in about 14secs. But if I connect to the physical box via Ssms on the Vm and run the query it too comes back in 8secs! How is it so? Really scratching my head particularly with the last point! Anyone ever seen anything similar? Cheers Damien.
... continuing * Have you rebuilt indexes on the physical instance? * Updated statistics with full scan on the physical instance? * Run DBCC UPDATEUSAGE() on the physical instance? * Are the SSMS connection settings the same on the physical instance SSMS and the virtual instance SSMS: tools -> options -> Query Execution -> SQL Server -> General, Advanced and ASCII tabs (I forget the query that shows this info) * Same number of CPUS and are the CPUs running at full speed on the physical server (check with CPUz) * are the blades in the same chassis? Share a backplane? For your select * query, I'd rule SSMS and network out as variables by doing the select * into a temptable on each server. Do it several times on each server and take the average time (after throwing out the fastest and slowest times) I would also run the queries from the same instance of SSMS. Perhaps on a 3rd machine that is not in the virtualization host farm so you don't get any virtual network shortcut advantage when querying the virtual.
50% slower is quite a bit. Here are some things I would probably look at. You've probably hit most of them, but maybe there's something here that will get you going you in the right direction. * Is the monitoring system that's running the queries the same version for each server? * Are the number of tempdb files/maxdop/min and max memory settings the same? * Same amount/clock speed RAM? Same number of memory slots filled? * Is tempdb in the same location on each server (either located with or separate from the data and log files)? * Data and log files are located in the same place on each server (either on the same drive or separate drives)? * No resource governor setup on either instance? * No memory pressure on the physical server? * Are the queries generating identical plans on each instance? continued...
@Grant and @Fatherjack have both provided some good information. Good stuff was also listed in the comments. But something that I have seen time and again cause this type of conundrum has yet to be mentioned. Check the power plan settings of the physical box. Also check the bios settings to ensure the "green" settings have all been disabled. I have seen the balanced power plan (including the power saving/green settings in bios) cause up to 67% performance degradation in many queries. You should have the power plan set to High performance. You should also ensure the bios is not set to enable the "green" / energy saving features. Here is an [article][1] that discusses that. [1]:
http://www.sqlsolutionsgroup.com/sql-servers-hidden-go-fast-button/
Hi all.... thanks for the responses. There was some very sensible Dba stuff in there but had got thru most of it over the last few months tinkering with this. As it happens CirqueDeSQLeil has hit the nail on the head. There's no real green features with the Ucs blade bios, but enabling the maxperf power setting in WS2012 sorted it immediately. I have had a couple of gun contractors in here looking at this and no one though to look at the green stuff. I cant believe it makes such an impact, and that the default setting is "balanced". There is no other workload on this box right now so the logic of how the power plan affects Sql so significantly still escapes me somewhat but very pleased that there is now a resolution. I did look at a non-prod Sql Vm a few minutes ago and the power setting seems to make no difference, perhaps masked by the hypervisor. Thanks CirqueDeSQLeil!
That happens probably a little more often than it should. To be fair, green machines were less of an issue >5 yrs ago. People (the hired guns) focus solely on queries happening in SQL Server and settings in SQL Server instead of the whole machine. And that is hardly any real fault of theirs. It is an easy thing to forget or overlook. And sometimes it just boils down to them not having enough experience. All of that said, I know a pretty good consultant (wink) should you need one in the future.
When you say you are running queries from SSMS are you making sure that SSMS isnt the cause of the query performance? Personally I would look to run the queries via some other means? At the very least are you using 'discard results after execution' option? How are you measuring the execution time - using SET STATISTICS TIME ? [Edit] It took a while but I have located a couple of posts on my blog about how SSMS can skew query execution results: [How to make sure you see the truth with Management Studio][1] [Don’t even believe SSMS when you think it’s telling the truth][2] [1]:
https://www.simple-talk.com/blogs/2011/02/23/how-to-make-sure-you-see-the-truth-with-management-studio/ [2]:
https://www.simple-talk.com/blogs/2011/02/28/dont-even-believe-ssms-when-you-think-its-telling-the-truth/
Instead of trying to run queries from SSMS and get a simple measure, automate the testing. Run the scripts from some third party product or script, PowerShell for example. Call them 10, 50, 1000 times, but do it all the exact same way from this script or location against each of the two servers. While doing this, use Extended Events to capture the query metrics (rpc_complete and sql_batch_complete events) from each server. Compare the results collected by Extended Events. You want everything to be apples to apples on your test runs. In addition to capturing the query metrics, I'd suggest capturing the wait statistics before and after you run the tests. You can use sys.dm_os_wait_stats to get the waits before and after you run the tests. That will tell you what is slowing down the access on each system which will help to determine where the bottlenecks are.
18 People are following this question.
