Blueprint for Fool-proof re-Indexing of VLDBs
Presenter: Lisa J Phillip
Our Data Warehouse environment is upwards of 7TB in size, and the expectation of “up time” is close to 100%, so it was difficult to find the right re-indexing strategy. Since some of our clustered indexes could be upwards of 80GB, I first had to find a way to re-index those, along with the associated non-clustered indexes. I also had to find a way to avoid massive rollbacks when the process ran out of space. The strategy I came up with is one that would not impact production by the index rebuild, reorganization, and would not fail due to running out of log/data space.
At Quicken Loans, most of our maintenance is home-grown. I built out a process that automatically does the following:
1. It will first gather the appropriate statistics for the process to decide on re-organization or re-indexing
2.It will only attempt to work on one index at a time. If it is a clustered index and there are non-clustered indexes, it will disable all non-clustered indexes first, then intelligently recover and move on. This avoids the large, singular transactions that ultimately fail when a clustered index rebuild causes the rebuild on all non-clustered ones.
3. Lastly, my home-grown CLR will tell the process whether the index process is skipped for lack of space on a) Data and log files, without hitting the max size (all partition filegroups must have space)and b) Mount points (drives). No manual intervention is needed!
About the Speaker
Lisa Phillip is currently a Senior Data Warehouse Engineer with Quicken Loans. She has been involved in building out and maintaining very large Data Warehouses for the past 12 years. Within a year of starting at Quicken Loans she was charged with leading the re-architecture of the Data Warehouse. Her current responsibilities include everything from administration and maintenance, performance tuning, cube and dashboard building in a SQL Server 2008 environment. She also has experience leading a project to architect and rollout a massive Disaster Recovery strategy. In part thanks to these efforts, she attended The Data Warehouse Institute summit in San Diego (2010), where she and her Director of Business Intelligence accepted the Best Practices Award for Operational BI.