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