Index Defrag Script, v4.0

UPDATE: This script has been updated. Please view the latest version at http://sqlfool.com/2011/06/index-defrag-script-v4-1/.

In my blog post, “Index Defrag Script Updates – Beta Testers Needed“, I stated “I’ll hopefully have the new version online in just a few days.” That was dated January 26th. I had every intention of following through with it, too, but something came up:

My daughter, Chloe Lynn, was born on February 10th. She’s a happy, healthy baby girl who consumes all of my free time and already has both her parents wrapped around her adorable little finger. So while I apologize for the delay in posting the latest version, I hope you can understand and forgive me. :)

Alrighty, back to SQL stuff! This version of the script has been significantly overhauled from previous versions. Here’s a full synopsis of the changes and enhancements:

- There’s now a time limit option so you have more control over how long your defrags run. This time limit is checked *before* a defrag is begun, so it’s still possible to have a defrag occur after the time limit is exceeded (i.e. a large index).

- I’ve added a static table for managing the index defrag scans. This way, you can start and stop the defrag process without the need to rescan. This is especially useful for VLDB’s or any environment where you’re unable to complete the defrags in one operation.

- Just in case you want to perform a rescan, even if there’s still indexes left to defrag from your last rescan, there’s a parameter to force it.

- There’s now an option to sort by page count, range scan count, or fragmentation level. Range scan count is defaulted, as the indexes that have high amounts of range scans will benefit the most from having a defragged index. You can also specify whether you want to sort by ASC or DESC.

- There’s now min and max parameters for page counts. This is useful for a) ignoring indexes with less than 1 extent (as recommended by Microsoft) and b) for scheduling index operations by size. For instance, you may want to defrag your small indexes during business hours but leave your big indexes for evening or weekend hours.

- There’s now a parameterized option for sorting in TEMPDB. This may reduce execution time and will prevent unnecessary database file size inflation during defrags. NOTE: Make sure you have enough free space in TEMPDB prior to enabling this option.

- I moved the SQL statement output to display before execution so you can see what’s currently executing.

- I’ve added a debug output of the parameters selected. I’ve added additional validation to the start of the script, so this will help show you if an invalid value was submitted and overwritten.

- I’ve added new columns to the log table to show what command is being executed and what error, if any, occurred when trying to execute.

- I’ve added try/catch logic to handle errors during execution; this way, a single error will not prevent the whole script from terminating.

- The script will now force a rebuild for indexes with allow_page_locks = off.

- For those who use partitioning, you can now exclude the right-most populated partition from the defrag operation. This won’t be applicable for all partitioning schemes, but for sliding-window scenarios (one of the most common schemes), it’ll reduce contention on the partition that’s being actively written to.

- I’ve fixed a bug where tables with LOB indexes may have more than one record returned from sys.dm_db_index_physical_stats.

- For various reasons, I’ve removed the option to rebuild stats.

Also, if you have a previous version of the script installed, this version will rename those tables, since there have been some changes made to them.

FAQ:

I often receive the same questions about this script, so allow me to answer them here:

“I keep running the script, but my index is still fragmented. Why?”
This is most likely a very small index. Here’s what Microsoft has to say:

“In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index. For more information about mixed extents, see Understanding Pages and Extents.”

“What database should I create it in?” or “Can I create this in the MASTER database?”
It’s up to you where you create it. You could technically create it in the MASTER database, but I recommend creating a utility database for your DBA administrative tasks.

“Can I run this againt a SharePoint database?”
I’ve never tried personally, but I’ve been told it runs just fine.

“What are the minimum requirements to run this script?” or “Will this run on SQL Server 2000 instances?”
You need to be on SQL Server 2005 SP2 or higher.

Without further ado, here’s the script:

UPDATE: This script has been updated. Please view the latest version at http://sqlfool.com/2011/06/index-defrag-script-v4-1/.

/* Scroll down to the see notes, disclaimers, and licensing information */
 
Declare @indexDefragLog_rename      varchar(128)
    , @indexDefragExclusion_rename  varchar(128)
    , @indexDefragStatus_rename     varchar(128);
 
Select @indexDefragLog_rename       = 'dba_indexDefragLog_obsolete_' + Convert(varchar(10), GetDate(), 112)
    , @indexDefragExclusion_rename  = 'dba_indexDefragExclusion_obsolete_' + Convert(varchar(10), GetDate(), 112)
    , @indexDefragStatus_rename     = 'dba_indexDefragStatus_obsolete_' + Convert(varchar(10), GetDate(), 112);
 
If Exists(Select [object_id] From sys.tables Where [name] = 'dba_indexDefragLog')
    Execute sp_rename dba_indexDefragLog, @indexDefragLog_rename;
 
If Exists(Select [object_id] From sys.tables Where [name] = 'dba_indexDefragExclusion')
    Execute sp_rename dba_indexDefragExclusion, @indexDefragExclusion_rename;
 
If Exists(Select [object_id] From sys.tables Where [name] = 'dba_indexDefragStatus')
    Execute sp_rename dba_indexDefragStatus, @indexDefragStatus_rename;
Go
 
Create Table dbo.dba_indexDefragLog
(
      indexDefrag_id    int identity(1,1)   Not Null
    , databaseID        int                 Not Null
    , databaseName      nvarchar(128)       Not Null
    , objectID          int                 Not Null
    , objectName        nvarchar(128)       Not Null
    , indexID           int                 Not Null
    , indexName         nvarchar(128)       Not Null
    , partitionNumber   smallint            Not Null
    , fragmentation     float               Not Null
    , page_count        int                 Not Null
    , dateTimeStart     datetime            Not Null
    , dateTimeEnd       datetime            Null
    , durationSeconds   int                 Null
    , sqlStatement      varchar(4000)       Null
    , errorMessage      varchar(1000)       Null
 
    Constraint PK_indexDefragLog_v40
        Primary Key Clustered (indexDefrag_id)
);
 
Print 'dba_indexDefragLog Table Created';
 
Create Table dbo.dba_indexDefragExclusion
(
      databaseID        int                 Not Null
    , databaseName      nvarchar(128)       Not Null
    , objectID          int                 Not Null
    , objectName        nvarchar(128)       Not Null
    , indexID           int                 Not Null
    , indexName         nvarchar(128)       Not Null
    , exclusionMask     int                 Not Null
        /* 1=Sunday, 2=Monday, 4=Tuesday, 8=Wednesday, 16=Thursday, 32=Friday, 64=Saturday */
 
    Constraint PK_indexDefragExclusion_v40
        Primary Key Clustered (databaseID, objectID, indexID)
);
 
Print 'dba_indexDefragExclusion Table Created';
 
Create Table dbo.dba_indexDefragStatus
(
      databaseID        int
    , databaseName      nvarchar(128)
    , objectID          int
    , indexID           int
    , partitionNumber   smallint
    , fragmentation     float
    , page_count        int
    , range_scan_count  bigint
    , schemaName        nvarchar(128)   Null
    , objectName        nvarchar(128)   Null
    , indexName         nvarchar(128)   Null
    , scanDate          datetime        
    , defragDate        datetime        Null
    , printStatus       bit             Default(0)
    , exclusionMask     int             Default(0)
 
    Constraint PK_indexDefragStatus_v40
        Primary Key Clustered(databaseID, objectID, indexID, partitionNumber)
);
 
Print 'dba_indexDefragStatus Table Created';
 
If ObjectProperty(Object_ID('dbo.dba_indexDefrag_sp'), N'IsProcedure') = 1
Begin
    Drop Procedure dbo.dba_indexDefrag_sp;
    Print 'Procedure dba_indexDefrag_sp dropped';
End;
Go
 
Create Procedure dbo.dba_indexDefrag_sp
 
    /* Declare Parameters */
      @minFragmentation     float           = 10.0  
        /* in percent, will not defrag if fragmentation less than specified */
    , @rebuildThreshold     float           = 30.0  
        /* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */
    , @executeSQL           bit             = 1     
        /* 1 = execute; 0 = print command only */
    , @defragOrderColumn    nvarchar(20)    = 'range_scan_count'
        /* Valid options are: range_scan_count, fragmentation, page_count */
    , @defragSortOrder      nvarchar(4)     = 'DESC'
        /* Valid options are: ASC, DESC */
    , @timeLimit            int             = 720 /* defaulted to 12 hours */
        /* Optional time limitation; expressed in minutes */
    , @database             varchar(128)    = Null
        /* Option to specify a database name; null will return all */
    , @tableName            varchar(4000)   = Null  -- databaseName.schema.tableName
        /* Option to specify a table name; null will return all */
    , @forceRescan          bit             = 0
        /* Whether or not to force a rescan of indexes; 1 = force, 0 = use existing scan, if available */
    , @scanMode             varchar(10)     = N'LIMITED'
        /* Options are LIMITED, SAMPLED, and DETAILED */
    , @minPageCount         int             = 8 
        /*  MS recommends > 1 extent (8 pages) */
    , @maxPageCount         int             = Null
        /* NULL = no limit */
    , @excludeMaxPartition  bit             = 0
        /* 1 = exclude right-most populated partition; 0 = do not exclude; see notes for caveats */
    , @onlineRebuild        bit             = 1     
        /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
    , @sortInTempDB         bit             = 1
        /* 1 = perform sort operation in TempDB; 0 = perform sort operation in the index's database */
    , @maxDopRestriction    tinyint         = Null
        /* Option to restrict the number of processors for the operation; only in Enterprise */
    , @printCommands        bit             = 0     
        /* 1 = print commands; 0 = do not print commands */
    , @printFragmentation   bit             = 0
        /* 1 = print fragmentation prior to defrag; 
           0 = do not print */
    , @defragDelay          char(8)         = '00:00:05'
        /* time to wait between defrag commands */
    , @debugMode            bit             = 0
        /* display some useful comments to help determine if/where issues occur */
 
As
/*********************************************************************************
    Name:       dba_indexDefrag_sp
 
    Author:     Michelle Ufford, http://sqlfool.com
 
    Purpose:    Defrags one or more indexes for one or more databases
 
    Notes:
 
    CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING.
             DO NOT RUN UNATTENDED ON LARGE DATABASES DURING BUSINESS HOURS.
 
      @minFragmentation     defaulted to 10%, will not defrag if fragmentation 
                            is less than that
 
      @rebuildThreshold     defaulted to 30% as recommended by Microsoft in BOL;
                            greater than 30% will result in rebuild instead
 
      @executeSQL           1 = execute the SQL generated by this proc; 
                            0 = print command only
 
      @defragOrderColumn    Defines how to prioritize the order of defrags.  Only
                            used if @executeSQL = 1.  
                            Valid options are: 
                            range_scan_count = count of range and table scans on the
                                               index; in general, this is what benefits 
                                               the most from defragmentation
                            fragmentation    = amount of fragmentation in the index;
                                               the higher the number, the worse it is
                            page_count       = number of pages in the index; affects
                                               how long it takes to defrag an index
 
      @defragSortOrder      The sort order of the ORDER BY clause.
                            Valid options are ASC (ascending) or DESC (descending).
 
      @timeLimit            Optional, limits how much time can be spent performing 
                            index defrags; expressed in minutes.
 
                            NOTE: The time limit is checked BEFORE an index defrag
                                  is begun, thus a long index defrag can exceed the
                                  time limitation.
 
      @database             Optional, specify specific database name to defrag;
                            If not specified, all non-system databases will
                            be defragged.
 
      @tableName            Specify if you only want to defrag indexes for a 
                            specific table, format = databaseName.schema.tableName;
                            if not specified, all tables will be defragged.
 
      @forceRescan          Whether or not to force a rescan of indexes.  If set
                            to 0, a rescan will not occur until all indexes have
                            been defragged.  This can span multiple executions.
                            1 = force a rescan
                            0 = use previous scan, if there are indexes left to defrag
 
      @scanMode             Specifies which scan mode to use to determine
                            fragmentation levels.  Options are:
                            LIMITED - scans the parent level; quickest mode,
                                      recommended for most cases.
                            SAMPLED - samples 1% of all data pages; if less than
                                      10k pages, performs a DETAILED scan.
                            DETAILED - scans all data pages.  Use great care with
                                       this mode, as it can cause performance issues.
 
      @minPageCount         Specifies how many pages must exist in an index in order 
                            to be considered for a defrag.  Defaulted to 8 pages, as 
                            Microsoft recommends only defragging indexes with more 
                            than 1 extent (8 pages).  
 
                            NOTE: The @minPageCount will restrict the indexes that
                            are stored in dba_indexDefragStatus table.
 
      @maxPageCount         Specifies the maximum number of pages that can exist in 
                            an index and still be considered for a defrag.  Useful
                            for scheduling small indexes during business hours and
                            large indexes for non-business hours.
 
                            NOTE: The @maxPageCount will restrict the indexes that
                            are defragged during the current operation; it will not
                            prevent indexes from being stored in the 
                            dba_indexDefragStatus table.  This way, a single scan
                            can support multiple page count thresholds.
 
      @excludeMaxPartition  If an index is partitioned, this option specifies whether
                            to exclude the right-most populated partition.  Typically,
                            this is the partition that is currently being written to in
                            a sliding-window scenario.  Enabling this feature may reduce
                            contention.  This may not be applicable in other types of 
                            partitioning scenarios.  Non-partitioned indexes are 
                            unaffected by this option.
                            1 = exclude right-most populated partition
                            0 = do not exclude
 
      @onlineRebuild        1 = online rebuild; 
                            0 = offline rebuild
 
      @sortInTempDB         Specifies whether to defrag the index in TEMPDB or in the
                            database the index belongs to.  Enabling this option may
                            result in faster defrags and prevent database file size 
                            inflation.
                            1 = perform sort operation in TempDB
                            0 = perform sort operation in the index's database 
 
      @maxDopRestriction    Option to specify a processor limit for index rebuilds
 
      @printCommands        1 = print commands to screen; 
                            0 = do not print commands
 
      @printFragmentation   1 = print fragmentation to screen;
                            0 = do not print fragmentation
 
      @defragDelay          Time to wait between defrag commands; gives the
                            server a little time to catch up 
 
      @debugMode            1 = display debug comments; helps with troubleshooting
                            0 = do not display debug comments
 
    Called by:  SQL Agent Job or DBA
 
    ----------------------------------------------------------------------------
    DISCLAIMER: 
    This code and information are provided "AS IS" without warranty of any kind,
    either expressed or implied, including but not limited to the implied 
    warranties or merchantability and/or fitness for a particular purpose.
    ----------------------------------------------------------------------------
    LICENSE: 
    This index defrag script is free to download and use for personal, educational, 
    and internal corporate purposes, provided that this header is preserved. 
    Redistribution or sale of this index defrag script, in whole or in part, is 
    prohibited without the author's express written consent.
    ----------------------------------------------------------------------------
    Date        Initials	Version Description
    ----------------------------------------------------------------------------
    2007-12-18  MFU         1.0     Initial Release
    2008-10-17  MFU         1.1     Added @defragDelay, CIX_temp_indexDefragList
    2008-11-17  MFU         1.2     Added page_count to log table
                                    , added @printFragmentation option
    2009-03-17  MFU         2.0     Provided support for centralized execution
                                    , consolidated Enterprise & Standard versions
                                    , added @debugMode, @maxDopRestriction
                                    , modified LOB and partition logic  
    2009-06-18  MFU         3.0     Fixed bug in LOB logic, added @scanMode option
                                    , added support for stat rebuilds (@rebuildStats)
                                    , support model and msdb defrag
                                    , added columns to the dba_indexDefragLog table
                                    , modified logging to show "in progress" defrags
                                    , added defrag exclusion list (scheduling)
    2009-08-28  MFU         3.1     Fixed read_only bug for database lists
    2010-04-20  MFU         4.0     Added time limit option
                                    , added static table with rescan logic
                                    , added parameters for page count & SORT_IN_TEMPDB
                                    , added try/catch logic and additional debug options
                                    , added options for defrag prioritization
                                    , fixed bug for indexes with allow_page_lock = off
                                    , added option to exclude right-most partition
                                    , removed @rebuildStats option
                                    , refer to http://sqlfool.com for full release notes
*********************************************************************************
    Example of how to call this script:
 
        Exec dbo.dba_indexDefrag_sp
              @executeSQL           = 1
            , @printCommands        = 1
            , @debugMode            = 1
            , @printFragmentation   = 1
            , @forceRescan          = 1
            , @maxDopRestriction    = 1
            , @minPageCount         = 8
            , @maxPageCount         = Null
            , @minFragmentation     = 1
            , @rebuildThreshold     = 30
            , @defragDelay          = '00:00:05'
            , @defragOrderColumn    = 'page_count'
            , @defragSortOrder      = 'DESC'
            , @excludeMaxPartition  = 1
            , @timeLimit            = Null;
*********************************************************************************/																
Set NoCount On;
Set XACT_Abort On;
Set Quoted_Identifier On;
 
Begin
 
    Begin Try
 
        /* Just a little validation... */
        If @minFragmentation Is Null 
            Or @minFragmentation Not Between 0.00 And 100.0
                Set @minFragmentation = 10.0;
 
        If @rebuildThreshold Is Null
            Or @rebuildThreshold Not Between 0.00 And 100.0
                Set @rebuildThreshold = 30.0;
 
        If @defragDelay Not Like '00:[0-5][0-9]:[0-5][0-9]'
            Set @defragDelay = '00:00:05';
 
        If @defragOrderColumn Is Null
            Or @defragOrderColumn Not In ('range_scan_count', 'fragmentation', 'page_count')
                Set @defragOrderColumn = 'range_scan_count';
 
        If @defragSortOrder Is Null
            Or @defragSortOrder Not In ('ASC', 'DESC')
                Set @defragSortOrder = 'DESC';
 
        If @scanMode Not In ('LIMITED', 'SAMPLED', 'DETAILED')
            Set @scanMode = 'LIMITED';
 
        If @debugMode Is Null
            Set @debugMode = 0;
 
        If @forceRescan Is Null
            Set @forceRescan = 0;
 
        If @sortInTempDB Is Null
            Set @sortInTempDB = 1;
 
 
        If @debugMode = 1 RaisError('Undusting the cogs and starting up...', 0, 42) With NoWait;
 
        /* Declare our variables */
        Declare   @objectID                 int
                , @databaseID               int
                , @databaseName             nvarchar(128)
                , @indexID                  int
                , @partitionCount           bigint
                , @schemaName               nvarchar(128)
                , @objectName               nvarchar(128)
                , @indexName                nvarchar(128)
                , @partitionNumber          smallint
                , @fragmentation            float
                , @pageCount                int
                , @sqlCommand               nvarchar(4000)
                , @rebuildCommand           nvarchar(200)
                , @dateTimeStart            datetime
                , @dateTimeEnd              datetime
                , @containsLOB              bit
                , @editionCheck             bit
                , @debugMessage             nvarchar(4000)
                , @updateSQL                nvarchar(4000)
                , @partitionSQL             nvarchar(4000)
                , @partitionSQL_Param       nvarchar(1000)
                , @LOB_SQL                  nvarchar(4000)
                , @LOB_SQL_Param            nvarchar(1000)
                , @indexDefrag_id           int
                , @startDateTime            datetime
                , @endDateTime              datetime
                , @getIndexSQL              nvarchar(4000)
                , @getIndexSQL_Param        nvarchar(4000)
                , @allowPageLockSQL         nvarchar(4000)
                , @allowPageLockSQL_Param   nvarchar(4000)
                , @allowPageLocks           int
                , @excludeMaxPartitionSQL   nvarchar(4000);
 
        /* Initialize our variables */
        Select @startDateTime = GetDate()
            , @endDateTime = DateAdd(minute, @timeLimit, GetDate());
 
        /* Create our temporary tables */
        Create Table #databaseList
        (
              databaseID        int
            , databaseName      varchar(128)
            , scanStatus        bit
        );
 
        Create Table #processor 
        (
              [index]           int
            , Name              varchar(128)
            , Internal_Value    int
            , Character_Value   int
        );
 
        Create Table #maxPartitionList
        (
              databaseID        int
            , objectID          int
            , indexID           int
            , maxPartition      int
        );
 
        If @debugMode = 1 RaisError('Beginning validation...', 0, 42) With NoWait;
 
        /* Make sure we're not exceeding the number of processors we have available */
        Insert Into #processor
        Execute xp_msver 'ProcessorCount';
 
        If @maxDopRestriction Is Not Null And @maxDopRestriction > (Select Internal_Value From #processor)
            Select @maxDopRestriction = Internal_Value
            From #processor;
 
        /* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */
        If (Select ServerProperty('EditionID')) In (1804890536, 610778273, -2117995310) 
            Set @editionCheck = 1 -- supports online rebuilds
        Else
            Set @editionCheck = 0; -- does not support online rebuilds
 
        /* Output the parameters we're working with */
        If @debugMode = 1 
        Begin
 
            Select @debugMessage = 'Your selected parameters are... 
            Defrag indexes with fragmentation greater than ' + Cast(@minFragmentation As varchar(10)) + ';
            Rebuild indexes with fragmentation greater than ' + Cast(@rebuildThreshold As varchar(10)) + ';
            You' + Case When @executeSQL = 1 Then ' DO' Else ' DO NOT' End + ' want the commands to be executed automatically; 
            You want to defrag indexes in ' + @defragSortOrder + ' order of the ' + UPPER(@defragOrderColumn) + ' value;
            You have' + Case When @timeLimit Is Null Then ' not specified a time limit;' Else ' specified a time limit of ' 
                + Cast(@timeLimit As varchar(10)) End + ' minutes;
            ' + Case When @database Is Null Then 'ALL databases' Else 'The ' + @database + ' database' End + ' will be defragged;
            ' + Case When @tableName Is Null Then 'ALL tables' Else 'The ' + @tableName + ' table' End + ' will be defragged;
            We' + Case When Exists(Select Top 1 * From dbo.dba_indexDefragStatus Where defragDate Is Null)
                And @forceRescan <> 1 Then ' WILL NOT' Else ' WILL' End + ' be rescanning indexes;
            The scan will be performed in ' + @scanMode + ' mode;
            You want to limit defrags to indexes with' + Case When @maxPageCount Is Null Then ' more than ' 
                + Cast(@minPageCount As varchar(10)) Else
                ' between ' + Cast(@minPageCount As varchar(10))
                + ' and ' + Cast(@maxPageCount As varchar(10)) End + ' pages;
            Indexes will be defragged' + Case When @editionCheck = 0 Or @onlineRebuild = 0 Then ' OFFLINE;' Else ' ONLINE;' End + '
            Indexes will be sorted in' + Case When @sortInTempDB = 0 Then ' the DATABASE' Else ' TEMPDB;' End + '
            Defrag operations will utilize ' + Case When @editionCheck = 0 Or @maxDopRestriction Is Null 
                Then 'system defaults for processors;' 
                Else Cast(@maxDopRestriction As varchar(2)) + ' processors;' End + '
            You' + Case When @printCommands = 1 Then ' DO' Else ' DO NOT' End + ' want to print the ALTER INDEX commands; 
            You' + Case When @printFragmentation = 1 Then ' DO' Else ' DO NOT' End + ' want to output fragmentation levels; 
            You want to wait ' + @defragDelay + ' (hh:mm:ss) between defragging indexes;
            You want to run in' + Case When @debugMode = 1 Then ' DEBUG' Else ' SILENT' End + ' mode.';
 
            RaisError(@debugMessage, 0, 42) With NoWait;
 
        End;
 
        If @debugMode = 1 RaisError('Grabbing a list of our databases...', 0, 42) With NoWait;
 
        /* Retrieve the list of databases to investigate */
        Insert Into #databaseList
        Select database_id
            , name
            , 0 -- not scanned yet for fragmentation
        From sys.databases
        Where name = IsNull(@database, name)
            And [name] Not In ('master', 'tempdb')-- exclude system databases
            And [state] = 0 -- state must be ONLINE
            And is_read_only = 0;  -- cannot be read_only
 
        /* Check to see if we have indexes in need of defrag; otherwise, re-scan the database(s) */
        If Not Exists(Select Top 1 * From dbo.dba_indexDefragStatus Where defragDate Is Null)
            Or @forceRescan = 1
        Begin
 
            /* Truncate our list of indexes to prepare for a new scan */
            Truncate Table dbo.dba_indexDefragStatus;
 
            If @debugMode = 1 RaisError('Looping through our list of databases and checking for fragmentation...', 0, 42) With NoWait;
 
            /* Loop through our list of databases */
            While (Select Count(*) From #databaseList Where scanStatus = 0) > 0
            Begin
 
                Select Top 1 @databaseID = databaseID
                From #databaseList
                Where scanStatus = 0;
 
                Select @debugMessage = '  working on ' + DB_Name(@databaseID) + '...';
 
                If @debugMode = 1
                    RaisError(@debugMessage, 0, 42) With NoWait;
 
               /* Determine which indexes to defrag using our user-defined parameters */
                Insert Into dbo.dba_indexDefragStatus
                (
                      databaseID
                    , databaseName
                    , objectID
                    , indexID
                    , partitionNumber
                    , fragmentation
                    , page_count
                    , range_scan_count
                    , scanDate
                )
                Select
                      ps.database_id As 'databaseID'
                    , QuoteName(DB_Name(ps.database_id)) As 'databaseName'
                    , ps.object_id As 'objectID'
                    , ps.index_id As 'indexID'
                    , ps.partition_number As 'partitionNumber'
                    , Sum(ps.avg_fragmentation_in_percent) As 'fragmentation'
                    , Sum(ps.page_count) As 'page_count'
                    , os.range_scan_count
                    , GetDate() As 'scanDate'
                From sys.dm_db_index_physical_stats(@databaseID, Object_Id(@tableName), Null , Null, @scanMode) As ps
                Join sys.dm_db_index_operational_stats(@databaseID, Object_Id(@tableName), Null , Null) as os
                    On ps.database_id = os.database_id
                    And ps.object_id = os.object_id
                    and ps.index_id = os.index_id
                    And ps.partition_number = os.partition_number
                Where avg_fragmentation_in_percent >= @minFragmentation 
                    And ps.index_id > 0 -- ignore heaps
                    And ps.page_count > @minPageCount 
                    And ps.index_level = 0 -- leaf-level nodes only, supports @scanMode
                Group By ps.database_id 
                    , QuoteName(DB_Name(ps.database_id)) 
                    , ps.object_id 
                    , ps.index_id 
                    , ps.partition_number 
                    , os.range_scan_count
                Option (MaxDop 2);
 
                /* Do we want to exclude right-most populated partition of our partitioned indexes? */
                If @excludeMaxPartition = 1
                Begin
 
                    Set @excludeMaxPartitionSQL = '
                        Select ' + Cast(@databaseID As varchar(10)) + ' As [databaseID]
                            , [object_id]
                            , index_id
                            , Max(partition_number) As [maxPartition]
                        From ' + DB_Name(@databaseID) + '.sys.partitions
                        Where partition_number > 1
                            And [rows] > 0
                        Group By object_id
                            , index_id;';
 
                    Insert Into #maxPartitionList
                    Execute sp_executesql @excludeMaxPartitionSQL;
 
                End;
 
                /* Keep track of which databases have already been scanned */
                Update #databaseList
                Set scanStatus = 1
                Where databaseID = @databaseID;
 
            End
 
            /* We don't want to defrag the right-most populated partition, so
               delete any records for partitioned indexes where partition = Max(partition) */
            If @excludeMaxPartition = 1
            Begin
 
                Delete ids
                From dbo.dba_indexDefragStatus As ids
                Join #maxPartitionList As mpl
                    On ids.databaseID = mpl.databaseID
                    And ids.objectID = mpl.objectID
                    And ids.indexID = mpl.indexID
                    And ids.partitionNumber = mpl.maxPartition;
 
            End;
 
            /* Update our exclusion mask for any index that has a restriction on the days it can be defragged */
            Update ids
            Set ids.exclusionMask = ide.exclusionMask
            From dbo.dba_indexDefragStatus As ids
            Join dbo.dba_indexDefragExclusion As ide
                On ids.databaseID = ide.databaseID
                And ids.objectID = ide.objectID
                And ids.indexID = ide.indexID;
 
        End
 
        Select @debugMessage = 'Looping through our list... there are ' + Cast(Count(*) As varchar(10)) + ' indexes to defrag!'
        From dbo.dba_indexDefragStatus
        Where defragDate Is Null
            And page_count Between @minPageCount And IsNull(@maxPageCount, page_count);
 
        If @debugMode = 1 RaisError(@debugMessage, 0, 42) With NoWait;
 
        /* Begin our loop for defragging */
        While (Select Count(*) 
               From dbo.dba_indexDefragStatus 
               Where (
                           (@executeSQL = 1 And defragDate Is Null) 
                        Or (@executeSQL = 0 And defragDate Is Null And printStatus = 0)
                     )
                And exclusionMask & Power(2, DatePart(weekday, GetDate())-1) = 0
                And page_count Between @minPageCount And IsNull(@maxPageCount, page_count)) > 0
        Begin
 
            /* Check to see if we need to exit our loop because of our time limit */        
            If IsNull(@endDateTime, GetDate()) < GetDate()
            Begin
                RaisError('Our time limit has been exceeded!', 11, 42) With NoWait;
            End;
 
            If @debugMode = 1 RaisError('  Picking an index to beat into shape...', 0, 42) With NoWait;
 
            /* Grab the index with the highest priority, based on the values submitted; 
               Look at the exclusion mask to ensure it can be defragged today */
            Set @getIndexSQL = N'
            Select Top 1 
                  @objectID_Out         = objectID
                , @indexID_Out          = indexID
                , @databaseID_Out       = databaseID
                , @databaseName_Out     = databaseName
                , @fragmentation_Out    = fragmentation
                , @partitionNumber_Out  = partitionNumber
                , @pageCount_Out        = page_count
            From dbo.dba_indexDefragStatus
            Where defragDate Is Null ' 
                + Case When @executeSQL = 0 Then 'And printStatus = 0' Else '' End + '
                And exclusionMask & Power(2, DatePart(weekday, GetDate())-1) = 0
                And page_count Between @p_minPageCount and IsNull(@p_maxPageCount, page_count)
            Order By + ' + @defragOrderColumn + ' ' + @defragSortOrder;
 
            Set @getIndexSQL_Param = N'@objectID_Out        int OutPut
                                     , @indexID_Out         int OutPut
                                     , @databaseID_Out      int OutPut
                                     , @databaseName_Out    nvarchar(128) OutPut
                                     , @fragmentation_Out   int OutPut
                                     , @partitionNumber_Out int OutPut
                                     , @pageCount_Out       int OutPut
                                     , @p_minPageCount      int
                                     , @p_maxPageCount      int';
 
            Execute sp_executesql @getIndexSQL
                , @getIndexSQL_Param
                , @p_minPageCount       = @minPageCount
                , @p_maxPageCount       = @maxPageCount
                , @objectID_Out         = @objectID OutPut
                , @indexID_Out          = @indexID OutPut
                , @databaseID_Out       = @databaseID OutPut
                , @databaseName_Out     = @databaseName OutPut
                , @fragmentation_Out    = @fragmentation OutPut
                , @partitionNumber_Out  = @partitionNumber OutPut
                , @pageCount_Out        = @pageCount OutPut;
 
            If @debugMode = 1 RaisError('  Looking up the specifics for our index...', 0, 42) With NoWait;
 
            /* Look up index information */
            Select @updateSQL = N'Update ids
                Set schemaName = QuoteName(s.name)
                    , objectName = QuoteName(o.name)
                    , indexName = QuoteName(i.name)
                From dbo.dba_indexDefragStatus As ids
                Inner Join ' + @databaseName + '.sys.objects As o
                    On ids.objectID = o.object_id
                Inner Join ' + @databaseName + '.sys.indexes As i
                    On o.object_id = i.object_id
                    And ids.indexID = i.index_id
                Inner Join ' + @databaseName + '.sys.schemas As s
                    On o.schema_id = s.schema_id
                Where o.object_id = ' + Cast(@objectID As varchar(10)) + '
                    And i.index_id = ' + Cast(@indexID As varchar(10)) + '
                    And i.type > 0
                    And ids.databaseID = ' + Cast(@databaseID As varchar(10));
 
            Execute sp_executesql @updateSQL;
 
            /* Grab our object names */
            Select @objectName  = objectName
                , @schemaName   = schemaName
                , @indexName    = indexName
            From dbo.dba_indexDefragStatus
            Where objectID = @objectID
                And indexID = @indexID
                And databaseID = @databaseID;
 
            If @debugMode = 1 RaisError('  Grabbing the partition count...', 0, 42) With NoWait;
 
            /* Determine if the index is partitioned */
            Select @partitionSQL = 'Select @partitionCount_OUT = Count(*)
                                        From ' + @databaseName + '.sys.partitions
                                        Where object_id = ' + Cast(@objectID As varchar(10)) + '
                                            And index_id = ' + Cast(@indexID As varchar(10)) + ';'
                , @partitionSQL_Param = '@partitionCount_OUT int OutPut';
 
            Execute sp_executesql @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OutPut;
 
            If @debugMode = 1 RaisError('  Seeing if there are any LOBs to be handled...', 0, 42) With NoWait;
 
            /* Determine if the table contains LOBs */
            Select @LOB_SQL = ' Select @containsLOB_OUT = Count(*)
                                From ' + @databaseName + '.sys.columns With (NoLock) 
                                Where [object_id] = ' + Cast(@objectID As varchar(10)) + '
                                   And (system_type_id In (34, 35, 99)
                                            Or max_length = -1);'
                                /*  system_type_id --> 34 = image, 35 = text, 99 = ntext
                                    max_length = -1 --> varbinary(max), varchar(max), nvarchar(max), xml */
                    , @LOB_SQL_Param = '@containsLOB_OUT int OutPut';
 
            Execute sp_executesql @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OutPut;
 
            If @debugMode = 1 RaisError('  Checking for indexes that do not allow page locks...', 0, 42) With NoWait;
 
            /* Determine if page locks are allowed; for those indexes, we need to always rebuild */
            Select @allowPageLockSQL = 'Select @allowPageLocks_OUT = Count(*)
                                        From ' + @databaseName + '.sys.indexes
                                        Where object_id = ' + Cast(@objectID As varchar(10)) + '
                                            And index_id = ' + Cast(@indexID As varchar(10)) + '
                                            And Allow_Page_Locks = 0;'
                , @allowPageLockSQL_Param = '@allowPageLocks_OUT int OutPut';
 
            Execute sp_executesql @allowPageLockSQL, @allowPageLockSQL_Param, @allowPageLocks_OUT = @allowPageLocks OutPut;
 
            If @debugMode = 1 RaisError('  Building our SQL statements...', 0, 42) With NoWait;
 
            /* If there's not a lot of fragmentation, or if we have a LOB, we should reorganize */
            If (@fragmentation < @rebuildThreshold Or @containsLOB >= 1 Or @partitionCount > 1)
                And @allowPageLocks = 0
            Begin
 
                Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' 
                                    + @schemaName + N'.' + @objectName + N' ReOrganize';
 
                /* If our index is partitioned, we should always reorganize */
                If @partitionCount > 1
                    Set @sqlCommand = @sqlCommand + N' Partition = ' 
                                    + Cast(@partitionNumber As nvarchar(10));
 
            End
            /* If the index is heavily fragmented and doesn't contain any partitions or LOB's, 
               or if the index does not allow page locks, rebuild it */
            Else If (@fragmentation >= @rebuildThreshold Or @allowPageLocks <> 0)
                And IsNull(@containsLOB, 0) != 1 And @partitionCount <= 1
            Begin
 
                /* Set online rebuild options; requires Enterprise Edition */
                If @onlineRebuild = 1 And @editionCheck = 1 
                    Set @rebuildCommand = N' Rebuild With (Online = On';
                Else
                    Set @rebuildCommand = N' Rebuild With (Online = Off';
 
                /* Set sort operation preferences */
                If @sortInTempDB = 1 
                    Set @rebuildCommand = @rebuildCommand + N', Sort_In_TempDB = On';
                Else
                    Set @rebuildCommand = @rebuildCommand + N', Sort_In_TempDB = Off';
 
                /* Set processor restriction options; requires Enterprise Edition */
                If @maxDopRestriction Is Not Null And @editionCheck = 1
                    Set @rebuildCommand = @rebuildCommand + N', MaxDop = ' + Cast(@maxDopRestriction As varchar(2)) + N')';
                Else
                    Set @rebuildCommand = @rebuildCommand + N')';
 
                Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.'
                                + @schemaName + N'.' + @objectName + @rebuildCommand;
 
            End
            Else
                /* Print an error message if any indexes happen to not meet the criteria above */
                If @printCommands = 1 Or @debugMode = 1
                    RaisError('We are unable to defrag this index.', 0, 42) With NoWait;
 
            /* Are we executing the SQL?  If so, do it */
            If @executeSQL = 1
            Begin
 
                Set @debugMessage = 'Executing: ' + @sqlCommand;
 
                /* Print the commands we're executing if specified to do so */
                If @printCommands = 1 Or @debugMode = 1
                    RaisError(@debugMessage, 0, 42) With NoWait;
 
                /* Grab the time for logging purposes */
                Set @dateTimeStart  = GetDate();
 
                /* Log our actions */
                Insert Into dbo.dba_indexDefragLog
                (
                      databaseID
                    , databaseName
                    , objectID
                    , objectName
                    , indexID
                    , indexName
                    , partitionNumber
                    , fragmentation
                    , page_count
                    , dateTimeStart
                    , sqlStatement
                )
                Select
                      @databaseID
                    , @databaseName
                    , @objectID
                    , @objectName
                    , @indexID
                    , @indexName
                    , @partitionNumber
                    , @fragmentation
                    , @pageCount
                    , @dateTimeStart
                    , @sqlCommand;
 
                Set @indexDefrag_id = Scope_Identity();
 
                /* Wrap our execution attempt in a try/catch and log any errors that occur */
                Begin Try
 
                    /* Execute our defrag! */
                    Execute sp_executesql @sqlCommand;
                    Set @dateTimeEnd = GetDate();
 
                    /* Update our log with our completion time */
                    Update dbo.dba_indexDefragLog
                    Set dateTimeEnd = @dateTimeEnd
                        , durationSeconds = DateDiff(second, @dateTimeStart, @dateTimeEnd)
                    Where indexDefrag_id = @indexDefrag_id;
 
                End Try
                Begin Catch
 
                    /* Update our log with our error message */
                    Update dbo.dba_indexDefragLog
                    Set dateTimeEnd = GetDate()
                        , durationSeconds = -1
                        , errorMessage = Error_Message()
                    Where indexDefrag_id = @indexDefrag_id;
 
                    If @debugMode = 1 
                        RaisError('  An error has occurred executing this command! Please review the dba_indexDefragLog table for details.'
                            , 0, 42) With NoWait;
 
                End Catch
 
                /* Just a little breather for the server */
                WaitFor Delay @defragDelay;
 
                Update dbo.dba_indexDefragStatus
                Set defragDate = GetDate()
                    , printStatus = 1
                Where databaseID       = @databaseID
                  And objectID         = @objectID
                  And indexID          = @indexID
                  And partitionNumber  = @partitionNumber;
 
            End
            Else
            /* Looks like we're not executing, just printing the commands */
            Begin
                If @debugMode = 1 RaisError('  Printing SQL statements...', 0, 42) With NoWait;
 
                If @printCommands = 1 Or @debugMode = 1 
                    Print IsNull(@sqlCommand, 'error!');
 
                Update dbo.dba_indexDefragStatus
                Set printStatus = 1
                Where databaseID       = @databaseID
                  And objectID         = @objectID
                  And indexID          = @indexID
                  And partitionNumber  = @partitionNumber;
            End
 
        End
 
        /* Do we want to output our fragmentation results? */
        If @printFragmentation = 1
        Begin
 
            If @debugMode = 1 RaisError('  Displaying a summary of our action...', 0, 42) With NoWait;
 
            Select databaseID
                , databaseName
                , objectID
                , objectName
                , indexID
                , indexName
                , partitionNumber
                , fragmentation
                , page_count
                , range_scan_count
            From dbo.dba_indexDefragStatus
            Where defragDate >= @startDateTime
            Order By defragDate;
 
        End;
 
    End Try
    Begin Catch
 
        Set @debugMessage = Error_Message() + ' (Line Number: ' + Cast(Error_Line() As varchar(10)) + ')';
        Print @debugMessage;
 
    End Catch;
 
    /* When everything is said and done, make sure to get rid of our temp table */
    Drop Table #databaseList;
    Drop Table #processor;
    Drop Table #maxPartitionList;
 
    If @debugMode = 1 RaisError('DONE!  Thank you for taking care of your indexes!  :)', 0, 42) With NoWait;
 
    Set NoCount Off;
    Return 0
End

You can also download it here: dba_indexDefrag_sp_v40_public.txt

I’ve had this latest version in production on terabyte-size databases running SQL Server 2005 and 2008 Enterprise editions for the last 3 months, where it runs nightly without issue. I’ve also had numerous beta testers report success in their environments, too. But to be safe, make sure to keep an eye on it the first time it runs to ensure you understand the impact on your server.

Enjoy!

Michelle

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , , , , , , , , . Bookmark the permalink.

98 Responses to Index Defrag Script, v4.0

  1. Pingback: Index Defrag Script, v3.0 : SQL Fool

  2. Pingback: uberVU - social comments

  3. Pingback: Tweets that mention Index Defrag Script, v4.0 : SQL Fool -- Topsy.com

  4. Roy says:

    Michelle,

    Congrats on your new born and thanks for your new script!

  5. Pingback: Something for the weekend – SQL Server links for the week 23/04/10 | John Sansom - SQL Server DBA in the UK

  6. Nadrek says:

    Congratulations on including @endDate; that’s a rare step in and of itself. Have you considered doing estimates on how long it is likely to take to run the next step, as well, to prevent (for instance) a multi-hour, dozens of millions of rows, wide table from starting to be rebuilt (offline, for instance) only half an hour before the end time?

    I might suggest separating out clustered from nonclustered (since they operate at different speeds, clustered being faster on my tests), and allow the passing in of “initial” speed guesses, which are used for the first N seconds (10 seconds, in my example), after which a cumulative average speed to date is used. Pages per second seems to be the least unreliable measure of speed in SQL Server 2000, for defragging indexes (retrusting constraints is another story entirely). In the case of the rebuild threshold being >0, rebuild vs reorganize will also operate at different speeds.

    Also, have you considered including low Scan Density as an optional parameter for triggering rebuilds (which will increase the scan density)?

    As a last pair of suggestions, I’d say for ordering, include an option for range_scan_count*fragmentation; get the most used, most heavily fragmented tables first. And for the log, include the current Fill Factor, so when you see a given index showing up too often, you can easily see what the fill factor has been each time (and you can track what fill factor changes do to the fragmentation).

    Congratulations, also, on including min and max page count; I find doing small index maintenance daily and larger index maintenance on less frequent schedules to fit with the maintenance windows I’m given.

    My personal opinion is that when one is given a fixed maintenance window during which severe performance limits and table locking is perfectly OK, there’s little reason to waste any of it. Do the most important maintenance first, and if there’s time left, do more maintenance to further optimize the system.

  7. Tom Hamilton says:

    Thank you for and excellent tool – great work and congratulations on your great looking little girl. Grandbabies are cool too!

  8. Lawrence says:

    Awesome updated script!

    “- For various reasons, I’ve removed the option to rebuild stats. ”

    Can you say the reasons why the rebuild stats was removed from this version (4.0)?

  9. Andris Marte says:

    Excellent Tools, Excelente herramienta, For Me as New DBA Jr,…. From Santo Domingo, Republica Dominicana

  10. @Nadrek Thank you for your suggestions. I’ll definitely consider them when I work on the next version. :)

  11. @Lawrence Sure. There was a bug in the previous version with the rebuild, although it’s been so long since I looked at it that I don’t recall the details. I then made it just rebuild stats for each database, but the feedback I received was that it could cause problems in some environments. I didn’t want that to hold up the release, so I just removed it. I’ll most likely add it back in the next version.

  12. ricky lively says:

    I see where it says the timelimit is up, but don’t see how it stops the loop…

  13. Lawrence says:

    Great. Thks!

  14. Paul Clark says:

    Excellent Script!

    As an aside I inherited some DB’s that stupidly have four zero’s as a prefix on their name, ie 0000_DB_Name, this caused some issues. Amended one line of the script to resolve the error:

    /* Do we want to exclude right-most populated partition of our partitioned indexes? */
    IF @excludeMaxPartition = 1
    BEGIN

    SET @excludeMaxPartitionSQL = ‘
    Select ‘ + CAST(@databaseID AS VARCHAR(10)) + ‘ As [databaseID]
    , [object_id]
    , index_id
    , Max(partition_number) As [maxPartition]
    From [' + DB_NAME(@databaseID) + '].sys.partitions
    Where partition_number > 1
    And [rows] > 0
    Group By object_id
    , index_id;’;

    As you can see I added square brackets to the From ‘ + DB_NAME(@databaseID) + ‘.sys.partitions

    Thanks for the script Michelle!!! Can always count on you to help us busy DBA’s out! ;0)

  15. Paul Clark says:

    Great Script Michelle. Thank You.

    Just a quick thing though for anyone who is experiencing an issue with DB’s with numbers prefixing DB names…..
    I have unfortunately inherited some DB’s that have 4 zero’s as a prefix. IE 0000_DB_Name.

    I changed the following part of the script to allow for this:

    /* Do we want to exclude right-most populated partition of our partitioned indexes? */
    IF @excludeMaxPartition = 1
    BEGIN

    SET @excludeMaxPartitionSQL = ‘
    Select ‘ + CAST(@databaseID AS VARCHAR(10)) + ‘ As [databaseID]
    , [object_id]
    , index_id
    , Max(partition_number) As [maxPartition]
    From [' + DB_NAME(@databaseID) + '].sys.partitions
    Where partition_number > 1
    And [rows] > 0
    Group By object_id
    , index_id;’;

    INSERT INTO #maxPartitionList
    EXECUTE SP_EXECUTESQL @excludeMaxPartitionSQL;

    As you can see I’ve amended the line
    From ‘ + DB_NAME(@databaseID) + ‘.sys.partitions
    and added Square Brackets.

  16. Dennis says:

    There is a bug if you run the script twice. Then it complains about the names of the clustered indexes having duplicate names

  17. Dennis says:

    >As a last pair of suggestions, I’d say for ordering, include an option for range_scan_count*fragmentation;

    The fix for this is easy…

    , @defragOrderColumn nvarchar(20) = ‘range_scan_count’
    –>
    , @defragOrderColumn nvarchar(200) = ‘range_scan_count’

    and then delete

    Or @defragOrderColumn Not In (‘range_scan_count’, ‘fragmentation’, ‘page_count’)

    Now you can do @defragOrderColumn = ‘range_scan_count * range_scan_count * fragmentation’ or whatever you want as your calculation

  18. Pingback: SQL University - Tools of the Trade | SQLRockstar

  19. mina says:

    really stupid question! we have installed this to our DBA database named, appropriately “DBA”

    how does the stored procedure “know” where the Index itself resides?

    when we did some testing it didn’t look like the indexes were getting updated…???

  20. Wayne Jurecki says:

    Michelle,

    Thanks, great script. But, I found a situation where it appears not to work properly.

    I am seeing that sys.dm_db_index_operational_stats does not return information for XML indexes and since you do an inner join on this and sys.dm_db_index_physical_stats my XML indexes are not being included in any index maintenance.

    I have found that simply changing the join to a left join and therefore relying only on sys.dm_db_index_physical_stats for the source of index information my XML indexes get included. This is not without its flaws. Specifically, if I sort the indexing operation by range_scan_count the XML indexes will be done last because of the null range_scan_count due to the left join mentioned.

  21. @Ricky On line 621, if the time limit is exceeded, I raise an error, which exits the TRY block and continues to the CATCH block.

    Thanks, @Paul and @Dennis… I’ll take a look at implementing those in the next version. :)

    @Mina No worries, I don’t believe in stupid questions. :) The stored procedure “knows” where the index resides because of the database_id in sys.dm_db_index_physical_stats. If you don’t pass it a database_id, it will return information on all indexes on the server. If your indexes weren’t being updated, make sure to check the size of the indexes; by default, the script doesn’t defrag indexes with less than 8 pages (1 extent), so indexes on small tables will usually be untouched. HTH.

    @Wayne Thanks. I’ve actually never had the opportunity to work with XML indexes, so I didn’t test for that. I’ll take a look at fixing in the next version. :)

  22. Jon Sells says:

    I ran into a case sensitive issue with the XP_MSVER and SP_EXECUTESQL command. Our database uses Server collation: SQL_Latin1_General_CP850_BIN2 and is therefore case sensitive.

    Otherwise, the script is great! Thanks.

  23. Jim says:

    thanks for the script. nice picture. Just be prepared, she’ll be mad at you when she is 14 for posting a picture of her naked. ( the voice of experience)

  24. pjdiller says:

    Scratch my question. Must have been something I changed while modifying for case. It’s working.

  25. Gabriel says:

    I got this error when trying to create the SP. I haven’t looked at what’s wrong yet.

    “Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 440
    Incorrect syntax near ‘Object_Id’.”

  26. Gabriel says:

    Ignore my previous post. My database was in 2000 mode, not 2005

  27. Panish says:

    Does anybody tested this script against Share Point databases? I really appreciate your input as I’m waiting to get confirmation from the users who used it this script to defrag Share point database Indexes.

    Thank you
    Panish

  28. Ron Firth says:

    If your’re looking for ideas then you could utilise the output defrag information table to identigy those that are commonly fragmented and find the hotspot tables

  29. This script contains capitalization errors that make it not work on databases/servers with binary collations. I’ve not looked through the extent of the problems but just in the first few lines it starts with SP_RENAME and that proc is actually called sp_rename.

  30. @Panish I’ve not personally tested this on SharePoint, but I’ve heard that it works just fine.

    @Emil That’s caused by the WordPress code plugin, which is why I included a link to a downloadable file. See above at the very end of the post for the link.

  31. Thanks :) Not the best plug-in if it reformats your code so it doesn’t work.

  32. So, I encountered an issue. Perhaps not a huge deal, but it gave me the impression of the script being broken until I found out what was happening.
    Might be a huge deal for some, if they suddenly start rebuilding stuff in their production DB while they intended to work on another one.
    The scenario.

    1. I scan database A but do not defrag it.
    2. I run the proc on database B, *not* with force rebuild on.
    3. I observe that the output says it’s working on database A.

    The reason:
    IF Not Exists(SELECT TOP 1 * FROM dbo.dba_indexDefragStatus WHERE defragDate IS Null)
    Or @forceRescan = 1

    Even if you change DB names, it works on the old one if there are indexes it has not maintained yet.

  33. Adriaan Van Bauwel says:

    I’ve had the same error Paul mentioned, which was slved by the same fix (placing square brackets around ‘ DB_Name(@databaseID) ‘ )
    This failed this script with a database name that had a minus ( – ) sign in the name, common in Sharepoint / WSS environments

    Other then that it seems to work perfectly for me … :) :)

    Thanks for the great work!

  34. Montrial says:

    Michelle – Thank you so much for writing this script. It is awesome!! I like a couple of others had to add some brackets because my developers used spaces in a couple of database names but that was the easy part. My question is about the rebuild online setting. It looks like the stored procedure is tailored to reorg indexes that need to be rebuilt if @onlineRebuild = 1. If I set the variable to zero will the sp rebuild all sp offline or just those that need to be taken offline. Again, thanks for the work you put into this script…=0)

  35. Laurie says:

    I got this error when trying to create the SP.
    “Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 440
    Incorrect syntax near ‘Object_Id’.”

  36. Brent Ozar says:

    Awesome script, been using it since you made the first one public. Here’s a script to generate histograms of how fragmented your indexes have been over time:

    SELECT databaseName, COUNT(*) AS Touches, AVG(durationSeconds * 1.00) AS AvgDuration, SUM(durationSeconds) AS TotalDuration, AVG(fragmentation) AS AvgFragmentation
    , COUNT(CASE WHEN fragmentation BETWEEN 0 AND 10 THEN 1 END) AS Frag10
    , COUNT(CASE WHEN fragmentation BETWEEN 10 AND 20 THEN 1 END) AS Frag20
    , COUNT(CASE WHEN fragmentation BETWEEN 20 AND 30 THEN 1 END) AS Frag30
    , COUNT(CASE WHEN fragmentation BETWEEN 30 AND 40 THEN 1 END) AS Frag40
    , COUNT(CASE WHEN fragmentation BETWEEN 40 AND 50 THEN 1 END) AS Frag50
    , COUNT(CASE WHEN fragmentation BETWEEN 50 AND 60 THEN 1 END) AS Frag60
    , COUNT(CASE WHEN fragmentation BETWEEN 60 AND 70 THEN 1 END) AS Frag70
    , COUNT(CASE WHEN fragmentation BETWEEN 70 AND 80 THEN 1 END) AS Frag80
    , COUNT(CASE WHEN fragmentation BETWEEN 80 AND 90 THEN 1 END) AS Frag90
    , COUNT(CASE WHEN fragmentation BETWEEN 90 AND 100 THEN 1 END) AS Frag100
    FROM master.dbo.dba_indexDefragLog
    WHERE dateTimeStart > ’7/1/2010′
    GROUP BY databaseName
    ORDER BY databaseName

    I use this to figure out whether I’m using the right percentages for defrag/rebuild, and whether I need to investigate a particular database’s indexes because they’re getting rebuilt too often.

  37. Hi Michelle, thanks very much for this really useful script.

  38. Robert Sharkey says:

    To those with the ‘Object_ID’ error: likely you are trying to create the sproc on SQL Server 2000, or the database you are trying to create it in is Compatibility level 80 (SQL Server 2000).

  39. Jeff Kelly says:

    Great script! I started on something like this, but then found your solution. No chance i could of built something even remotely as good!!

    My solution wasn’t to defrag however, but only to monitor/provide history.

    I modified your proc slightly so that even if the execute is not happening, its still adding the results of the scan into the Log table.

    This way I can run it daily (hourly, or whatever) to get an idea of how fast an index is degrading.

    The only issue is that its hard to capture how long it takes for the scan to actually finish, so the dateTimeEnd field isn’t quite as useful, but i’ll play with that.

    again, thanks for the great utility!

  40. gary says:

    Hi Michelle,

    We have 20 databases and out of them only 5 databases required to do Index defrag.

    Is there any option in the above script to select multiple databases?

    I can see that we can give either one database or all databases

    @DATABASE VARCHAR(128) = Null
    /* Option to specify a database name; null will return all */

    Thanks

  41. gary says:

    hi,

    I just tried to pass two databases as below

    @DATABASE = ‘Mydb1′,’Mydb2′

    But getting the below error:

    Msg 119, Level 15, State 1, Line 1
    Must pass parameter number 8 and subsequent parameters as ‘@name = value’. After the form ‘@name = value’ has been used, all subsequent parameters must be passed in the form ‘@name = value’.

    Thanks

  42. Pingback: SQL Server Defrag Script

  43. Mark says:

    Hello,

    I have been using this script against various servers with exceptional results. However, when I run it on a server that hosts a database whose name contains a guid, it appears to be blowing up whilst scanning for partitions because the database name contains “-”.

    working on _85992528-be67-4058-94f2-f89ddaa987c4…
    Incorrect syntax near ‘-’. (Line Number: 6)
    DONE! Thank you for taking care of your indexes! :)

    I was able to get around this by using the QUOTENAME function when concatenating the db name to sys.partitions.

  44. gary says:

    Can we pass multiple tables for a particular Database?

    Thanks

  45. pjdiller says:

    I’ve been using this *with much gratitude* for a while now. I am noticing a snag when I am defragging very large databases. If I run it once a week, let’s say… instead of every night, some of the indexes that were originally scanned no longer exist the next week. I believe this is why I’m getting an error when debugging:

    Cannot insert the value NULL into column ‘objectName’, table ‘master.dbo.dba_indexDefragLog’; column does not allow nulls. INSERT fails. (Line Number: 708) [SQLSTATE 01000]

    I think it’s a good idea to make sure that what was originally scanned is still around. Maybe I’m wrong about what’s happening though. Am I missing something in the code?

  46. Why are tables with a LOB always reorganized (regardless of the fragmentation)?
    If the fragmentation is above the treshold, I suggest to switch to offline rebuilding. Or do you have a KB article about this? ;)

  47. It seems the script is first looking into the indexdefragstatus table to process unhandled indexes. But if this script is only running on some specific days, I would like to discard outdated data about those indexes (because another index might be more fragmented)

    My suggestion is to add another parameter which is a treshold for how long unprocessed indexes will stay in this table. Delete outdated data before processing based on this parameter

  48. Roland says:

    I have indexes with long names. This script ends with an error String or binary data would be truncated. I fixed this by increasing the NVARCHAR’s (in variables and tables) currently limited to 128 characters. After this it runs fine. Maybe an idea to include this in the next version?

  49. Dugi says:

    Great stuff here …thanks for sharing!

  50. Pingback: Aaron Bertrand : Useful, free resources for SQL Server

  51. Pingback: free SQL server resources « xunyangit's Blog

  52. Andris Marte says:

    hi guys,
    Previously used this great script, but now I have my Mirror database enabled and I can’t use it, because this scrip change Recovery Model, and the mirror I need Full Recovery Model on all the time. You can help me change this scrip to use it and not have to change the Recovery Model.

  53. Pingback: Agresso Database Servers – Maintenance Checklist | The Lone DBA

  54. Steven says:

    Hi everyone, I am an IT Admin at a small office. I have little to no dba experience and I found this script to help defrag and rebuild index if necessary. Thank you Michelle for sharing such a wonderful script. However, when I paste this script and tried to run it, I get these errors. I didn’t edit anything, just copied and paste into an sql query and execute.

    Caution: Changing any part of an object name could break scripts and stored procedures.
    Caution: Changing any part of an object name could break scripts and stored procedures.
    Caution: Changing any part of an object name could break scripts and stored procedures.
    dba_indexDefragLog Table Created
    dba_indexDefragExclusion Table Created
    Msg 2714, Level 16, State 4, Line 43
    There is already an object named ‘PK_indexDefragStatus_v40′ in the database.
    Msg 1750, Level 16, State 0, Line 43
    Could not create constraint. See previous errors.
    Msg 2714, Level 16, State 3, Procedure dba_indexDefrag_sp, Line 834
    There is already an object named ‘dba_indexDefrag_sp’ in the database.

    It always runs the first time creating the tables only, then reports that it has been completed. However, I know there are a lot of fragmentation on my database. Any help is greatly appreciated. I need to get this to run asap for everyone in the office is complaining that it is slow.

    Congratulations Michelle. I just had my first baby girl as well. :)

  55. Pingback: The Kinetic Serendipity of the Written Word « NULLgarity

  56. Pingback: SQL Server 2000 tables

  57. Nigel says:

    Michelle,

    I’ve made a slight change to the script which others may find useful. It basically allows you to specify a comma separated list of databases in the ‘@database’ parameter.
    Where the database list is populated around line 400 I’ve added to the where clause as follows:

    WHERE
    (
    name = IsNull(@database, name)
    OR
    @database + ‘,’ LIKE ‘%’ + name + ‘,%’
    )
    AND … (rest of original where clause)

  58. Michelle,

    This post is simply AWESOME, i didn’t know about this post until today, but now i’m using and it is very useful and indeed.

    Congrats for this post.

  59. David says:

    I am having an issue when i run this and it throws an error when it it’s a read only database I have that is used for reporting. It uses log shipping. Is there away to have the procedure skip it?

  60. Adriano says:

    Hi, I apologize for my English (I’m Italian). I want to know how to use the script and the extension.
    Can I use it on a database maintained by iAnywhere?
    Thanks

  61. Pingback: Script to UPDATE STATISTICS with time window | Dark Blog

  62. Piroc says:

    Hi Michelle,

    First, thank you for your great script :-)

    Quickly I have a question:
    ==================
    I wanted to create the tables into my DBA database, which caused no problem at all.
    Then I wanted to rename the tables to be compliant with our naming convention, which caused no problem at all.
    Finally I wanted to rename the columns to be compliant with our naming convention, which caused SOME problems.
    Can you confirm that there is only one column for which the name cannot be changed: “range_scan_count” in the status table?

    And I have one tiny tiny bug:
    When reinstalling your “software”, the primary key constraints are causing a problem when trying to recreate them.
    As you can read, this is definetely not an issue, but in case you are as perfectionist as I am, i thought i’ll let you know :-)

    Thanks again

    Regards,
    Pierre

  63. Robin says:

    Hi, I’m trying your script out and have an initial comment to make. I have set @executeSQL=0 and @printCommands=1 in order to see what it’s going to do. What confused me for a while though was that on running it a second time, the ALTER statements did not appear. Whilst I understand this is because the statements have already gone into the Status table. My expectation was that setting executeSQL to 0 would mean that it did “nothing” and therefore ran the same each time.

  64. @Steven Running the script above just builds the objects. In order to actually execute the script, you need to run:
    Exec dbo.dba_indexDefrag_sp;

    @David I’ll have to investigate that error when I work on revisions.

    @Adriano I can’t say for sure, but if it’s a SQL Server database, you should be able to use it successfully.

    @Piroc Thanks. I did that so I wouldn’t inadvertently overwrite historical log tables that folks may want to keep. But I’ll fix the script in future versions to look to see if those tables exist first before trying to recreate them. As for the range_scan_count column, I believe you should be able to change the column names. My guess is you tried to do a find/replace on that column name, but the @defragOrderColumn value must remain range_scan_count, even if you change the column name in the table.

    HTH. Thanks, everyone. :)

    Michelle

    @Robin Thanks for the head’s up. I’d expect it to function the way you were expecting, too. I’ll take a look and see about adding it to the bugs list. :)

  65. thank you so much for this important tool.. It’s saving my life hehe.. :)

    So.. What do you think about if we could put which tables we are wanting to Defrag as a parameter? because some times we have just a few hours to can defrag all the database, thus, if we can choice the tables for defrag, it’s easier..

    Marcos Freccia

  66. Pingback: Robots Blog » SQL Server Scripts I Use

  67. Pingback: SQL Server Scripts I Use | Blue Water Blog

  68. Pingback: SQL Server Scripts I Use | Dark Blog

  69. Pingback: SQL Server Database Maintenance for the CRM Developer « Pogo69's Blog

  70. Pingback: 5 Things Every DBA Should NOT Do | John Sansom - SQL Server DBA in the UK

  71. Jason Spatz says:

    Silly question .. do most of you up the page count parameter well over the default? I find this script ends up trying to reorganize the same index (getting no real results) day after day because the frag level doesn’t go down on the smaller indexes.. It runs through them super fast, so its not a performance thing but I am trying to look in the defraglog table and there is a lot of duplicate repeating stuff that sometimes makes it a chore to sift through.

  72. Pingback: 5 Things Every DBA Should NOT Do | Nobel Software Development

  73. Pingback: SQL Server Central

  74. Stuart says:

    Very good script, but noticed that dba_indexDefrag_sp errors if it comes across a database name with a dash “-” symbol in it.

    Probably need to escape the databasename in [ ] brackets.

    Looping through our list of databases and checking for fragmentation…
    working on model…
    working on msdb…
    working on AAAA-YYYYYYY…
    Incorrect syntax near ‘-’. (Line Number: 6)
    DONE! Thank you for taking care of your indexes! :)

  75. Pingback: SQLU DBA Week – Set It And…. | StraightPath Consulting's SQL Server Blog

  76. Pingback: Database Maintenance | SQL RNNR

  77. Pat B says:

    If an index does not meet the rebuild or reorganize criteria, is there a way to just update statistics? I looked but didn’t see any option for this in your script. Is it something that could or should be added?

  78. Robert says:

    I’m having some trouble with this script. I’m a relatively new DBA so please bear with me. It appears to me that the script is not defragging all of the databases that it should be.

    Before running the script, I queried the DMVs for all indexes on a particular database that have more than 8 pages and 10 or more percent fragmentation. It came back with 212 indexes. This database is on a test server and maintenance hasn’t been done on it for a while. When I run the script on the same database, it says there are 86 indexes to defrag.

    After the script has finished, I check the DMVs and it appears that the script did what it said it would — there are now about 140 indexes left that need to be defragged. So I run the script again with the same parameters. This time it finds 34 indexes to defrag.

    When the script finishes, I check the DMVs again and now I have about 110 indexes left to defrag. So it appears that the script is defragging indexes, but I’m wondering why it isn’t choosing to defrag all 212 in the first round.

    Here’s the text that shows the parameters I have chosen when the script starts:

    Defrag indexes with fragmentation greater than 10;
    Rebuild indexes with fragmentation greater than 30;
    You DO want the commands to be executed automatically;
    You want to defrag indexes in DESC order of the RANGE_SCAN_COUNT value;
    You have specified a time limit of 720 minutes;
    The mdb database will be defragged;
    ALL tables will be defragged;
    We WILL be rescanning indexes;
    The scan will be performed in LIMITED mode;
    You want to limit defrags to indexes with more than 8 pages;
    Indexes will be defragged OFFLINE;
    Indexes will be sorted in TEMPDB;
    Defrag operations will utilize system defaults for processors;
    You DO NOT want to print the ALTER INDEX commands;
    You DO want to output fragmentation levels;
    You want to wait 00:00:05 (hh:mm:ss) between defragging indexes;
    You want to run in DEBUG mode.

  79. Mimi says:

    Ok…maybe it is just me, but I cannot get the script to compile in my database. I am getting “Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 440
    Incorrect syntax near ‘Object_Id’.”

    Did anyone else have this problem. I have been up several hours trying to work on the problem myself before I found this blog so I am sure I am just looking over something. Please help…thanks in advance

  80. Pingback: UniverSQL » Index Optimisation for Very Large Databases (VLDBs)

  81. Maneesh says:

    Hi Michelle,
    Thanks for such a good script.

    I have a big DWH database server on which ETL jobs is not allowing to run DBM uniformally on all databases.Now i have to run DBM according to ETL schedule.
    Can this be possible to run above script at a particular schedule on particular time.
    I dont want to create multiple sql job for multiple databases.
    Only one job which call that SP and check which database/table has a schedule to run and perform its task.
    If that is possible than it would be a great relief for me as its very difficult to manage many jobs.

    Thanks in Advance…

    Regards,
    Maneesh

  82. Terry says:

    I get the same Msg 102, Level 15, State 1, Procedure dba_indexDefrag_sp, Line 440
    Incorrect syntax near ‘OBJECT_ID’.
    My version of SQL is : Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: )

    Many thanks.

  83. Pingback: How do you want your index? | Verity

  84. Nasi Peretz says:

    Amazing script and I would love to run it but I’m getting an error… below is my debug mode, note the “string or binary data would be truncated” error

    Undusting the cogs and starting up…
    Beginning validation…
    Your selected parameters are…
    Defrag indexes with fragmentation greater than 10;
    Rebuild indexes with fragmentation greater than 30;
    You DO want the commands to be executed automatically;
    You want to defrag indexes in DESC order of the PAGE_COUNT value;
    You have not specified a time limit; minutes;
    The LaughStub database will be defragged;
    ALL tables will be defragged;
    We WILL be rescanning indexes;
    The scan will be performed in LIMITED mode;
    You want to limit defrags to indexes with more than 8 pages;
    Indexes will be defragged ONLINE;
    Indexes will be sorted in TEMPDB;
    Defrag operations will utilize 1 processors;
    You DO want to print the ALTER INDEX commands;
    You DO want to output fragmentation levels;
    You want to wait 00:00:05 (hh:mm:ss) between defragging indexes;
    You want to run in DEBUG mode.
    Grabbing a list of our databases…
    Looping through our list of databases and checking for fragmentation…
    working on LaughStub…
    Looping through our list… there are 72 indexes to defrag!
    Picking an index to beat into shape…
    Looking up the specifics for our index…
    String or binary data would be truncated. (Line Number: 1)
    DONE! Thank you for taking care of your indexes! :)

  85. Christophe says:

    Hi,

    First, thanks for this very good script!!!!!!

    I just have a small problem, when I run it, I receive an error:

    Incorrect syntax near ‘-’. (Line Number: 6)

    I thinks it a problem with a DB name containing a “-”.

    Of course, I can’t change the name of this DB, it would be too easy ;-)

    Could you help me?

    Thanks.

    Regards, Christophe

  86. RCP says:

    THank you for developing this script. It has helped quite a bit in my work.

    I am running into one issue that is problematic. I can run this script without issue when logged into SSMS but when I put the script into a SQL Server 2005 Maintenance Plan I always get the following error:

    Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1).

    I have tried setting up various connections etc to no avail. Any help woudl be appreciated.

    Thank you

  87. MF4 says:

    Hello,

    Thank you for the script,

    My problem is that the script run for 2:56:46 then failed.

    Date 5/27/2011 10:00:00 PM
    Log Job History (Index Defrag)

    Step ID 1
    Server *\*
    Job Name Index Defrag
    Step Name Run Script
    Duration 02:56:46
    Sql Severity 0
    Sql Message ID 50000
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Executed as user: *. …ssage 50000) Beginning validation… [SQLSTATE 01000] (Message 50000) Your selected parameters are… Defrag indexes with fragmentation greater than 1; Rebuild indexes with fragmentation greater than 30; You DO want the commands to be executed automatically; You want to defrag indexes in DESC order of the PAGE_COUNT value; You have not specified a time limit; minutes; ALL databases will be defragged; ALL tables will be defragged; We WILL be rescanning indexes; The scan will be performed in LIMITED mode; You want to limit defrags to indexes with more than 8 pages; Indexes will be defragged OFFLINE; Indexes will be sorted in TEMPDB; Defrag operations will utilize system defaults for processors; You DO want to print the ALTER INDEX commands; You DO want… The step failed.

    Any direction you can point me to ?

    Thank you,

  88. Pingback: Database Oil Changes – Part 2 | Art of the DBA

  89. Rob Johnson says:

    Hi, have been testing this script, and have not really noticed a drop in fragmentation on a table with an index with a page count > 20 and fragmention percent > 30, but when I manually ran the ALTER INDEX command on this index with a FILLFACTOR = 1 statement included, the fragmentation level dropped to nearly 0. Have you ever considered adding logic to include a FILLFACTOR in your REBUILD INDEX logic?

  90. Ninja RgR'us says:

    Just a quick note to say I love this script and recommended it over 30 times over on sqlservercentral.com

    I’m currently working on a fill factor rework project and I’m using your log tables to pick the best candidates for changes. My “issue” is that you log everything EXCEPT the schema so it makes it a little hard to build my own commands from the history. I know it’s in the command column but since I’ll be redoing a couple 100 indexes I’d rather not have to work that hard to get it!

    TIA.

  91. Spaceman says:

    am i correct in thinking that the current script will use a default fillfactor of 0?… is so can it be changed to pick the indexes current fillfactor and use this in the alter index statement. Ta.

  92. Ninja RgR'us says:

    Adjusted my script to allow for an exclusion list of databases (had a 2nd copy of prod db and doubled the maintenance time).

    I’m sure I’m not the only one who needs this feature.

  93. Spaceman says:

    Had another thought on this great script. Would it be benenficial to limit the indexes in scope by table rowcount rather than by index pagecount?.
    My thought here is that then i could have several jobs working overnight simultaneously, each one reindexing tables according to the tables rowcount. If i tried this using pagecount i suspect the jobs could end up rebuilding different indexes for the same table at the same time – which would probably cause some sort of contention problem.
    Hope that makes sense… thanks.

  94. Spaceman says:

    In response to my previous comment on the FILLFACTOR used i think it’s a misunderstanding because BOL is a bit contradictory…
    “FILLFACTOR = fillfactor
    Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0.”

    But later says…

    “When an option is not explicitly specified, the current setting is applied. For example, if a FILLFACTOR setting is not specified in the REBUILD clause, the fill factor value stored in the system catalog will be used during the rebuild process.”

  95. Pingback: Index operation « Simon's SQL

  96. Jed says:

    So while I apologize for the delay in posting the latest version, I hope you can understand and forgive me.

    Don’t apologize unless you’re actually sorry – and int this case, there’s no reason to be. Thanks for your work on the script.

  97. Pingback: Managing Index Fragmentation and Compression | Robert's space

  98. Pingback: MSSQL2005 database hanging during maintenance - Just just easy answers

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>