{"id":1137,"date":"2007-11-05T03:01:40","date_gmt":"2007-11-05T03:01:40","guid":{"rendered":"\/blogs\/paul\/post\/Conference-Questions-Pot-Pourri-1-Indexes-stats-corruption-and-Enterprise-only-features.aspx"},"modified":"2017-07-17T08:45:14","modified_gmt":"2017-07-17T15:45:14","slug":"conference-questions-pot-pourri-1-indexes-stats-corruption-and-enterprise-only-features","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/conference-questions-pot-pourri-1-indexes-stats-corruption-and-enterprise-only-features\/","title":{"rendered":"Conference Questions Pot-Pourri #1: Indexes, stats, corruption, and Enterprise-only features"},"content":{"rendered":"

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.\u00a0These 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<\/em>. 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.<\/p>\n

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?<\/strong><\/p>\n

A1)<\/strong> 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<\/em> 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<\/span> 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.<\/p>\n

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<\/em> command, and then drop the old filegroup.<\/p>\n

Q2) Multiple questions about whether a non-clustered index gets rebuilt on SQL Server 2005 under different circumstances<\/strong><\/p>\n

A2) <\/strong>See the blog post I wrote here<\/a> 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<\/em>), 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.<\/p>\n

Q3)<\/strong> 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?<\/strong><\/p>\n

A3)<\/strong> The best list we know of is in MSDN – http:\/\/msdn2.microsoft.com\/en-us\/library\/ms143761.aspx<\/a><\/p>\n

Q4) In a maintenance plan, is it a good idea to do an index rebuild followed by an update statistics?<\/strong><\/p>\n

A4)<\/strong> 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.<\/p>\n

Q5) How can you tell whether DBCC CHECKDB<\/em> is doing a deep-dive<\/em> that’s going to take much longer than usual?<\/strong><\/p>\n

A5)<\/strong> There are several algorithms in DBCC CHECKDB<\/em> that are designed to quickly tell whether a corruption exists or not, but at the expense of not being able to tell exactly where<\/em> the corruption is. The justification for this is that corruptions are not very common (considering the millions of times per day that DBCC CHECKDB<\/em> 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<\/em> 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.<\/p>\n

Q6) What are the various forums where Paul answers questions on corruptions?<\/strong><\/p>\n

A6)<\/strong> There are 4 forums that I (and others with good advice also) frequent:<\/p>\n