Index maintenance on a VLDB: How to tame the beast!

When working with large data volumes, doing normal day to day maintenance becomes a more difficult issue, in a previous post (https://swyssql.wordpress.com/2015/08/06/dbcc-how-to-check-the-uncheckable-dbcc-on-vldb/) I talked about how to handle DBCC CheckDB on a multi-terabyte database. Today I will tackle another issue I came across while dealing with multi-terabyte databases, Index Maintenance.

Like most of you my absolute favorite solution when it comes to index maintenance is the Ola hallengren solution, this is a perfectly written and solid maintenance solution, but I noticed on VLDB’s this solution will not get passed the fragmentation checking stage of your index.

So in order to tame the VLDB beast, I wrote my own script for handling Index Maintenance on a VLDB. This script can be found on https://www.dropbox.com/s/mmhlxjx7fhln2ky/IndexColStatMaintenance.sql?dl=0

Now first things first: How does the script work?

When using other maintenance solutions, your maintenance will start all over every day, even if your maintenance did not reach all indexes during its maintenance window. To cope with this the VLDB maintenance script will create a few tables which will keep track of the progress of your index maintenance (Checks & Rebuild/Reorganizes), and not start over every day. By doing this we are able to check all indexes & rebuild all indexes. When the whole cycle has been done, it will start over again. What we actually do is divide the full index maintenance workload over a few days.
Another issue I was having that when rebuilding an index which is larger than your TLOG size, your TLOG will grow & cause your disk to get full with all problems which come with it. So in the script is a check built in which will skip the index if not enough space is available at this current moment or in total. It will then get reported in the reporting table, so that you can check which indexes are not getting rebuild and try to get them rebuild in another way.

The parameters

As a normal script I have defined some parameters which you will have to enter to create the index maintenance.

SET @ManagementDatabase =‘master’–> Database in which objects have to be created

This will be the database which you will use to create the objects needed for the script to work

SET @Database =‘WideWorldImporters’–> Database which will be maintained

This will be the database on which the maintenance has to be done

SET @AmountOfMemory = 30 –> Percentage of memory to be used for Online Rebuild Operations(0-100) (EnterpriseOnly)

This will be the amount of memory available for online rebuild. To ensure you don’t use all your memory for one rebuild.

SET @ColumnStatisticsMaintenance ‘Yes’ –>Do you want to maintain Column Statistics? (Yes/No)

If you also want to maintain the column statistics you will have to say yes here

SET @MaxRunTime = 300 –> Amount of minutes the Maintenance can run each night (300 = 5hours)

The amount of minutes your script can run every night, after this amount of time no new statements will be launched

SET @JobStartHour = 1 –> F.E. 1 job will start at 01:00 (possible options 1 2 3 4 5 6 7 8 9 10 11 12 13 … 24)

The start hour of your job, this will be the hour the jobs created by the script will be scheduled at.

SET @LockTimeout = 360 –> Set the locktimeout in seconds

The amount of time in seconds a lock can be kept by de index rebuild/reorganize before cancelling the operation

SET @WithOnline ‘YES’ –>Rebuild indexes with the ONLINE rebuild option (YES OR NO)

This will give you the choice to use online rebuilds or not (in case of enterprise: I would suggest YES,but I had one case where it was NO J )

Objects that will be created

Tables

The ColumnStatistics table will contain the workload which will be done for all Column Statistics. The script uses this table to define whether or not it should resample the statistics

The IndexFragTable will contain all information the script needs to rebuild or reorganize your indexes & where it has to continue since last rebuild

The IndexRebuildColumnStatLoggingTable is the most important table for you because it will contain all information on failed rebuilds and error messages and indexes which are not being checked.

Stored Procedures

The dbo.IndexStatsOptimize is the main stored procedure which will be executed to handle you index maintenance.

The dbo.Update IndexStatsTable will update your table and add new indexes & reset after the cycle has been run.

Job


This job will execute the stored procedures. If you need to make alteration to your schedule this is the place to go, and also if you want to change your parameters you can do it here or by altering the dbo.IndexStatsOptimizer stored procedure.

Availability Groups

The script has been made AG-Aware, the only thing you have to do is execute it on both nodes.

I made a mistake

If you made a mistake with your parameters just reexecute the script It will recreate all objects. If you made an error with the name of your database, then you have to manually remove all objects listed above.

I have had a lot of table changes in my database.

The script will check daily for new tables and indexes add them to the list of indexes to be checked and rebuild, it will also look for deleted table and delete them from the list.

If you have any questions feel free to ask.

Thanks for reading and enjoy the script!

Stay tuned!

Advertisements
Tagged , , , ,

6 thoughts on “Index maintenance on a VLDB: How to tame the beast!

  1. Rajan Patel says:

    Thanks for Script. I will test this on a Database with 750 GB Size.

  2. Anthony says:

    I have a index that is a little over 1tb. When rebuilding the index online I run out of TLOG space since the TLOG has a max size of 2TB.(In my test environment so nothing else is running) Is creating a 2nd TLOG my only option? Any concerns with performance, AG or logging shipping(yes, I’m doing all ) by adding the 2nd TLOG?

    Thanks

    • SQL Stijn says:

      Hi Anthony,

      Have you partitioned your table? Then you can rebuild it piece by piece, however you will not be able to rebuild it online. What version of sql server are you working with? You can alter your maxsize of your tlog file, so there is no need to create a second file.

      Let me know if you have any more questions

      • Anthony Murry says:

        Thanks so very much for you reply…

        Unfortunately I cannot partition the table, but I was able to increase the initial size which worked like a charm.
        I thought it was a hard limited of 2TB.
        Learned something new…

        Thanks

        Anthony L Murry | Sr Database Administrator
        CCC Information Services Inc.
        222 Merchandise Mart Plaza, Suite 900 | Chicago, Il 60654
        O: (312) 229-2449 | C: (708) 516-0181
        amurry@cccis.com | http://www.cccis.com
        [ccc_whitedklogo]

      • SQL Stijn says:

        no problem! let me know if you have any other questions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: