Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

New more powerful servers are slower than old ones? Expand / Collapse
Author
Message
Posted Tuesday, January 8, 2013 10:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 3:56 AM
Points: 128, Visits: 421
We have 2008R2 servers, We are testing performance on new hyper-v servers before we migrate from old 2005 phsyical servers and as no one is on the new server, and with newer cpus, more memory, better disks etc, we would expect better or at least equivelant speed, but our erp application feels much slower in testing, but I am having trouble to understand exactly where the problem is and how to solve it, I've tried checking lots of counters/waits etc.

I've now dropped down to a simple small query.
SELECT * from dbo.dimCustomer from adventureworksDW
It seems async_network_io is much higher but this is with running the simple query directly in ssms on the server (via rdp). (used http://sqlcat.codeplex.com/wikipage?title=ExtendedEventsWaitStats)

If I run this query many times over (the table is only 9MB), with no other users on the system, I know the data is cached.
On 'servers' I am rdp'd onto, I get values around this, but they do change a lot but no where near my desktop speed.
CPU time 219 ms, elapsed time 1387

On my desktop, I get
CPU 31ms elapsed time 507ms

Any ideas why a server would be much slower when no network involvement, no disk involvement afaik.

Thanks
Post #1404370
Posted Tuesday, January 8, 2013 11:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 9:58 AM
Points: 13,872, Visits: 9,600
My first guess would be the virtual machines aren't configured correctly for optimal SQL Server performance. If not done exactly right, it's pretty usual to get some performance degredation when you go virtual.

Another possibility is that the desktop machine and the old servers may have had the databases on local drives, while the new servers have them on a SAN or some such. Local bus is usually faster than a SAN, even if just due to latency.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1404402
Posted Tuesday, January 8, 2013 11:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:31 AM
Points: 12,962, Visits: 32,501
also, because of differences in the SQL engine's optimizer between versions, if you upgrade from 2005 to 2008R2 like you said, at a minimum you need to updating statistics, and i'd consider rebuilding the indexes , as that is a critical piece that must be part of your upgrade process.

you can search and there's lots of posts stating "new server is slower than old one", and that is often a root cause.

DECLARE @Exec VARCHAR(MAX)
SELECT @Exec = ''
SELECT @Exec = 'UPDATE STATISTICS ' + QUOTENAME(schema_name(schema_id))+ '.' + quotename(name) + ' WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) + @Exec FROM sys.tables ORDER BY name DESC

PRINT LEN(@Exec)

PRINT @Exec

EXEC(@Exec)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1404407
Posted Tuesday, January 8, 2013 4:00 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 3,113, Visits: 11,540
You should probably do some IO performance testing with SQLIO to verify that the IO performance of the VMs is better than the IO performance of the old machines.

Post #1404482
Posted Tuesday, January 8, 2013 5:14 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 18,064, Visits: 16,099
With these new servers, did you make sure the energy saving features were disabled in bios?

SQL Server does not play well with servers that have the Energy saving features enabled.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1404501
Posted Tuesday, January 8, 2013 11:16 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:16 AM
Points: 2,840, Visits: 3,983
What about the setting up maximum limit of Memory.... (I might be hitting the nail in dark holding with it other hand. )

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1404551
Posted Wednesday, January 9, 2013 2:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 3:56 AM
Points: 128, Visits: 421
Thanks for all the responses, I will try to answer/respond to them all.

in my small test to see a reproducable negative difference, I am doing a select * from a 9MB table, which will do a full clustered index scan so a table scan, so not sure how rebuilding statistics will help in this specific case. We have done update statistics on the erp database to no effect, but I will give that a shot, and if all the data is in cache, why would the i/o have that much of an impact if all data is in local RAM?

However I will find a few more machines to do an identical test with local/remote and virtual/physical.

In this specific example it is the adventureworksDW database so it is not a 2005 to 2008R2 issue, as the source is 2008R2.

We've run SQLIO some time ago, and the virtual servers drives are much faster than my local drive but I will run the tests again, in case something has changed.

The max sql memory is set, but the data is only 9MB, and I re-run the select * from dbo.dimcustomer many times to make sure it is fully in memory.

I will check with the network/server guys about the energy saving features.

All references I can find to network_io wait type issue do not seem to be relevant here, as I am running the query on the server in ssms and with cached data.

Thanks.
Post #1404606
Posted Monday, January 14, 2013 3:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:03 AM
Points: 2,900, Visits: 3,301
What workload are you trying to work out a performance profile for?

If it is only going to be a SELECT from a 9MB table then you are most of the way there. If it is to predict how your production system will work on the new servers then you are going down the wrong tack.

If you want to work out how your production workload will perform then you need to run part of your production workload. SQL Server has Replay capabilities, so you can capture a SQL Profiler trace of your production system and replay the workload on your new system. While both the capture and replay are running, also run a PAL trace to capture IO statistics. You can then compare raw run times between the capture and the replay to get an idea of comparative run times, and compare the PAL output to see where bottlenecks exist in your old and new systems. If you find a bottleneck in your new system you think you can fix, then do so and rerun the workload.

At some point you will get to a state where you cannot fix any further bottlenecks found by PAL. At this stage you can look at overall run times, response times, etc, and have meaningful data you can report to management about the new system performance. It is then up to the managers to decide if the new system is fit for use.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1406629
Posted Monday, January 14, 2013 3:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 3:56 AM
Points: 128, Visits: 421
I think this is fixed.
We have found that the CPU settings in the bios were set to balanced
Thanks SQLRNNR for the pointer.

A 'real' workload before changing the setting now takes just a 3rd of the time, we are doing more tests and my simple select * from 1 million rows takes between half and a third of the cpu ms time.

Thanks for all the replies.
Post #1406641
Posted Monday, January 14, 2013 8:54 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 18,064, Visits: 16,099
sotn (1/14/2013)
I think this is fixed.
We have found that the CPU settings in the bios were set to balanced
Thanks SQLRNNR for the pointer.

A 'real' workload before changing the setting now takes just a 3rd of the time, we are doing more tests and my simple select * from 1 million rows takes between half and a third of the cpu ms time.

Thanks for all the replies.


Good to hear.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1406780
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse