Leaving the Land of Confusion…

I've been a big fan of Genesis since as long as I can remember, and I especially like their 1986 album Invisible Touch with the cool song Land of Confusion.   Over the last couple of months we've led you into the Land of Confusion. We revamped and expanded our training classes late last year and […]

Myths and misconceptions: 60-page PDF of blog posts

Back when I did the DBA-Myth-A-Day series in April, many people asked for the 30 blog posts to be collected up into a document for easy printing/reading/etc. I promised, but never got around to it, until I had an email from a blog reader, Peter Maloof, who had very kindly done all the work of […]

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 (732.96 kb)) Back in April I spent an entire month doing a DBA-Myth-A-Day series once a day which was […]

Benchmarking: Introducing SSDs (Part 1: not overloaded log file array)

Well it's been almost 6 weeks since my last benchmarking blog post as I got side-tracked with the Myth-a-Day series and doing real work for clients :-) In the benchmarking series I've been doing, I've been tuning the hardware we have here at home so I can start running some backup and other tests. In the last […]

A SQL Server DBA myth a day: (30/30) backup myths

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) The month is finally over so time for the grand […]

A SQL Server DBA myth a day: (29/30) fixing heap fragmentation

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Another quickie but goodie before the finale tomorrow! Myth #29: […]

A SQL Server DBA myth a day: (28/30) BULK_LOGGED recovery model

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) The BULK_LOGGED recovery model continues to confuse people… Myth #28: […]

A SQL Server DBA myth a day: (27/30) use BACKUP WITH CHECKSUM to replace DBCC CHECKDB

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) A short one today as I'm up to my eyeballs […]

A SQL Server DBA myth a day: (26/30) nested transactions are real

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Nested transactions are an evil invention designed to allow developers […]

A SQL Server DBA myth a day: (25/30) fill factor

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) I'm all mythed-out from yesterday, so today's is a quick […]

A SQL Server DBA myth a day: (24/30) twenty six restore myths

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) One area I haven't touched on yet in the series […]

A SQL Server DBA myth a day: (23/30) lock escalation

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Another really commonly-held belief… Myth #23: lock escalation goes row-to-page […]

A SQL Server DBA myth a day: (22/30) resource governor allows IO governing

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Resource governor is a great feature in SQL Server 2008, […]

A SQL Server DBA myth a day: (21/30) corruption can be fixed by restarting SQL Server

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) This myth (and derivatives) are very common among non-DBAs as […]

A SQL Server DBA myth a day: (20/30) restarting a log backup chain requires a full database backup

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) This myth is one of the most common and I've […]

A SQL Server DBA myth a day: (19/30) TRUNCATE TABLE is non-logged

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list.) Today's myth is very persistent, so it's high time it was debunked with a nice script to prove it too! […]

A SQL Server DBA myth a day: (18/30) FILESTREAM storage, garbage collection, and more

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Phew – starting to get tiring having to make sure […]

A SQL Server DBA myth a day: (17/30) page checksums

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) A few people have suggested some of the myths around […]

A SQL Server DBA myth a day: (16/30) corruptions and repairs

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Short and quicky today as in the dictionary under 'busy' […]

A SQL Server DBA myth a day: (15/30) checkpoint only writes pages from committed transactions

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) My DBA Mythbusters session went *really* well at SQL Connections […]

A SQL Server DBA myth a day: (14/30) clearing the log zeroes out log records

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Today is the big day – 8am PST I'm presenting […]

A DBA myth a day: (13/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005)

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) This blog post is part of two series – my […]

Important things for DBAs to consider

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Over the last month or so I've found myself handing […]

A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) This is a myth I hear over and over and […]

A SQL Server DBA myth a day: (11/30) database mirroring failover is instantaneous

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Following on from yesterday's myth about database mirroring failure detection […]

A SQL Server DBA myth a day: (10/30) database mirroring detects failures immediately

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Off to SQL Connections today in Las Vegas – hope […]

A SQL Server DBA myth a day: (9/30) data file shrink does not affect performance

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Myth #9: Data file shrink does not affect performance.   […]

A SQL Server DBA myth a day: (8/30) unicorns, rainbows, and online index operations

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Another short one today as I'm still teaching a class. […]

A SQL Server DBA myth a day: (7/30) multiple mirrors and log shipping load delays

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) A short one today as I'm teaching a class on […]

A SQL Server DBA myth a day: (6/30) three null bitmap myths

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Today's myth is a multi-parter especially for the folks in […]

A SQL Server DBA myth a day: (5/30) AWE must be enabled on 64-bit servers

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Myth #5: AWE must be enabled on 64-bit servers FALSE […]

A SQL Server DBA myth a day: (4/30) DDL triggers are INSTEAD OF triggers

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Myth #4: DDL triggers (introduced in SQL Server 2005) are […]

A SQL Server DBA myth a day: (3/30) instant file initialization can be controlled from within SQL Server

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) A bit of a shorter one for today as we're […]

A SQL Server DBA myth a day: (2/30) DBCC CHECKDB causes blocking

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) It's the last day of our class here in Boston […]

A SQL Server DBA myth a day: (1/30) in-flight transactions continue after a failover

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Fellow MVP Glenn Berry (Twitter|blog) has just started a blog […]

Misconceptions around database snapshots and transaction rollbacks

This is a quick post to clarify an article I saw on SQLServerCentral this morning that seemed to state that transaction rollbacks push data into database snapshots. This is absolutely not true. A database page is copied into a database snapshot before it is changed in the source database. Although the mechanism is commonly called copy-on-write, […]

Why you should not shrink your data files

(Be sure to join our community to get our bi-weekly newsletter with exclusive content, demo videos, and other SQL Server goodies! Also check out our online training courses.) One of my biggest hot-buttons is around shrinking data files. Although I used to own the shrink code while I was at Microsoft, I never had a […]

Misconceptions about running DMVs on databases with lower compatibility levels

It's lunchtime for my class – time for a blog post! This is an interesting one that crops up every so often (just an hour ago on SQL Server Central) and is not known very widely at all. There's a misconception that you cannot run DMVs in databases that have compatibility levels of 80 or […]

Misconceptions around the log and log backups: how to convince yourself

There’s still a widely held misconception that when properly in the FULL or BULK_LOGGED recovery models that full or differential backups can truncate the log. No. It *NEVER* happens. This is one of the reasons why I’m doing a whole spotlight session on this at PASS this year – the transaction log and its behavior […]

Misconceptions around database repair

This week's been a busy one on the forums and Twitter, with lots of interesting problems people are hitting. One of the things I've noticed is that there are lot's of misconceptions about running repair, so to round out Friday I'm going to run through a list of them for you. Here are the misconceptions, […]

Misconceptions around adding columns to a table

This is a quick follow-on from my Misconceptions around null bitmap size post. The null bitmap is *always* present in a data record (i.e. records in a heap or the leaf-level of a clustered index) except when all columns are defined as SPARSE in SQL Server 2008 onwards, but is optional in index records if all […]

Misconceptions around FILESTREAM storage

This short post is prompted by a question that came in through Twitter – I *knew* it was worth joining and spending time on it (http://twitter.com/PaulRandal). The (paraphrased) question is "can FILESTREAM data be stored remotely?". This has been confusing people, and neither FILESTREAM BOL nor my FILESTREAM whitepaper (see here) explicitly answer the question. […]

Misconceptions around corruptions: can they disappear?

This has come up a few times now, most recently in an email question this morning - subsequent runs of DBCC CHECKDB show varying numbers of corruptions, and sometimes no corruptions – what's going on? Even more strange – a maintenance job runs a DBCC CHECKDB, which shows errors, but then in the morning – no […]

Misconceptions around null bitmap size

I'm starting a new series called 'Misconceptions' – a series of short posts that debunk some of the many myths and misconceptions that exist about the way SQL Server behaves. I actually already did the first post a couple of weeks back (Misconceptions around TF 1118) but just went back to re-tag it. In this […]

Misconceptions around TF 1118

There’s been a recent flurry of confusion and misconceptions about trace flag 1118 in SQL Server 2008. This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load […]

Misconceptions around instant file initialization

Whenever I teach a class I’m amazed at the number of DBAs who don’t know about instant file initialization. Without wanting to redo blog posts that others have done, in a nutshell it allows a file to be create or grown ‘instantly’ by not having to zero the space in the file. This is especially […]

Search Engine Q&A #19: Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking)

Over the last few weeks I've seen (and helped correct) quite a few myths and misconceptions about index rebuild operations. There's enough now to make it worthwhile doing a blog post (and it's too hot here in Orlando for us to go sit by the pool so we're both sitting here blogging)… Myth 1:  index […]