SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


New more powerful servers are slower than old ones?


New more powerful servers are slower than old ones?

Author
Message
sotn
sotn
Mr or Mrs. 500
Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)

Group: General Forum Members
Points: 524 Visits: 469
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
GSquared
GSquared
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54567 Visits: 9730
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
Lowell
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67625 Visits: 40894
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
Michael Valentine Jones
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13713 Visits: 11848
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
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63643 Visits: 18570
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

Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12530 Visits: 4077
What about the setting up maximum limit of Memory.... (I might be hitting the nail in dark holding with it other hand. :-D )

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
sotn
sotn
Mr or Mrs. 500
Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)

Group: General Forum Members
Points: 524 Visits: 469
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
EdVassie
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13173 Visits: 3893
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
sotn
Mr or Mrs. 500
Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)

Group: General Forum Members
Points: 524 Visits: 469
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
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63643 Visits: 18570
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search