April 15, 2013 at 7:02 am
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 ?
April 15, 2013 at 7:05 am
have you updated the statisics?
April 15, 2013 at 7:10 am
No. let me just try updating statistics on all tables.
April 15, 2013 at 7:58 am
Updated statistics on all tables in the database and still not much improvement in performance. Any other suggestions ?
April 15, 2013 at 2:16 pm
excessive parallelism?
compare the query plans on the UAT and prod servers and go from there.
---------------------------------------------------------------------
April 15, 2013 at 2:28 pm
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
April 15, 2013 at 10:01 pm
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. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 15, 2013 at 11:41 pm
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
April 17, 2013 at 8:09 am
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.
April 17, 2013 at 11:19 am
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" 😉
April 17, 2013 at 3:42 pm
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
Change is inevitable... Change for the better is not.
April 18, 2013 at 2:21 pm
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
April 18, 2013 at 3:05 pm
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
April 18, 2013 at 3:14 pm
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
April 19, 2013 at 2:41 pm
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.
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.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply