T-SQL Tuesday #11: Misconceptions about…. EVERYTHING!!

This month’s T-SQL Tuesday is being run by fellow-MVP Sankar Reddy (blog|twitter) and the topic is about Misconceptions in SQL Server.

(Check out the 60-page PDF with all the myths and misconceptions blog posts collected together: CommonSQLServerMyths.pdf)

Back in April I spent an entire month doing a DBA-Myth-A-Day series once a day which was all about misconceptions. So, rather than repeating some of what I said then, or spilling the beans on some more myths I’ll be discussing at PASS and SQL Connections in November, this post will be an uber-list of all the misconceptions I debunked during April – and, boy, there are a lot of them! I keep hearing these myths over and over, from all kinds of people, including SQL MVPs, vendors, and consultants.

These posts explain in detail why the misconception is a misconception and in many cases use a script to prove it as well.

• A SQL Server DBA myth a day: (30/30) backup myths
  • This post debunks my top 30(!) myths around backups.
• A SQL Server DBA myth a day: (29/30) fixing heap fragmentation
  • This post explains why dropping and recreating a clustered index is not a good solution for removing heap fragmentation.
• A SQL Server DBA myth a day: (28/30) BULK_LOGGED recovery model
  • This post debunks myths around bulk logging for DML operations, how BULK_LOGGED can affect your DR plan, and whether BULK_LOGGED affects log backup size.
• A SQL Server DBA myth a day: (27/30) use BACKUP WITH CHECKSUM to replace DBCC CHECKDB
  • This post explains why you can’t avoid running proper consistency checks.
• A SQL Server DBA myth a day: (26/30) nested transactions are real
  • This post explains how nested transactions are really a SQL Server dev team joke on all of us.
• A SQL Server DBA myth a day: (25/30) fill factor
  • This post explains when fill factor is adhered to, the difference between 0 and 100, and why it only affects the leaf-level of an index.
• A SQL Server DBA myth a day: (24/30) twenty six restore myths
  • This post debunks my top 26(!) myths around restores.
• A SQL Server DBA myth a day: (23/30) lock escalation
  • This post explains how lock escalation does NOT go row-to-page and then page-to-table, ever.
• A SQL Server DBA myth a day: (22/30) resource governor allows IO governing
  • Just as it says – hopefully this will appear in SQL11.
• A SQL Server DBA myth a day: (21/30) corruption can be fixed by restarting SQL Server
  • This post explains why the vast majority of the time this isn’t true.
• A SQL Server DBA myth a day: (20/30) restarting a log backup chain requires a full database backup
  • This post explains that a differential backup will work just fine.
• A SQL Server DBA myth a day: (19/30) TRUNCATE TABLE is non-logged
  • This post explains how and why a TRUNCATE *is* logged and proves it by showing the log records generated.
• A SQL Server DBA myth a day: (18/30) FILESTREAM storage, garbage collection, and more
  • This post addresses five common myths about FILESTREAM storage and internals.
• A SQL Server DBA myth a day: (17/30) page checksums
  • This post addresses siz common myths around page checksums.
• A SQL Server DBA myth a day: (16/30) corruptions and repairs
  • Most of what you’ve ever heard are misconceptions I’m afraid.
• A SQL Server DBA myth a day: (15/30) checkpoint only writes pages from committed transactions
  • This post explains how checkpoint works.
• A SQL Server DBA myth a day: (14/30) clearing the log zeroes out log records
  • This post explains how log clearing really works.
• A DBA myth a day: (13/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005)
  • This post explains how to call DMVs using 2005 syntax in 80-compat mode databases.
• A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core
  • One of the most confusing misconceptions out there.
• A SQL Server DBA myth a day: (11/30) database mirroring failover is instantaneous
  • Marketing nonsense, and I explain why.
• A SQL Server DBA myth a day: (10/30) database mirroring detects failures immediately
  • More marketing nonsense.
• A SQL Server DBA myth a day: (9/30) data file shrink does not affect performance
  • I’m still laughing about this one…
• A SQL Server DBA myth a day: (8/30) unicorns, rainbows, and online index operations
  • Why ‘online’ is a bit of a misnomer…
• A SQL Server DBA myth a day: (7/30) multiple mirrors and log shipping load delays
  • This post explains why you can’t have multiple mirrors.
• A SQL Server DBA myth a day: (6/30) three null bitmap myths
  • Just as it says…
• A SQL Server DBA myth a day: (5/30) AWE must be enabled on 64-bit servers
  • It’s no wonder this is confusing…
• A SQL Server DBA myth a day: (4/30) DDL triggers are INSTEAD OF triggers
  • If only…
• A SQL Server DBA myth a day: (3/30) instant file initialization can be controlled from within SQL Server
  • This is tricky as it’s partly true.
• A SQL Server DBA myth a day: (2/30) DBCC CHECKDB causes blocking
  • I take that misconception as a personal afront :-)
• A SQL Server DBA myth a day: (1/30) in-flight transactions continue after a failover
  • Common failure when designing and HA strategy.

And there you have it – over 100 myths and misconceptions debunked, explained and, in many cases, proven!