Category Archives: SQL Maintenance

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!

Tagged , , , ,

The SQL Server Misconfiguration Chronicles: Part 1 Database AutoClose & AutoShrink

In the SQL Server Consulting world you see a lot of different environments, but mostly you see the same basic misconfigurations for their SQL Server Enviroment.

The following blogposts will show you the most common issues with the configuration of SQL Server, they will tell you what the issue is, why it is a bad configuration and how you can solve it.

The first post will be on two common misconfigured database options: AutoClose & AutoShrink.

What does Autoclose do to my database?

Autoclose will close your database when the last user who uses the database is disconnected.

Why is that a bad thing?

Everytime a user connects to a SQL Database which is closed it will have to be opened again, this will degrade performance because the connecting user will have to wait until the database is opened again before being able to access the data.

How do we solve this issue?

You can execute the following script which will generate statements to alter auto close to false for all databases of the instance

----------------------------------------------------------------------
———————————————————————-
—        Created By Stijn Wynants                                    
—        SQL AutoClose Fixer            
—        Description: Execute Script to PRINT ALTER STATEMENTS
—                     auto_close to false on all databases
———————————————————————-
———————————————————————-
DECLARE @CMD varchar(max)
DECLARE @Database varchar(200)
DECLARE AutoCloseFixer CURSOR
FOR 
select name from sys.databases where database_id >and is_auto_close_on = 1
OPEN AutoCloseFixer
FETCH NEXT FROM AutoCloseFixer INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
         PRINT
————> DisableAutoClose on ‘+@Database+
USE [master];
 GO
ALTER DATABASE [‘+@Database+‘] SET AUTO_CLOSE OFF WITH NO_WAIT;
PRINT ”Disabled AutoClose on ‘+@Database+”’
 ‘
END TRY
BEGIN CATCH
    SELECT  ERROR_MESSAGE()

END CATCH
FETCH NEXT FROM AutoCloseFixer INTO @Database END
CLOSE AutoCloseFixer
DEALLOCATE AutoCloseFixer

What does AutoShrink do to my Database?

It will remove the unused space from your database file and thus making your database file smaller.

Why is that a bad thing?

Because auto shrink process removes all free space out of your database file, there will be no space left for new data. So the moment when new data gets loaded in your database your database file will have to grow again. (This is handled by auto growth, this will be discussed in the next post) This will grow larger than the actual space you needed, which will leave some free space, when the auto shrink feature kicks in again it will remove this space again. This constant shrink grow shrink grow operation will cause file fragmentation (on a system level) and this uses a lot of resources. Also the shrink operation itself uses a lot of resources because it will move pages to other places in you database file which takes CPU IO & generates transaction log. But last but not least when auto shrink services your database it will not only shrink your database but because it moves around the pages it will have a devastating side-effect! It will cause a complete fragmentation of all your database indexes which will cause your performance to plummet.

How do we solve this issue?

You can execute the following script which will generate statements to alter auto growth to false for all databases of the instance

———————————————————————-
———————————————————————-
—        Created By Stijn Wynants                                    
—        SQL AutoShrink Fixer            
—        Description: Execute Script to PRINT ALTER STATEMENTS
—                     auto_shrink to false on all databases
———————————————————————-
———————————————————————-
DECLARE @CMD varchar(max)
DECLARE @Database varchar(200)
DECLARE AutoShrinkFixer CURSOR
FOR
select name from sys.databases where database_id >and is_auto_shrink_on = 1
OPEN AutoShrinkFixer
FETCH NEXT FROM AutoShrinkFixer INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        PRINT

————> DisableAutoShrink on ‘+@Database+
            USE [master];
            GO
            ALTER DATABASE [‘+@Database+‘] SET AUTO_SHRINK OFF WITH NO_WAIT;
            PRINT ”Disabled AutoShrink on ‘+@Database+”’
            ‘
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE()
    END CATCH
    FETCH NEXT
FROM AutoShrinkFixer 
INTO @Database END
CLOSE AutoShrinkFixer
DEALLOCATE AutoShrinkFixer

Thank you all for reading and happy reconfiguring!

The next part will be on Default Memory Settings Misconfiguration!
(<a href="https://swyssql.wordpress.com/2015/12/17/the-sql-server-misconfiguration-chronicles-part-2-instance-memory-settings//)

Stay Tuned!

Tagged , , , , , , , , , , ,