Do yourself a favor… Trust No One

Cue spooky music… I used to love the X-Files, where one of the catch-phrases was Trust No One.

Their other major catch-phrase was The Truth Is Out There.

Yes, it is – but you're likely going to come up against all kinds of obstacles to finding it, just like Agent Mulder did.

Now, searching for true answers to your SQL Server conundrums is unlikely to make you the assassination target of shadowy government agencies or lead to you or your family being abducted by aliens – although the list of undocumented trace flags is a very closely guarded secret…

The problem is that there's so much crap out there on the internet. And I'm not being dramatic here – there really is some *utter rubbish* being written about SQL Server that only serves to confuse and mislead people.

But how do you know who to trust?

What about Microsoft? You'd think that Microsoft itself is trustworthy all the time, right? Wrong.

Case in point: the Microsoft whitepaper that was published in May about performing SQL Server maintenance for SharePoint 2010. It was full of all kinds of nonsense such as:

  • Run CHECKDB, and if it shows problems, run repair to fix them (without any mention whatsoever of backups or the problems repair can cause).
  • After shrinking a database, grow it again and then rebuild all the indexes.

I couldn't believe it when it was published! I offered to rewrite it and it's since been republished with correct info in it.

Anyone inside Microsoft can publish a blog on MSDN or a whitepaper without any kind of peer review by the SQL team and then it becomes the Microsoft official word on the subject. This has caused no end of problems for DBAs, consultants and others who have to work really hard to correct ingrained misinformation from well-meaning Microsoft people.

Don't get me wrong, Microsoft is great – but being The Source itself, it's deemed inherently trustworthy, so how do you tell whether the blog or whitepaper you're reading is written by someone qualified to disseminate information/guidance to a wide audience? Stick to those whitepapers and blogs published by (or through) the SQL Server team, the SQL CAT team, and to the information in Books Online (which is improving in leaps and bounds). Take anything else with a pinch of salt. Even from the excellent CAT team, for instance, some of their advice is based on them dealing with very large enterprise customers running SQL Server on honking big hardware – and doesn't translate well down to smaller SQL Servers with lower workloads.

Also consider the publication date of a whitepaper/blog post, and which version of SQL Server the whitepaper/blog post is referencing – behavior may have changed drastically since then.

What about 3rd-party tools? You have to be very careful with these too. Look at the problems you can have if you create all the indexes DTA advises. Read my rant against one of the tools that was recommending dropping indexes because it wasn't considering INCLUDEd columns in the index definitions. Tools and their recommendations are only as good as the people who write them – if *they* are incorrect in their knowledge or assumptions, so will the tool be.

What about blogs? There are a *ton* of people blogging about SQL Server, across all levels of knowledge and experience. You have to be careful. Everyone starts with zero knowledge and experience about SQL Server, and no-one is an expert in all areas of SQL Server. For instance, I only blog about the areas I consider myself an expert in – and I only ever blog about things I'm 100% certain of, or I blog about how I'm finding things out, like in my benchmarking series. But nobody's perfect – I've made mistakes in the past and posted corrections, as have many people I consider experts. Look for blogs that are referenced a lot by people you've heard of in the community, and then still check what you're reading if you can. There are lots of people blogging and writing articles that contain poor advice, perpetuate myths and misconceptions, and are just plain wrong.

Enthusiasm about SQL Server is great, but accuracy of the information being presented is paramount.

What about forums? These are the most dangerous areas of the Internet for getting poor or incorrect advice about SQL Server. Even on the most reputable of forum sites (e.g. MSDN or SQL Server Central), anyone can join and anyone can reply to forum threads. And it doesn't matter how many experience points a person has either – I know people with lots of points but often post misleading or incorrect replies. But saying that, there are many, many people who give good advice – just take it with a pinch of salt again.

Here's a classic example from the MSDN forums back in 2008. I just *love* the retort about autoshrink that "Nevertheless all high-priced SQL server expert consultants warn against using it.'

What about twitter? Very interestingly I find that the advice on twitter on the #sqlhelp alias is some of the best I've seen – a sort of collaborative, real-time debugging forum. I'm not sure why that is – maybe because there are so many well-informed people in the SQL twitter community.

What about training classes? This is a hard one for me to comment on as I don't want this to seem a plug for our classes. Anyone can set themselves up as a trainer and claim to be a SQL Server expert. I've heard horror stories of trainers reading slides, having demos that don't work and they don't know what to do, parroting marketing info or Books Online, and class attendees having to correct inaccuracies in material. I've also heard of instructors that cannot answer anything but the most basic questions about the material being taught. Before signing up for a class, do some research about the company and the instructors – look for good and bad reviews. Looks for their blogs and how well they're known in the community. There's good stuff out there, and there's also poor quality stuff too.

Summary

Here are some things to look out for that should make you wary of the advice:

  • Someone saying that "this is *always* what you should do". E.g. if you have CXPACKET waits, set MAXDOP to 1. No!
  • Someone citing a configuration as a best practice because it worked for them (e.g. using one data file per processor core in all filegroups for user databases)
  • Someone referencing commands or syntax that have been removed from the product (e.g. advice about using DBCC REBUILD_LOG to fix a suspect database on 2005 or 2008)
  • Anyone recommending auto-shrink :-)

One of the worst things I'm seeing recently is people referencing a Page Life Expectancy value of 300 as being a threshold. That number was published in a whitepaper 6 years ago by Microsoft and is a nonsensical number today. IMHO if you see someone recommending this, they're parroting information from the old whitepaper and they don't really know what they're talking about and what PLE is actually saying. Ask them to explain *why* 300 is the threshold they recommend… sometimes expertise is only skin-deep. (Here's my explanation of PLE over on our SQL Server Magazine blog.)

Ok – so Trust No One is a bit too restrictive – but be careful who you trust. Try things out on a test system before doing them in production. Get validation of the info you've read from a few other sources if possible. Don't just blindly follow advice you get on forums – especially for critical operations like disaster recovery.

I'm sure some people will take this post as some sort of self-aggrandizement – that's not my aim in any way.

I'm just fed up of seeing people posting garbage and of having to defend the truth in the face of misleading misinformation.

Remember – The Truth Is Out There. Just be careful who you trust.

35 thoughts on “Do yourself a favor… Trust No One

  1. I’ve met a few DBAs who wanted autoshrink on, until they learned what it did.

    I suspect it’s got to do with MS Access where Compact and Repair is frequently needed, and people who transition from Access to SQL assume that autoshrink === automatic Compact and Repair

  2. If I was using something that ‘all high-priced SQL Server expert consultants’ recommend against, I’d be asking myself ‘what do they know that I don’t?’.

    If it was a case that one expert was for it and another against, then maybe there’s reason to use it, but if all recommend against… Hmmmm…

  3. I would beleive 70% of people who *call* themselves DBAs recommend auto-shrink. After all, how many times have we seen forum posts by "DBAs" whose transaction log has filled up the drive because they never backed it up? It happens way more often than it should.

  4. Love this post, although this type of advice always raises the question – can you trust someone who advises you to trust no-one? :)

    Keep up the great work!

  5. So why is it that the developers, contractors, and managers seem to be able to find and lock into the "bad" stuff and they are so resistant to the on staff DBA tryihng to fix it. I am following a contract C# developer who built the "standard maintenance plans" that Rebuild Indexes, then Shrink the database, then Update Statistics with defualt sample size. The various App Managers are unwilling to agree to changes to these atrocities. After all the white paper Paul referenced says that’s a best practice. :0

  6. Great post..i’ve had some experiences with Microsoft itself in this regard. We had some tempdb issues after we set up one tempdb file per core on a quad core box (totally 16 files!) per the MS white paper on this. After we had severe tempdb performance issues we looked into Paul’s posts on SSCcom and took it down to 8 files and we did get some relief. A few days later i called MS on another issue, they found the # of tempdb files to be ‘too low’ and referred me back to the same white paper again! No amount of MVP posts or material could make them change their mind. Luckily my boss trusted what i knew and the people i refer to and let me keep it at 8. But lot of companies go strictly by Ms white papers, that is the core of the issue. The issue with PLE is also similar. If the ‘source’ decides to fix their material, mark outdated as clearly outdated and so on we will see far less people making misguided decisions and in turn misguiding others. Thank you Paul, for policing the community and getting people in line.

  7. Thanks for giving an "official opinion" on this important topic.
    What do you think about books? (except "it depends")

    cheers from 2 rows in front of you, sitting in your class :-)

  8. @8/11/2011 2:45:53 PM

    I think some of that behavior is due to onerous change control procedures that make it easy to lock into a horrible idea early and very difficult to make changes later. I’ve had clients that effectively preferred to let their business go down rather than make minor, non-breaking changes to improve uptime.

  9. And bad advice in MS KB articles and white papers is the worst to deal with… There’s no way I can convince most people that I’m right about something that MS’s documentation is wrong about.

  10. From a security standpoint this becomes more than just a nuisance/problem/heart-ache. It can obviously become fatal. And, of course, the problem is that while SQL Server ships as a fantastically secure platform (all things considered) the primary vector of attack for hackers remains SQL Injection – and there’s really just soooo much bad advice out there on how to protect against it.

    So yeah, I can definitely identify this with ‘rant’ ;)

  11. @Andreas :-)

    Books are the same – they’re not immune from mistakes. Heck – even the internals book that I wrote part of has incorrect information in it (in other chapters! :-)

  12. Another good post, Paul. I think some DBAs are pressured by their SAN Admins or Operations people into regularly manually shrinking or auto-shrinking data files to free up disk space on their precious SANs, thereby causing all sorts of fragmentation for SQL Server..

  13. @Greg It’s unfortunately based on number of posts rather than how relevant/useful the responses are. SSC is the same. Things like ServerFault are much better in that respect.

  14. I must only know the other 30% of DBA’s because i’ve never known any wanting to have autoshrink enabled. I’m glad you’ve highlighted the fact that not everyone that posts on forums, tweets or writes articles are 100% correct all of the time as i’ve spoken to a lot of DBAs (quite recently) who quote from forums/blogs (and microsoft) where the information is sometimes incorrect but because it was on a reputable site then they took it as gospel!

  15. Especially in larger corporations ($1B+) that don’t value/trust their "high-priced DBA" a whitepaper from MS that is incorrect can be disastrous. I recently left a contract gig at one where I had to find references to back up my suggestions. They hadn’t had a decent SQL Server DBA there in forever and I was trying to put out fires in a hurry. But the CIO would not trust my expertise on anything…autoshrink, AWE, index maintenance, anything. Several times I used you, Paul, as my expert reference.

    It was supposed to be contract-to-hire and they tried to low-ball me after I put out all their fires. So I declined their offer and left. They ended up getting an H1-B "DBA" for about 1/3 of what I was asking. One week after I left they were calling me again because the new guy couldn’t do the job. LOL I don’t believe they’ll ever learn and I have no desire to spend any time there again.

  16. Great post. As an accidental DBA I try and only follow pointers/hints/instructions from people that are active on #sqlhelp, or people that have 24hrs of PASS presentations or the alike. However I still don’t take that advice with blind faith. I still try and learn the meaning.

    You are correct in saying that there is way to much misinformation out there. It is even harder for someone just getting into SQL sever administration.

  17. I was getting lots of CXpackets. Once I set MAXDOP to 1, they when away on my OLTP system. Did I screw up?

  18. This is a timely advice, especially for those starting out in their career, not just in SQL Server. Which is why we are always advised to go back to the fundamental principles and the basics. Weighing in information out there against the fundamentals and basic principles will guide us on whether or not to trust the information we find. I hope that experts like you, Paul, will continue to pass along their legacy to the younger generations.

    P.S. There’s a reason I still use the NNTP Bridge to access the forums. I don’t like to see those numbers :-)

  19. The bad advice is out there… I was going to give some other examples of craziness with transaction logs, but unfortunately you only need to do a search to find some on the web.

    In fairness to the original poster, it is from 2008 and he does seem to have taken your point… eventually

    The problems really occur when bad advice gets repeated unchecked which is why we are lucky that the SQL server community is so active. Thank you Paul.

    The forums have recently gone thru an overhaul and the scoring system has also changed, but it is obviously hard to come up with any system that is able to determine the quality of answer and difficulty of the question. (Without peer review). Ability to mark down answers would definitely be nice!
    It is good that you can look at that post and see Pauls’ MVP award though, and therefore give his answer alot more weight. As for the points system, I would remove it from public view and have it just as a personal statistics but that is just me.

  20. In my opinion, there are a lot of lazy guys out there.
    They read something from somewhere and just trust it without a second opinion or trying to think about the consequences.

    I like the movie “The Negotiator”, Samuel L. Jackson – Kevin Spacey, and others great actors, when Kevin Spacey said: “I try to read all of the books about the subject and get all of the facts, and decide for myself what really happen”

    Paul,

    Could you tell us why “auto-shrink” was not removed or why it was kept?

  21. Great post; I guess you can’t drive these points home enough.

    I could add one more item to the generic list of things to watch out for: The "in-flight magazine syndrome" whereby anything new and not yet widely understood gets enthusiastiastic blogging coverage, turning into an apparent silver bullet instead of just another weapon in the arsenal, to be used when appropriate.

    Related to this, it is unfortunately also human nature to enojy showing off knowledge and magic incantations that one’s peers aren’t familiar with. Again, this can end up in people suggesting obscure solutions to run-of-the-mill problems.

  22. Nice Post Paul. I was pissed off when Amit mentioned about the whitepaper on Sharepoint. That aside, you can safely trust SQL Support Team blogs as there is 2 member review for every post to validate content. Heck, if you want to trust anyone in MS, it should be CSS. One bad advice and we are the one’s taking the call due to the someone’s "expert advice". I agree 100% about forums, it seems to be the place for showing off with bad advice, twitter might eventually get there :)

  23. If you want TV reference I would have CSIs "Follow the evidence". That said, it should not an excuse for only being re-active, evidence can be gained from testing and monitoring. If no-one has claimed it, I want the dabs on the pro-activity:re-activity ratio as a measurement of DBA-ability!! There may be some caveats to this equation, so may be I should throw in a general constant (or two) before revising the equation… that sounds familiar ???

  24. Good post! I find the border between "I *know* this" and "I’m *pretty sure* I know this" to be very thin sometimes.

    When I blog I definitely prefer to blog about stuff I’m positive is correct – sometimes however, I risk my reputation writing about stuff I don’t *know*. It can be about something undocumented, which I can only guess and theorize about, or it can be about a topic I think I have a correct opinion about, but may later be proven wrong.

    Likewise, I’m reluctant to answer a question on #sqlhelp unless I’m totally positive I know the correct answer. I’ve found myself looking at an unanswered question many times, not feeling like giving my 2c’s as I’m unsure if I’m correct. That feeling of replying and then being corrected, yuck, it hurts.

    I’m not sure what my point is, other than it being difficult. You’ve got to cross the border now and then to progress, but you should absolutely not be stern unless you *know* you’re right.

    If you write about something you’re pretty sure about, but not positive, make a note of it! When I had a short stint as a cashier in a wood furniture shop, I made a big case out of immediately notifying customers if I didn’t know the answer to their question. I’d give any supporting answers/information to their questions, and delegate the remainder to the more experienced staff, provided the customer needed more information.

  25. Hi Paul…. Great advice, as usual! I hope that you’re reporting the errors you find in the "book that you contributed to". There is an online site for all errors and corrections.

    There have been many times that I have been asked to support my recommendations by showing the client "something from Microsoft" that says the same thing. They won’t believe it unless Microsoft says it. Occasionally I’ve been able to get them past this requirement but showing them things that Microsoft says that are obviously untrue, or if not that, at least show them two opposing views, both published by Microsoft, just to convince them that "something from Microsoft" is not always the final word.

    I think it is fine for DBAs to turn on autoshrink.
    As long as _I_ am the high-priced consultants that they call to come fix things afterward. :-)

    I once had a gig that they brought me in for a whole week, and their reported problem was "periodic slowdowns", like about every 30 minutes. The first morning, after our big intro meeting and overview of the system and application, I sat down at the DBA’s desk and looked at all the configuration options, and there was autoshrink set to on. We turned it off, and they had no more of the slowdowns they called me in about. There were lots of little things we could improve on also, but the big problem was fixed within 15 minutes of the time I first got online. They paid me for a whole week’s work. And I have cousins in the city where the job was whom I hadn’t seen in years, so it was a great week!

    Thanks for all you do!
    ~Kalen

  26. Talking about points on forums (SQL Server Central for example)… It’s quite easy to check if user collected the points by simply replying: "Yes! I absolutely agree!" to every possible post.

  27. With traditional published media, specifically referring to academia, it’s sacrilegious not to site your references, be it works from other authors or personal experience (i.e. lab tests, observations, etc.). In most cases not doing so results in not getting published yourself. In this vein, I find that when I read blog entries or heed advice from forums, I tend to gravitate toward those folks that either reference their material or are often referenced themselves. And to append to your point on whitepapers, vendor-sponsored software comparison "studies" (i.e. anything from a software vendor comparing their S/W to their competitors) should be immediately disregarded, as they are nothing but poorly written sales pitches masked as objective technical articles.

  28. I’ve certainly experienced both types of trainers over the years. Doing my SQL 2000 Implementation course I was lucky enough to have a fantastic trainer (fortunate since as a SysAdmin that was the toughest module of the four for me), who knew the course content back to front, had clearly actually DONE all those tasks out in the real world, and knew his stuff beyond what was discussed in the syllabus. Lots of "well for the exam the answer is x, but in the real world…" and "well that’s a bit beyond the scope of this course but if everyone’s interested we can go into that…". I might have felt brain fried at the end of each day, but I learnt SO much from that guy that week.

    On the flip side, doing a SQL 2000 to 2005 upgrade course we had a guy who read entirely from the book and slides. He’d clearly never touched SQL in anger, and at one point had myself and a few others having to explain to him that what he was saying was wrong, when he told us that there is NEVER a reason to use SQL authentication. Yep, for the exams that might be right, but in the real world that doesn’t always hold true.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.