Automated Defragmentation of SQL Indexes – The ADoSI Script

ADoSI Automated Defragmentation of SQL IndexesThe Automated Defragmentation of SQL Indexes, or ADoSI, script is an excellent option for maintaining your Microsoft SQL Server database indexes. ADoSI is made to be a “drop in and go” index defragmentation solution, simple to set up and get your database indexes on their way to being more quick and efficient in minutes. It doesn’t overwhelm you with cryptic options, yet is still flexible and powerful enough to handle the index maintenance needs of most. It’s compatible with all editions of Microsoft SQL Server, versions 2005 through 2014+.

Download ADoSIWant to get ADoSI and dive right in to running some defragmentations and playing around with the script options? You can download the ADoSI script here. Want to learn more about the script’s features and how it works? Then read on…

Why ADoSI for Defragmentation?

There are many Microsoft SQL Server index defragmentation scripts out there. Take Ola Hallengren’s SQL Server Maintenance Solution for example. It’s a great index maintenance solution that can handle any index you throw at it. It’s very configurable, it has fallback mechanisms, and it also includes SQL backup and integrity check management procedures. But because of the script’s length and robustness if you want to understand how it does what it does it can be a bit complicated and cumbersome to decipher. Michelle Ufford’s Index Defrag Script is another good one. This one is easier to read because it has nice inline comments and is made specifically for index defragmentation, and it also has many interesting options.

So, why choose ADoSI instead of one of the other options? What makes ADoSI stand out?

  • It’s nicely formatted, well commented, and easy to read.
  • It’s organized into distinct sections whose functions are easy to understand.
  • It’s flexible, and can be run in many different ways to fit your deployment and maintenance scheduling needs.
  • It adapts to your edition and version of Microsoft SQL Server to run in the best way possible for that environment.
  • It understands the latest SQL Server options.
  • It defrags by individual partition when possible.
  • It handles defrag errors and one index that fails defragmentation won’t keep the other waiting indexes from being run.
  • It’s a complete drop-in index defrag solution, from support table creation and index fragmentation statistics collection and logging to defragmentation and emailing of alerts.
  • Its support tables store detailed information that can be easily queried to do additional analysis of your defragmentation needs over time.
  • And More!

Does your organization have a production support team that handles all production deployments? Does it adhere to development, ISO audit, or other standards or guidelines that limit developers’ access to production systems? The ADoSI script fits nicely into that type of environment. During the production deployment to SQL Server of this script you’d likely want to have just the first “Create” section of the script run to creates the support tables. Then, in your SQL Agent job or wherever you schedule your stuff to run you’d likely set the script up to run in its “Normal” mode, which gathers index fragmentation statistics, defragments, and sends out alerts on defragmentation activity.

Maybe you have an environment where querying your SQL Server databases is acceptable at any time, but operations like index rebuilds have to be performed during a scheduled maintenance window of time. If that maintenance window is short, you may want to run just the “Gather” step of this script some time before the window. That way, you’ll have all of your index fragmentation statistics collected ahead of time and can get right to the defragmenting step of the process when your maintenance window begins.

These are just a couple ideas of the ways ADoSI can be used to enhance the way you do SQL Server index maintenance in your organization.

ADoSI Options

Here are the options available when running the ADoSI script.

@DatabasesToReorg		-- A comma-delimited list of database names to check
	Default	= NULL		--   indexes on.  If null, the currently active
						--   database is checked.  Ex.  'master,msdb'

@RunMode				-- A mode of operation, with options as follows:
	Default	= 'Normal'	--   Normal  = Gather, Defrag, Alert.
						--   Create  = Create defrag tables.
						--   Gather  = Get index stats.
						--   Defrag  = Defrag flagged indexes.
						--   Alert   = Produce alert messages.
						--   All     = Create, Gather, Defrag, Alert.
						--   DCreate = Drop and Create defrag tables.

@OnlineMode				-- Use the following online option to defrag indexes:
	Default	= 'Normal'	--   Normal  = ReOrg/Build as flagged. ReBuild offline.
						--   Lazy    = ReOrg/Build as flagged.  ReBuild online if
						--             possible, offline otherwise.
						--   Offline = Offline reBuild all, ignoring reOrg flag.
						--   Online  = Only do online operations. Fallback to
						--               reOrg if online reBuild is not possible.

@DefragOnly1			-- If enabled, only a single index gets defragged this run.
	Default	= 'false'	--   Useful when combined with RunMode Defrag for testing.

@SPTimeMaxRun			-- No index defrags will be started after this maximum
	Default	= '03:00'	--   duration is reached.  Set to NULL for no time limit.

--------------------------------
@AlertMode				-- Produce Alert messages of the following types:
	Default	= 'Error'	--   None       = No message
						--   Error      = Show indexes that had defrag errors.
						--   Success    = Show successfully defragged indexes.
						--   Both       = Error and Success.
						--   Candidate  = Show index candidates for defrag.

@AlertEmailRecipients	-- A semicolon-delimited list of e-mail addresses
	Default	= NULL		--   to send Alerts to.  Leave null to disable email.
						--   Ex.  'me@myCompany.com;you@anotherCompany.com'

@AlertEmailProfile		-- A valid Database Mail Profile you have set up in
	Default	= NULL		--   SQL Server.  Leave null to use user/system defaults.

@AlertAcknowledge		-- Set the defragAcknowledged bit on entries sent
	Default	= 'true'	--   in Alerts so that they will not be sent again.

How ADoSI works

And now, for those of you that would like to view the code online without having to open the script file itself, here is a brief explanation of each section of ADoSI followed by the code for that respective section.

This ADoSI script is broken up into four sections. The first creates support tables for the script. The second gathers fragmentation statistics for the indexes you’re interested in and decides what needs to be defragmented. The third performs the actual defragmentation. And finally, the fourth produces, displays and emails alert messages about your indexes. These sections can be run independently or in various preselected useful combinations. This flexibility can come in very handy when you consider what environments and runtime scenarios you may encounter.

Create

The first section creates three support tables that ADoSI uses. Of the first two, one holds the current state of the indexes that you are interested in and the other holds a history of all of the indexes that have been defragmented. These support tables were designed to hold a lot of useful, detailed information that not only support this primary defrag process but can also be easily queried to do additional analysis of your defragmentation needs over time.

The third table can be used to optionally exclude indexes from the defragmentation process. This can be useful, for example, if you have a huge index that would take hours to defragment. You may wish to maintain this kind of index manually so the defrag process on that large index doesn’t slow down your system for long periods of time when you are not prepared for it. As another example, maybe you’ve been analyzing your history table and found that you have an index that’s being defragged daily and hurting your overall runtime goal. You think that you might be able make an ETL change, maybe sorting data differently for example before loading it into a table, that would resolve this indexes constant fragmentation issue. But until you have time to look into that, you want to exclude this index from the defragmentation process for now.

1

Gather

The second section first gathers fragmentation statistics for the indexes in the database(s) you’re interested in. It loops through each database you requested, gathering info about all of the indexes contained in each. One thing to keep in mind is that ADoSI, like most other index defragmentation scripts out there, does its analysis on the row data leaf level nodes of indexes. There can be circumstances when the fragmentation of leaf level nodes of an index can look perfectly fine, but the index could still benefit from defragmentation. This situation is illustrated nicely in this article by Holger Schmeling on sqlserver-online. But although this is a possibility, leaf level analysis is usually sufficient, and an index that is not using page space optimally at non-leaf levels will still get optimized when its leaf level gets fragmented enough to get flagged by this script.

From this detailed fragmentation information we’ve collected, those indexes that fall outside desirable fragmentation and page space use thresholds get flagged as needing to be rebuilt or reorganized per Microsoft’s recommended threshold percentages. A SQL command is then assembled to perform the rebuild or reorganize operation on those indexes that have been flagged. Lots of logic is used at this point to determine the appropriate rebuild or reorganize command to generate here, based on the index information collected and calculated, the options selected when running ADoSI, and the SQL environment. This final defrag command gets stored in the defragCommand column for the index to be used in the defrag section of the process.

2

Defrag

The third section performs the actual defragmentation on the indexes. One by one, the defrag command is run for each index that has been flagged. Error conditions are trapped and saved but do not stop the main process, so a single index that has a problem will not prevent the remaining indexes from getting processed. Post-defrag statistics are collected after a defrag command is completed, and the results of the operation are also stored to a history table.

3

Alert

The final section produces summary alert messages of the defragmentation work that has been done. These messages can include a list of indexes that failed to be defragmented along with the description of the error. They can also include indexes that succeeded in being defragmented along with a few before and after fragmentation statistics. Another option is to include indexes that need to be defragmented with some of their details. Records can be flagged so once an alert message has been issued for them they will not show up in another alert. These alerts are displayed and can optionally be emailed to a list of recipients using SQL Server Database Mail.

An alert message set to display both error and success messages could look something like this (manually rigged up to show an error on purpose):

TEST-SqlServer Alert:  Index Defrag completed --ERRORS-- Encountered 

The following indexes produced ERRORS when being defragged:
-----------------------------------------------------------

[MyDatabase].[data].[MyTestTable].[IX_MyTestTable_Calendar]
        Error Message:    'ONLINE' is not a recognized ALTER INDEX REORGANIZE option.

The following indexes have been defragged successfully:
-------------------------------------------------------

[MyDatabase].[data].[AnotherTestTable].[IX_AnotherTestTable_Calendar]
        Old Frag %:  9.35         Old Page Space Used %:  88.68
        New Frag %:  0.12         New Page Space Used %:  90.36

[MyDatabase].[data].[AnotherTestTable].[IX_AnotherTestTable.CalendarAccountCustomer]
        Old Frag %:  93.92        Old Page Space Used %:  61.40
        New Frag %:  0.00         New Page Space Used %:  90.25
4

Conclusion

Download ADoSII hope you enjoy using ADoSI for your index defragmentation needs. If you haven’t yet, download ADoSI and take it for a test drive yourself. And be sure to share any questions, comments, or concerns you may have.

Leave a Reply

Your email address will not be published. Required fields are marked *