Conference Questions Pot-Pourri #1: Indexes, stats, corruption, and Enterprise-only features

As promised, here’s the first of the grab-bag of questions we were asked during conferences. I’m blogging a selection of the stuff I noted down. These are some of the questions we were asked during our pre-con at SQL Connections on Database Maintenance: From Planning to Practice to Post-Mortem. It was cool that people came prepared with so many great questions – most of which we answered during the session and some I need to follow-up with the Product Team about.

Q1) I have a filegroup with two files. I add a third file, use some of the space, and then do a rebuild of the index that takes up most of the space in the original two files. Why doesn’t the newly rebuilt index get spread evenly across the three files? I.e. why doesn’t SQL Server re-balance the data across the files?

A1) There are two things to consider here. The first is the way that SQL Server allocates space from multiple files in a filegroup. It uses a mechanism called proportional-fill that will allocates space from files in round-robin fashion, but weights the allocations towards files that are larger and have more free space. In the example above, the space in the newly-added third file will be used before the first and second files are grown to add more space. The second thing to consider is that the process of rebuilding an index requires building a new copy of the index before dropping the old on – so in the example above, the existing allocated space can’t be reused until after the index rebuild operation completes.

The concept of adding a file and having SQL Server re-balance the data across the files doesn’t exist. It was something I proposed during SQL Server 2005 development but we (seriously) didn’t have time to do it. The solution I recommend is to create a new filegroup with as many files as you need, rebuild the index into the new filegroup using the CREATE INDEX WITH DROP_EXISTING command, and then drop the old filegroup.

Q2) Multiple questions about whether a non-clustered index gets rebuilt on SQL Server 2005 under different circumstances

A2) See the blog post I wrote here which goes into all the different combinations. A lot of the confusion comes from the fact that on SQL Server 2000, for non-unique clustered indexes where SQL Server has to generate a uniquifying value (called a uniquifier), when it gets rebuilt all the non-clustered indexes have to be rebuilt too as the uniquifier values are regenerated. On SQL Server 2005 this is not the case – a BIG improvement.

Q3) A lot of the features we discussed (e.g. partitioning and online operations) are in Enterprise Edition only. What’s the complete list of features that are in Enterprise vs Standard Editions for SQL Server 2005?

A3) The best list we know of is in MSDN – http://msdn2.microsoft.com/en-us/library/ms143761.aspx

Q4) In a maintenance plan, is it a good idea to do an index rebuild followed by an update statistics?

A4) No! An index rebuild will do the equivalent of an update stats with a full scan. A manual update stats will use whichever sampling rate was set for that particular set of statistics. So – not only does doing an update stats after an index rebuild waste resources, you may actually end of with a worse set of stats if the manualy update stats only does a sampled scan.

Q5) How can you tell whether DBCC CHECKDB is doing a deep-dive that’s going to take much longer than usual?

A5) There are several algorithms in DBCC CHECKDB that are designed to quickly tell whether a corruption exists or not, but at the expense of not being able to tell exactly where the corruption is. The justification for this is that corruptions are not very common (considering the millions of times per day that DBCC CHECKDB is run) and so it makes sense to engineer for the success case and take the hit of a longer run-time in the error case. When a corruption is discovered there hasn’t been any to tell that DBCC CHECKDB is going to run longer than usual until SQL Server 2005 SP2. In SP2 a new error, 5268 was added that will be output to the error log when one of the deep-dive algorithms is triggered. I advise you to add an alert on this error.

Q6) What are the various forums where Paul answers questions on corruptions?

A6) There are 4 forums that I (and others with good advice also) frequent:

• The SQL Team Data Corruption Forum
• The MSDN Disaster Recovery and Availability Forum that I founded while I was at Microsoft
• The SQL Server Central 2005 Data Corruption Forum
• The SQL Server Central 2000/7.0 Data Corruption Forum

I’m also happy for you to send me email! Just beware that on forum posts and emails you may not get a reply from anyone for a day or more – so if you have a critical server-down issue then you should contact SQL Product Support.

Ok – that’s enough for tonight. More tomorrow from the Disaster Recovery workshop. Thanks to everyone who came to the workshop today!