SQL Clone
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
Sanz
Sanz
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1205 Visits: 1002
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 ?

Sanz
adb2303
adb2303
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3800 Visits: 3265
have you updated the statisics?
Sanz
Sanz
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1205 Visits: 1002
No. let me just try updating statistics on all tables.

Sanz
Sanz
Sanz
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1205 Visits: 1002
Updated statistics on all tables in the database and still not much improvement in performance. Any other suggestions ?

Sanz
george sibbald
george sibbald
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43970 Visits: 13705
excessive parallelism?

compare the query plans on the UAT and prod servers and go from there.

---------------------------------------------------------------------
GilaMonster
GilaMonster
SSC Guru
SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)

Group: General Forum Members
Points: 411379 Visits: 47112
Could simply be that the hardware wasn't the bottleneck in the query performance.

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


Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)

Group: General Forum Members
Points: 377040 Visits: 42913
I've been through this a couple of times now and I agree with Gail. It boils down to which car will run better with sugar in it's gas tank and sand in the air intake? An old Volkswagon Beetle or a Maserati? ;-)

Of course, the answer is "neither" because performance is in the code... or not. :-P

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC Guru
SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)SSC Guru (411K reputation)

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

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


Summer90
Summer90
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: 12801 Visits: 3899
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.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)SSC Guru (102K reputation)

Group: General Forum Members
Points: 102392 Visits: 18004
Markus (4/17/2013)
Throwing hardware at an issue sometimes is just not the answer.

It's something you see time and time again and you're right it's a futile\costly exercise.

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
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