New more powerful servers are slower than old ones?

Question

New more powerful servers are slower than old ones?

sotn

SSCrazy

Points: 2672
More actions
January 8, 2013 at 10:24 am

#269073

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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[/url]
Learn Extended Events

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

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;-)

sotn SSCrazy Points: 2672 More actions · Answer 6

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.

EdVassie SSC Guru Points: 60476 More actions · Answer 7

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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

sotn SSCrazy Points: 2672 More actions · Answer 8

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

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[/url]
Learn Extended Events