DBCC Commands: The Quick and the Dangerous

There are many DBCC statements at the disposal of Database Administrators and Developers. Some are used for the greater good; others can wreak havoc in your system. The majority of these commands are informational, but if you’re not careful you can introduce chaos or at worst, lose data you may never find again. In this session we will explore DBCC commands that are useful for the DBA and developer, and cover when to use them and how. Expect demonstrations that provide a practical application of these commands – both documented and not – that database professionals can utilize quickly and easily.

This question came up during my Statistics presentation, and was at the end as we were winding down.  My reply was, “I have no idea, I’ve never tried it.”

The attendee noted that he had tried to create the index but couldn’t – so it cannot be done – but he was looking to see if there was a creative way to work around it.  During the ensuing conversation he wondered if maybe it couldn’t be the leading column because the index would be too large, and then mentioned that he had ended up making in an included column in the index.  My question was, “Well if it’s an included column what does that do to the size of the index?”

Then I stopped and said something to the effect of, “Hang on, what is the problem you’re trying to solve?  Meaning, I’m guessing you’re trying to put a varchar(max) column in an index because you want to search on it.  Is making it an included column going to help that much?  I suppose you’ll get an index scan instead of a table scan, but is that really the right solution?”

My new favorite question to ask is, “What problem are you trying to solve?”  In this case, I assume that users need to search on the data in the varchar(max) field.  And this can be done in SQL Server a couple different ways.  My first thought was Full Text Search.  Now, configuring FTS is going to take more work than creating an index with an included column, but I would bet that performance and management long-term would be a lot easier.  You could also create a computed column on a substring of the varchar(max) column, and put that in an index.  There are many assumptions you have to handle here, most notably, is the data you want to search always in the same location within the original column?

I’m sure there are other options, but the overall point is this: understand what problem you’re trying to solve, and then figure out what options you have for solving it.  Once you have your options, understand the pros and cons of each, and then make an informed decision.

Circling back to the original question, one of the first things I tried when I was waiting at the Madison airport for my flight home was to create a table with a varchar(max) column and create an index with that as a leading column.  It of course failed, and Books Online clearly states:

Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index.

So there you have it, two presentations at SQLSaturday in Madison, and three blog posts from questions.  SQLSaturdays always reaffirm several things for me.  First, I am truly grateful for the SQL Community – the people, the opportunities, the fun…all of it.  Second, I love presenting, although I tend to think of it as teaching and even said during one of my sessions, “If you have questions please come ask me after class.”  Apparently I was having flashbacks to grad school.  And finally, I will never know enough; there is always so much more to learn.

I’ve blogged about this option before, and whether or not you leave it enabled for a database is not as cut and dried as to whether you leave the Auto Create Statistics option enabled (I’ve yet to personally find a reason to disable Auto Create Statistics).  The Auto Update Statistics option exists for the benefit of performance – when statistics are out of date, the engine will update them for you.  The engine knows when statistics are out of date because it tracks modifications to your data.  The method for tracking modifications changed between SQL 2000 and SQL 2005, and where the modifications are tracked changed between SQL 2005 and SQL 2008.  For this post, I’m just going to discuss SQL 2008 and higher.

Data modifications are tracked at the column level in SQL 2008 and higher in the hidden and undocumented sys.sysrscols system table, specifically in the rcmodified column.  During my statistics session last Saturday, one attendee asked if modifications were still tracked if the Auto Update Statistics was disabled for a database.  In short: Yes.

I admit, I didn’t know the answer off the top of my head – but my logic when I answered the question was that the setting exists at the database level and the engine is tracking modifications for tables and indexed views across numerous databases.  I didn’t expect that the engine would check the database Auto Update Statistics option for each modification, that’s just way too much overhead.  But then I wanted to know for sure so I came home and tested it.  The necessary statements are below, and the DAC is utilized (so if you don’t have it enabled, you’ll need to do that first).

Now, as I wrote this up, I wondered why the engine really continued to track it.  I don’t want to assume it is simply overhead, but it makes a lot of sense.  You can flip the Auto Update option at will, and you can also disable automatic updates at the table, index and statistic level.  If the engine had to reset the rcmodified column for a statistic, index, table or all tables in a database every time one of the settings changed, it could take time and would generate some level of overhead.  Ultimately, it’s just less work to continually track them, and presumably the engine is very efficient at tracking that information.

The script below can be used to show that data modifications are still tracked, even if the Auto Update Statistics option is disabled.  Realize that you’re playing with an undocumented system table and the DAC.  You’re off the grid, proceed with caution, and realize that the behavior you’re seeing could change in a future version.  If you want more details about sysrscols, I highly recommend Paul Randal’s post, How are per-column modification counts tracked?  I admit, I used his query for sysrscols, but I did ask for permission first

-- create a database for testing
CREATE DATABASE [NCI_Corruption]
ON PRIMARY (
NAME = N'NCI_Corruption',
FILENAME = N'C:\Databases\SQL2008R2\NCI_Corruption.mdf' ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
LOG ON (
NAME = N'NCI_Corruption_log',
FILENAME = N'C:\Databases\SQL2008R2\NCI_Corruption_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 1024KB );

ALTER DATABASE [NCI_Corruption] SET RECOVERY SIMPLE;

USE NCI_Corruption;
GO

-- create a table
CREATE TABLE MovieInfoHistory (
MovieName VARCHAR(800),
ReleaseDate SMALLDATETIME,
Rating VARCHAR(5)
);

-- create an index
CREATE NONCLUSTERED INDEX NCI_MovieName ON dbo.MovieInfoHistory (MovieName);

-- connect with the DAC if not already...

-- check rcmodified - it should be 0
SELECT
p.[object_id],
p.[index_id],
rs.[rscolid],
rs.[rcmodified]
FROM sys.sysrscols rs
JOIN sys.partitions p ON rs.[rsid] = p.[partition_id]
WHERE p.[object_id] = OBJECT_ID ('MovieInfoHistory');

-- add some data
INSERT INTO dbo.MovieInfoHistory (
MovieName, ReleaseDate, Rating
)
VALUES
('Caddyshack', '1980-07-25', 'R'),
('Bill & Ted''s Excellent Adventure', '1989-02-17 00:00:00', 'PG'),
('Apollo 13', '1995-05-30 00:00:00', 'PG'),
('The Hunt for Red October', '1990-03-02 00:00:00', 'PG'),
('A Few Good Men', '1994-12-11 00:00:00', 'R'),
('The Natural', '1984-05-11 00:00:00', 'PG'),
('The Truman Show', '1998-06-05 00:00:00', 'PG-13'),
('All The President''s Men', '1976-04-09 00:00:00', 'R'),
('The Right Stuff', '1983-10-21 00:00:00', 'PG-13');

CHECKPOINT;
GO

-- verify that rcmodified has increased
SELECT
p.[object_id],
p.[index_id],
rs.[rscolid],
rs.[rcmodified]
FROM sys.sysrscols rs
JOIN sys.partitions p ON rs.[rsid] = p.[partition_id]
WHERE p.[object_id] = OBJECT_ID ('MovieInfoHistory');

-- turn off auto update stats
ALTER DATABASE NCI_Corruption SET AUTO_UPDATE_STATISTICS OFF;

-- add some more data
INSERT INTO dbo.MovieInfoHistory (
MovieName, ReleaseDate, Rating
)
VALUES
('Miracle', '2004-02-06 00:00:00', 'PG'),
('Walk the Line', '2005-11-18 00:00:00', 'PG-13'),
('Bull Durham', '1988-06-15 00:00:00', 'R'),
('Field of Dreams', '1989-04-21 00:00:00', 'PG'),
('G.I. Jane', '1997-08-22 00:00:00', 'R'),
('St. Elmo''s Fire', '1985-06-28 00:00:00', 'R'),
('Goonies', '1985-06-07 00:00:00', 'TV14'),
('The Shawshank Redemption', '1994-10-14 00:00:00', 'R'),
('The Bad News Bears', '1976-04-07 00:00:00', 'PG');

CHECKPOINT;
GO

-- verify that rcmodified has increased again
SELECT
p.[object_id],
p.[index_id],
rs.[rscolid],
rs.[rcmodified]
FROM sys.sysrscols rs
JOIN sys.partitions p ON rs.[rsid] = p.[partition_id]
WHERE p.[object_id] = OBJECT_ID ('MovieInfoHistory');

-- clean up
USE master;
GO
DROP DATABASE NCI_Corruption;

There were three things that I wanted to follow up on from my presentations, and I’ll do that in this post and the next two.

The first item was mentioned in the DBCC session.  One of the commands I discuss in the session is DBCC CHECKDB, and I have a demo that shows corruption of a nonclustered index and how you can fix it.  If corruption strikes, you’re lucky if it only strikes in a nonclustered index, as it can be fixed without any data loss.  However, fixing the corruption is not the end of the issue.  You then need to make sure you perform root-cause analysis to understand why you had corruption to begin with (e.g. run checkdisk, look for other errors indicating problems with storage).

In order to fix the corruption you have to create a new copy of the index.  When you normally rebuild an index, the engine uses the existing copy of the index to create the new copy.  In the case of corruption in the index, it cannot do that.  I initially thought that I would have to drop and then create the index, and I didn’t love that option because if I didn’t know the index definition, I had to go look it up.  While it’s definitely possible, I was looking for something that required less work.  Remember, if you’re using SQL 2005 or higher, using sp_helpindex to look at index doesn’t tell you everything, as sp_helpindex doesn’t provide any information about included columns or filters (you need Kimberly Tripp’s sp_helpindex script for that).

Then I tested disabling the index and rebuilding it, and that worked!  Problem solved, as I didn’t have to figure out the index definition.  So I did a demo showing this when I was talking about the corruption in the nonclustered index, and Luke Jian ( b | t ) mentioned that you had to be careful if you had a unique constraint in your index, as the time between the drop and create (or disable and rebuild) would leave the table vulnerable for data that violated the constraint to be inserted.  It was an excellent point and I’m glad he mentioned it.  Luke went on to say that this had come up during one of the SQLskills Immersion Events he had attended (and he’s attended IE1, IE2 and IE3 might I add!) and another attendee had suggested wrapping the drop and create into a transaction to prevent an invalid insert.  It was a great learning moment for everyone, including me, and I thought it worth a post.

Remember, in this particular example I’m only working with corruption in the nonclustered index.  Do not disable a a clustered index that is corrupt, and do not drop a clustered index that is corrupt, as you will lose the entire table.

-- create a database for testing
CREATE DATABASE [NCI_Corruption]
ON PRIMARY (
NAME = N'NCI_Corruption',
FILENAME = N'C:\Databases\SQL2008R2\NCI_Corruption.mdf' ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
LOG ON (
NAME = N'NCI_Corruption_log',
FILENAME = N'C:\Databases\SQL2008R2\NCI_Corruption_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 1024KB );

ALTER DATABASE [NCI_Corruption] SET RECOVERY SIMPLE;

USE NCI_Corruption;
GO

-- create a table
CREATE TABLE MovieInfoHistory (
MovieName VARCHAR(800),
ReleaseDate SMALLDATETIME,
Rating VARCHAR(5)
);

-- create the NCI
CREATE UNIQUE NONCLUSTERED INDEX NCI_MovieName ON dbo.MovieInfoHistory (MovieName);

-- add some data
INSERT INTO dbo.MovieInfoHistory (
MovieName, ReleaseDate, Rating
)
VALUES
('Caddyshack', '1980-07-25', 'R'),
('Bill & Ted''s Excellent Adventure', '1989-02-17 00:00:00', 'PG'),
('Apollo 13', '1995-05-30 00:00:00', 'PG'),
('The Hunt for Red October', '1990-03-02 00:00:00', 'PG'),
('A Few Good Men', '1994-12-11 00:00:00', 'R'),
('The Natural', '1984-05-11 00:00:00', 'PG'),
('The Truman Show', '1998-06-05 00:00:00', 'PG-13'),
('All The President''s Men', '1976-04-09 00:00:00', 'R'),
('The Right Stuff', '1983-10-21 00:00:00', 'PG-13');

-- find a page in the NCI to corrupt
DBCC IND ('NCI_Corruption', 'dbo.MovieInfoHistory', 2);

-- take the database offline
USE master;
GO
ALTER DATABASE NCI_Corruption SET OFFLINE;

-- corrupt the Nonclustered Index (I tend to use a hex editor)

-- bring the database back online
USE master;
GO
ALTER DATABASE NCI_Corruption SET ONLINE;

USE NCI_Corruption;
GO

-- verify the corruption
DBCC CHECKTABLE ('dbo.MovieInfoHistory');

-- disable and rebuild the index within a transaction
BEGIN TRANSACTION;
ALTER INDEX NCI_MovieName ON dbo.MovieInfoHistory DISABLE;
ALTER INDEX NCI_MovieName ON dbo.MovieInfoHistory REBUILD;
COMMIT;

-- verify the corruption is gone
DBCC CHECKTABLE ('dbo.MovieInfoHistory');

-- clean up
USE master;
GO
DROP DATABASE NCI_Corruption;

 

Profiler Error

And the PerfMon data looked like this in Profiler:

Truncated PerfMon data in Profiler (click to see larger)

I hadn’t seen that message before and the output was then somewhat useless since I couldn’t compare the PerfMon data with Trace in that format after 6:30 AM.  I took another set of PerfMon and Trace files and tried to the import again.  Same message.  I decided to dig in and see if I could figure it out.

I loaded a third set of PerfMon and Trace, same thing.  I checked to see if the data cut off at the same time in each trace and it didn’t.

Then I tried to load just one counter at a time.  I started with %Processor Time_Total, I have no idea why I picked that counter first, but when I went to click OK, it was grayed out:

Nope, cannot select "OK"

Interesting.  I selected another counter and the OK button became available so I selected OK to import the data and got the error message again.  I started over.  After I selected the %Processor Time_Total counter, I selected another counter, then unselected it.  The OK button was now available:

OK is now available

I imported the data, and I got the error again.  Then I zoomed in on the PerfMon graph to see the timestamp for when it stopped:

Zoomed in to find the timestamp (click to see larger)

At that point, I knew the issue was with data somewhere between 6:40:05 and 6:40:20.  Next I read the data directly from the PerfMon trace, which I had loaded into a database table using relog.  I found this:

PerfMon data up close (click to see larger)

There were two samples taken at 6:40:10, three milliseconds apart.  I have to conclude that for some reason, that causes the import to fail, because when I checked the other files, I found the same thing: multiple entries for the same second, taken milliseconds apart.

There is a fix for this.  I used the relog utility to split out the original file into two files, excluding the data sampled at 6:40:10, and then merged the files back into one.

separate the file:

relog PerfMon_20120309.blg -b 03/09/2012 03:35:00 -e 03/09/2012 06:40:10 -o PartI.blg

relog PerfMon_20120309.blg -b 03/09/2012 06:40:15 -e 03/09/2012 18:35:00 -o Part2.blg

merge files:

relog Part1.blg Part2.blg -o PerfMon_20120309_Fixed.blg

Not elegant, but possible.  Once I did that, I could import all the data for % Processor Time_Total:

Everything except 6:40:10 in the graph (click to see larger)

Even though I have a way to fix this problem, this is not a solution.  The solution is to not have bad data to begin with.  If I were consistently working with this customer’s data, I would dig in and try to figure out what was causing the bad sampling.  It’s not likely that I will have the opportunity to do that, but should I run into this problem again, at least I have a quick fix, and maybe I will have the time then to figure out what’s really going on.

If data is being modified by a transaction, any subsequent select statement that occurs before the modification is complete will read the original copy of the data (not the modified data).

Rob asked me to read it again, and I realized that this explanation was not accurate for READ COMMITTED.  In fact, what I described was the SNAPSHOT isolation level.

What I should have said for READ COMMITTED was:

If data is being modified by a transaction, any subsequent select statement that occurs before the modification is complete will wait until the transaction commits or rolls back to read the data.

(I have noted and modified my original post with this information.)

To be clear, in a READ COMMITTED isolation level, you will only read committed data.  This is different than in the READ UNCOMMITTED isolation level, where can you read uncommitted, or dirty, data.

Here’s a quick example using the HumanResources.Employees table in the AdventureWorks database.  Let’s look at the output if I select from the table.  Notice that for EmployeeID 3, the Title is Engineering Manager.

Original output from Employees table

In one query window, I run the following:

USE AdventureWorks;
GO

BEGIN TRANSACTION;
UPDATE HumanResources.Employee
SET Title = 'Senior Engineering Manager'
WHERE EmployeeID = 3;

Notice that I haven’t committed the data yet.  Also notice that I didn’t set the isolation level, and that’s because the isolation level of the UPDATE does not affect what happens when I SELECT.

Now I open a second query window and run the following:

USE AdventureWorks;
GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

SELECT * FROM HumanResources.Employee;

What happens?  I wait.  And I wait.  And I wait.  This query will continue to wait until I either COMMIT or ROLLBACK my UPDATE statement.  Once I do that, the results will return.  But in this case, let’s kill the query, and then run the following statements:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

SELECT * FROM HumanResources.Employee;

Now I get data back, and notice that the value for EmployeeID 3 is now Senior Engineering Manager, even though that data hasn’t been committed yet.  Yuck.  A dirty read.

Output after a dirty read

Feel free to change the isolation level for the UPDATE statement to prove out that it doesn’t affect what happens when you SELECT – and take it a step further by checking out the lock that gets taken with the SELECT in both isolation levels (I use sp_lock <SPID>).  Have fun!

On a personal note, I was looking forward to this trip because after the meeting I was planning to go to Ann Arbor to see my friend and mentor, Susan.  As I write, I’m in a hotel in Farmington Hills because as I was leaving yesterday, I received a call from one of her graduate students explaining that she was in the hospital.  … … … I didn’t see that one coming.  She is ok, and should be fine long term, but it was a bit of a wake up for me.  Life is short.  You all know this, right?  You do.  But I’ve been thinking about this all morning, which meant I couldn’t let it go and just needed to say what was on my mind.

In my pocket I carry a glass stone with the quote, “Let us live while we live.” (Phillip Doddridge)  It is a reminder to live in the moment, and to live life fully.  In 50 years I don’t want any regrets about things I wish I would have done.  Last night I thought about driving back home, but I was really tired, and I wanted to try and see Susan today, because you just never know.  She’s probably fine, in all honesty, but what if she’s not?

So today, I encourage you to enjoy your life and the people that are in it.  If there’s something that’s missing, go find it.  If there’s something that you’ve wanted to do but are too afraid, get over that hurdle and go do it.  Life is meant to be lived, to be enjoyed.  It’s not always easy, but without a doubt, it’s all worth it.

Thanks for reading.  Now I’m off to Ann Arbor to see Susan, go to Zingerman’s, and pick up some Michigan gear.  Happy Friday all – go tackle the day!

The isolation level for a database connection directly affects the type of lock that is taken for a read operation, and therefore affects concurrency.  I bring this up because I’ve seen people inadvertently create blocking in a database because they do not understand properties for their database connection.  I don’t want to repeat Books Online, but let’s take a minute to review the more common isolation levels in SQL Server.

READ COMMITTED

This is the default isolation level in SQL Server.  When you initiate any connection to SQL Server (e.g. from Management Studio), this isolation level will be used.  In a READ COMMITTED isolation level:

• If data is being modified by a transaction, any subsequent select statement that occurs before the modification is complete will wait until the transaction commits or rolls back to read the data. (Note: this statement has been modified since the original post, please see Isolation Levels and the Effects on Select Statements, Part II for more details.)
• Select statements create shared locks which are released as soon as the data is read. If a shared lock exists, data cannot be modified until the lock is released (for more information see Lock Modes in BOL).  Under the right circumstances, this can lead to blocking.

Lead blocker

READ UNCOMMITTED

This the most optimistic isolation level in SQL Server, and in order to utilize it, the SET TRANSACTION ISOLATION LEVEL statement must be executed when a connection is made.  In a READ UNCOMMITTED isolation level:

• If data is being modified by a transaction, any subsequent select statement that occurs before the modification is complete will read the modified copy of the data. This is known as a dirty read.

Note: You can create the same behavior by using the NOLOCK hint in queries.  That is not something I would recommend, and NOLOCK is another debate for another time.

A dirty read

SNAPSHOT

This isolation level was added in SQL Server 2005, and is considered a good hybrid of READ COMMITTED and READ UNCOMMITTED as it requires that data that is read during a select statement will be the same value that existed when the transaction started.  In a SNAPSHOT isolation level:

• If an insert, update or delete statement does not commit before the select statement begins, then the output from the select will show the original values.
• Locks are not taken when reading data (unless a database is being recovered).

This isolation level requires setting the ALLOW_SNAPSHOT_ISOLATION database option to ON.

What to remember

As a DBA, it’s important to recognize that when you open Management Studio (SSMS) and open a query window to a database and start to query that database, you can affect the type of locks being taken.  If you run a select statement which takes 10 minutes to return data, you may block other activity in the database.  If you’re troubleshooting a problem, you might make it worse.  If you’re just looking at data, or writing some reports against production, you might create a problem.

What you can do

If you’re writing reports, don’t do it against production.  A good DBA restores backups on a regular basis.  Restore a recent backup of production and write reports against it.  Don’t have the space to restore production?  Utilize a test, development, QA or UA environment and write the reports there.  If the database is not comparable in size and/or data distribution to production, at some point you will need to do performance testing so you can make sure the reports are optimized.  At that time, you really want to find the space to restore that recent production backup, even if it’s only available to you for a couple days of testing.  In six months, you do not want to explain why the reports you wrote are causing performance issues in production.

If you’re just selecting data to troubleshoot or look at data, and are not concerned about dirty reads, you can change the isolation level in SSMS.  You must do this each time you open a new query window by executing

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

For subsequent queries issued in that session, the isolation level will be READ UNCOMMITTED. Again, realize that this must be set for each new query window you open.

Alternatively, you can change the default isolation for SSMS connections. I don’t recommend doing this for every SSMS installation in your environment.  I would only change this on the SSMS installed on my own machine, to which only I access, so that I am the only person affected if I start to see unexpected results.

Within SSMS, select Tools | Options. Then select Query Execution | SQL Server | Advanced. In the SET TRANSACTION ISOLATION LEVEL drop down, change the value to READ UNCOMMITTED. Select OK. All new sessions will have an isolation level of READ UNCOMMITTED.

Changing default isolation in SSMS

What about Oracle?

As you may or may not know, the application which I support can also run on Oracle.  There are numerous differences between SQL Server and Oracle, including isolation levels.  Oracle has an optimistic locking model because it assumes that users spend more time reading data than modifying it.  Oracle utilizes two isolation levels to manage data concurrency and consistency: READ COMMITTED and SERIALIZABLE (see Data Concurrency and Consistency for more information).

The default isolation level is READ COMMITTED and when you initiate any connection to Oracle (e.g. from SQLPlus), this isolation level will be used.  In the READ COMMITTED isolation level:

• When a select statement is issued in Oracle, there are no locks taken on the rows being read.

This behavior is very different than SQL Server. A select statement in Oracle should never block a data modification, and a data modification should not block a select statement either.  Within the Oracle community, the phrase is simply: Readers don’t block writers and writers don’t block readers.  In a SQL Server database, you can create the same behavior by using the SNAPSHOT isolation level.

It's Oracle, no one is bothering anyone else

As such, I have seen only two blocking issues in Oracle in all the years I’ve worked with it, but I’ve seen numerous blocking issues in SQL Server.  In Oracle, both issues occurred because the connection was lost during the middle of an update, and the lock taken by the update blocked subsequent updates to that same data.

Additional resources

In this post I wanted to focus on what a DBA should remember when connecting to a production database, as it relates to isolation level.  I have merely touched upon the impact of isolation level, and if you want to read more, I highly recommend Kendra Little’s isolation levels resource page for more information.

Now I’m faced with the challenge of figuring out what to recommend to our customers going forward.  For many years we have recommended that customers have Auto Update Statistics disabled, and I listed the reasons previously:

• We do not want statistics updating during production hours if at all possible; we want the customer to manage statistics through a regularly scheduled job.
• We want statistics updated with 100% sample because very often the distribution in critical tables is skewed.  With Auto Update Statistics enabled, the statistics do not update with 100% sample unless the table is less than 8 MB in size.

We cannot continue to make the same recommendation unless we provide more information about the ramifications (notably query plans are not recompiled by an update of statistics); it would be irresponsible of us to not mention this.  Alternatively, we could change our viewpoint entirely and recommend that customers enable Auto Update Statistics.

After a lot of thought, the recommendation is going to go away completely.  That is, we’re going to tell customers that they can either enable or disable the option, it’s their choice and it depends on how they want to manage statistics.  I feel that we need to provide an explanation to customers about why our recommendation has changed, and I also I believe we need to provide a few suggestions on how to manage statistics.  The challenge here is that so many of our customers do not have a full time DBA.  Very often, the application administrator acts as the DBA.  The application administrator will probably not have the time, nor the inclination, to manage statistics at the detailed level to which a full time Production DBA would.  Is there a set of maintenance and configuration options that we can suggest that will work for the Accidental DBA?

Here are the options I have developed, and please read through the entire rest of the post before adding a comment if you disagree:

Option 1

1. Disable Auto Update Statistics for the database
2. Create a job to update index and column level statistics with 100% sample on a regular basis (or rebuild indexes + update column statistics with 100% sample on a regular basis)
3. Clear procedure cache or restart the instance after the update of statistics is complete

Option 2*

1. Disable Auto Update Statistics for the database
2. Before running any update statistics jobs, enable the Auto Update Statistics for the database.
3. Create a job to update index and column level statistics with 100% sample on a regular basis (or rebuild indexes + update column statistics with 100% sample on a regular basis)
4. When the job is complete, disable the Auto Update Statistics for the database

*Joe Fleming ( t ) initially proposed this option in the comments of my original post

Option 3

1. Enable Auto Update Statistics for the database
2. Create a job to update index and column level statistics with 100% sample on a regular basis (or rebuild indexes + update column statistics with 100% sample on a regular basis)

Before you skip straight to the comments to disagree, hear me out.  Remember that the majority of customers are not 24/7 shops, and do not have a full time DBA.  These customers have databases less than 250 GB, and can take the hit of rebuilding every index and updating statistics every week, every other week or once a month.  Is it overkill?  Probably.  But it works for the majority, which is what we have to target.

Do I love the option of clearing query cache?  No, not at all.  That suggestion probably gives many people a heart attack.  I understand.  We can try to mitigate the effect a bit by just clearing the cache for the one database (DBCC FLUSHPROCINDB(<db_id>)).  But yes, clearing the cache will cause every query to be freshly compiled, and this will utilize a lot more CPU, it will cause query duration to go up initially, and it will affect overall performance.  I get that.  But for a system that is not managed by a full time DBA, I will take this hit in order to ensure that the query plans are based on the current set of data.

Now, the ideal option is:

Option 4

1. Enable Auto Update Statistics for the database
2. Create a job to rebuild indexes and update statistics on a regular basis, dependent upon the level of fragmentation in an index and the need to update statistics because of changes to the data

This option is appropriate for any customer with a full time DBA who is comfortable managing fragmentation and statistics through custom scripts and jobs.  For example, many DBAs use Ola Hallengren’s Index and Statistics Maintenance script, or Michelle Ufford’s Index Defrag script.  To take things a step further, you can monitor when an automatic update of statistics occurs (use the Auto Stats Event Class in Trace), and you can capture snapshots of table row counts (sys.partitions) and index updates (sys.dm_db_index_usage_stats) to understand what tables have data that changes frequently.  High volume or volatile tables (those with a lot of inserts, updates and deletes) may require a more aggressive approach to managing statistics.

Ultimately, there is no silver bullet…no one perfect answer, unless that answer is “It depends.”  But again, you have to know what “it” depends on.  In this case, whether you should have the Auto Update Statistics option enabled or disabled for a database depends on your ability to manage statistics for a database, and understanding that having it disabled can lead to out of date query plans.

However, I am not infallible and if I had recommended something that was not in line with a Microsoft best practice or other well-known recommendation, I would want to know.  And this was Paul’s call to action in the email:

Next time you come across some guidance from a vendor that is clearly wrong or misleading, call them on it! Education is the key to getting things like this fixed.

I completely agree.  If I am wrong, I want to know.  And if I am wrong and spreading misinformation to thousands of customers, I absolutely must know about it.  But I do have one request: be nice about it.  I have no problem admitting when I’m wrong.  I won’t be happy with myself, but I will admit it and will be appreciative, even if someone is very smug or condescending.  But it doesn’t have to be that way.  There is a civil way to point out when someone is wrong – this is true in life, not just vendor recommendations.  As a DBA, you wouldn’t like me too much if I pointed out that your backups were going to the same set of disks as your database and then said, “What were you thinking?”

So when you do find an error, get your data, present your case, and view the discussion as a partnership.  You’re trying to help the vendor, and hopefully they will recognize that and realize the value in you as a DBA and customer.  I truly enjoy some of the relationships I have developed with customer DBAs on both a personal and professional level.  And on a professional level, that relationship can benefit both parties.  They have a direct line to me and can email questions, I have a direct line to them and can ask them to check something in their database, or try something in their environment if they’re willing.  Is that relationship atypical?  Yes.  Is it possible?  Absolutely.