Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


No performance gain on queries on different servers with considerable hardware change


No performance gain on queries on different servers with considerable hardware change

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45116 Visits: 39917
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 2582
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44389
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, MVP, M.Sc (Comp Sci)
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


wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 2582
GilaMonster (4/18/2013)


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


Hehe Hopefully, you were billing them by the hour!

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
richykong
richykong
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 621
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.
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6328 Visits: 13687
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.

---------------------------------------------------------------------
Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4168 Visits: 4849
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
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5963 Visits: 8312
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
Sanz
Sanz
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 957
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
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