There are many reasons why we follow best practices. My own reasons have changed over the course of my career. Early in my career as a database developer and then later as a new DBA, I followed best practices because people who claimed to know more about SQL Server said to, and I assumed that their advice would be better than my own.

This of course led to me adopting the best practice of not following the advice of someone simply because they claimed to know more than others. I learned that you needed to evaluate the source that it was coming from. I don’t even take best practices from Microsoft itself as gospel. Having worked at Microsoft for many years, I can assure you that internally, we don’t all agree on certain practices. I have heard from people who set all of their OLTP SQL Servers to MAXDOP = 1 because an engineer in PSS (Microsoft’s Premier Support Services) told them this was the best practice for fixing CXPacket waits and they should always use this on an OLTP server. I’m not going to go into detail in this post on why this is a bad recommendation (read my post The Barking Dog Analogy if you want to know why I say this). My point is that it is not always easy to know who you can trust for best practices.

I’m not by any means trying to disparage PSS and its engineers. They have some truly brilliant guys working there, and I advise people to open a case with them quite frequently. I was just demonstrating that even normally reliable sources can distribute bad practices as good. At some point in my career, I came to the realization that for most reported best practices I could find posted on the internet, with just a little more research, I could find the exact opposite advice posted as a best practice on another website or blog. In addition to figuring out who I could trust for best practices, I started developing the skill of evaluating the best practice myself to determine if what they were saying agreed with what I could prove. In short, I learned the value of testing before implementing.

The skill of being able to verify a best practice is critical for DBAs. At some point, we all are faced with others recommending a bad best practice to management and management buying in to it. You either need to have already proven that the practice would be bad or be able to prove it now. A number of times I have won out over the bad practice by saying, “Let’s try it in test and see what happens.” This only works if you make sure that the proper scenarios are run in test so that it is accurately evaluated. Other times, I already had empirical data to back me up. If management doesn’t understand the technology, it’s very easy for them to buy in to a bad practice because they are relying on us to advise them of these things. I have found that it is very easy to get management on your side when it’s a case of the other side “read it on a blog somewhere” versus you “have real data showing that it would be bad.”

At some point, I started cultivating my own set of best practices. Ultimately, it’s a culmination of what I have learned from others, my own experiences, my own tests, and my understanding of how SQL operates internally. I have also learned to tailor my best practices to the audience. I was the SQL technical lead on an operations team at Microsoft and most of the operations engineers on the team were not DBAs. I spent a fair amount of time trying to tech them best practices. When talking to non-DBAs, I will give them a different set of best practices than i would to experienced DBAs. For example, I would tell a non-DBA to create one data file per CPU for tempdb so they can avoid the possibility of tempdb contention altogether. Conversely, I would tell an experienced DBA that they should start with 1 data file for every 2 or 4 CPUs for tempdb, monitor for tempdb contention, and adjust if needed. I also held monthly training sessions for non-DBAs where I tried to educate them on things like tempdb contention to try to elevate their SQL Server knowledge level.

Demystifying Database Administration Best Practices

Being able to develop and evaluate best practices is a critical skill for DBAs. That’s one reason that fellow Certified Master (MCM) Argenis Fernandez (blog|@DBArgenis) and I are doing a pre-con at SQLRally on May 9th called Demystifying Database Administration Best Practices. This training will cover more than simply enumerating best practices. We will show you how why they are best practices and show you how to evaluate best practices themselves.

More info:

Our pre-con: Demystifying Database Administration Best Practices
Pre-con schedule: PASS SQLRally Dallas 2012: Pre-Conference Sessions
SQLRally homepage: Join Us in Dallas for PASS SQLRally 2012