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 ««12

No performance gain on queries on different servers with considerable hardware change Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 3:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
Markus (4/17/2013)
I agree. It goes to show you that your hardware is not the bottleneck. Sometimes it is just simply the I/O can only go as fast as it can go. If you want to see if there is any way you can improve the runtime you need to look at the SQL statements and see if there are any tuning indexes that can help the process run any faster. Throwing hardware at an issue sometimes is just not the answer.


I agree... indexes can help a lot. Sometimes, though, they actually make things worse depending on what you're doing.

But biggest performance and most significant gains I've seen come from redacting or maybe even rewritting code because a lot of the code cannot now nor ever will be able to use an index.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1443515
Posted Thursday, April 18, 2013 2:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
GilaMonster (4/15/2013)
To expand on that, I've seen a database's performance dramatically decrease after an upgrade from a single processor server to an 8-core with lots more memory. The code was riddled with cursors and implicit conversions and had minimal useful indexes. Running on a single core, the multiple sessions couldn't interfere with each other too badly, on an 8-core on the other hand they very much could, the lock waits were through the roof.

Was kinda fun because I'd been advising tuning the system before upgrading for months and all the managers 'knew better' and wanted to upgrade the hardware first because it would be a 'quick win'.
Yeah, not so much.


Let me guess - those same managers "solved" the performance issue on the new server by changing the server configuration to set max degree of parallelism to 1?


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1444138
Posted Thursday, April 18, 2013 3:05 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 40,438, Visits: 36,894
wolfkillj (4/18/2013)
GilaMonster (4/15/2013)
To expand on that, I've seen a database's performance dramatically decrease after an upgrade from a single processor server to an 8-core with lots more memory. The code was riddled with cursors and implicit conversions and had minimal useful indexes. Running on a single core, the multiple sessions couldn't interfere with each other too badly, on an 8-core on the other hand they very much could, the lock waits were through the roof.

Was kinda fun because I'd been advising tuning the system before upgrading for months and all the managers 'knew better' and wanted to upgrade the hardware first because it would be a 'quick win'.
Yeah, not so much.


Let me guess - those same managers "solved" the performance issue on the new server by changing the server configuration to set max degree of parallelism to 1?


No, I spent the next couple months advising on code changes, fighting with a developer who knew SQL better than I did (the one who wrote all the cursors) and generally getting a week of tuning work done in 2 months.

Thinking back, I may have forgotten to suggest the maxdop setting to that exceedingly skilled developer.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1444156
Posted Thursday, April 18, 2013 3:14 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
GilaMonster (4/18/2013)


. . . getting a week of tuning work done in 2 months. . . .


Hopefully, you were billing them by the hour!


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1444164
Posted Friday, April 19, 2013 2:41 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, October 20, 2014 9:30 AM
Points: 132, Visits: 588
It looks like the standard edition only supports up to 4 processors so going to a 32 proc machine probably doesn't make a difference.
MSDN

Were there upgrades in the storage configuration? You should do some analysis to figure out where the bottleneck is in the query. If the bottleneck was in the disk, then upgrading the the server alone would not make a big impact on your query performance.
Post #1444611
Posted Saturday, April 20, 2013 1:10 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:23 AM
Points: 5,886, Visits: 13,047
richykong (4/19/2013)
It looks like the standard edition only supports up to 4 processors so going to a 32 proc machine probably doesn't make a difference.
MSDN



the OP doesn't say how the 32 processors are made up (how many sockets and cores etc) but thats a very good point.


---------------------------------------------------------------------

Post #1444738
Posted Sunday, April 21, 2013 2:10 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 @ 1:30 AM
Points: 3,084, Visits: 3,195
Hi,
Interesting.

It may be your log file? Is it on same or different RAID and on what type? It's recommended to be on separate and faster disks.
It may be your tempdb? It also should reside on faster disks.
It may be the disks. Are the allocation units sizes same?
It may be the server settings?
You can make a check for these stuff.

Regards,
Igormi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1444819
Posted Monday, April 22, 2013 10:10 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 4,439, Visits: 6,346
Sanz (4/15/2013)
I have a dedicated Server for SQL Server. SQL Server runs on SQL Server 2008 Standard edition with SP2 applied. The machine is a 64 bit windows machine with 32 processors and 48 GB Ram.

I have moved a database from my existing UAT server to the new server(There is a drastic change in the server hardware) but can't find increase in performance when I run the same queries. What could be the reason ? Any suggestion on changes to be made ?


You forgot the MOST important piece of the performance puzzle: What is the IO configuration of each machine??


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1445264
Posted Tuesday, April 23, 2013 8:21 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Saturday, November 22, 2014 9:16 AM
Points: 184, Visits: 863
Thanks guys for the very valuable inputs.

Checked Memory, CPU, I/O etc.. and finally came to the conclusion that there is no issue with the hardware and the problem is in the query.


Sanz
Post #1445460
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse