Using Memory for Performance Tuning in SQL Server by Brent Ozar Unlimited®

Everybody’s worried that adding memory is somehow cheating.

Says this article:

via Using Memory for Performance Tuning in SQL Server by Brent Ozar Unlimited®.

I can’t say I’ve ever heard that worry expressed in that way. I hear one group worried about the price of adding memory. I hear one group worrying that adding memory will just delay the onset of the problem and that as the data volume increases, there will come a time when you can’t chuck memory at it anymore and the Godzilla like table/query will eat your performance.

We had a classic example the last few days. Customer screaming for blood because his tool is unusable and everyone passes the buck down the line until it stops where the poor sucker has no one to pass it on to. The louder the screams the more it will spread and the more „the buck stops here“ points/people will get hit with the stuff flying off of the fan.

So, we do traces and Perfmons whilst a precursory look at the database reveals a few heap tables with a Gigabyte of data each that are getting hit pretty often. It doesn’t take much thinking to realise that you can throw memory at the problem to get the heaps into memory or you can get indexes added and if still necessary, add memory. Adding an index is not exactly rocket science but the developer had previously tried to bully the assembled crowd with loud assertations that with all their years of experience they have optimised their indexes until there’s nothing more to be won. At this point a gentle suggestion that a few indexes must have then gone missing scince a 1.5GB indexless heap table would be one of the most obvious signs of poor database design, allowed the poor fellow to save some face and to later admit that some indexes must have gone missing in the porting process from MySQL.

The fact that the porting from MySQL to MSSQL was done using a Microsoft migration tool and that there were ample, but unheeded, warnings to check that all design structures had been ported/migrated can, of course, be put down to a blustering answer of „well we can’t check everything can we?“. The bluster is of course true, I cannot expect them to check that the moon is not made of green cheese or where the first occurence of my telephone number within the decimal expression of PI happens to be, but from my position, I do believe that if someone has a problem with „my“ product then I do think it is my duty to check that I have my house in order before I try to shovel the blame on someone else…

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert