No, this is not a forthcoming erotic SQL novel, that's next year’s
big project :-) This piece has actually been brought about by a post on one of
the LinkedIn forums that I frequently visit.
Recently a post went up about best
practices for a new SQL Server. The article included a number of things we should do to configure and improve your SQL instance post install.
Let’s take an example question, how many TempDB files should I
have? Commonly we might see one these answers (btw, all of the answers above have
been taken from actual posts in reply to the question):
1 – Leave it at one, the system should look after itself.
2 – It should have one file per core.
3 – It depends.
Answer 1 is absolute rubbish. Answer 2 might be right in some cases
but the guideline is wrong. Answer 3, well it’s kind of right but not very helpful!
Clearly the conflicting advice out there can be very
confusing, especially for those new to SQL and the fact is that for many settings
within SQL there are no definitive answers or best settings that can just be
applied. Thanks to the varying mixture of advice out there many settings and actions get referred to as "grey areas", take parallelism, memory, maintenance, indexing to name just a few.
But are they really grey areas?
You see, we DBA’s are rather evidence based. Many of these grey
area settings in SQL are dependent rather simply on what is going on.
Take our TempDB question above. There is no single right answer. It isn’t 2,4,8 or even 32. The answer lies in evidence, are we seeing TempDB contention
or not? If we are, we need to add files. Have we added too much? Well, are we
seeing an increase in IO waits?
Ultimately guidelines serve as just that, a guide
to area’s that we should look into and not just to suggest a supposed magic number that should be used. It is the analysis and problem solving technique that needs to be taught because although there are grey area’s, all of the answers lie in black and
white evidence.
No comments:
Post a Comment