Index Definition Audit Script

Recently, I needed to audit indexes on two different servers. I wanted to compare things like index keys and included columns, partitioning keys, unique constraints, clustered indexes and primary keys, and filter index definitions. Basically, I wanted to make sure that the indexing of two databases on two different servers were completely in sync. To do this, I wrote the following scripts. The first script will audit a single database or even a single table. The second script once more makes use of Aaron Bertrand’s sp_foreachdb procedure to iterate through every database on a server.

To do a quick and easy compare, I dumped the results to a single table on each server and used Red Gate’s SQL Data Compare to find the differences.

This is another metadata script, so it should be fairly lightweight. That said, all of the usual disclaimers apply:

  • This script will only run on SQL 2008 or newer because of the filtered index component. See Patrick’s solution in the comments below for a mod that works in 2005 too.
  • It worked for me, but YMMV depending on editions, collations, creative settings, etc.
  • I don’t currently have access to any instances older than SQL Server 2012, so please respond with any backwards compatibility issues.
  • If you don’t have sp_foreachdb installed and don’t want to install it, you can replace it with sp_msforeachdb, but be aware databases may be skipped

Enjoy. :)

Single-Database Version

WITH indexCTE AS
(
    SELECT st.object_id                                                                         AS objectID
        , st.name                                                                               AS tableName
        , si.index_id                                                                           AS indexID
        , si.name                                                                               AS indexName
        , si.type_desc                                                                          AS indexType
        , sc.column_id                                                                          AS columnID
        , sc.name + CASE WHEN sic.is_descending_key = 1 THEN ' DESC' ELSE '' END                AS columnName
        , sic.key_ordinal                                                                       AS ordinalPosition
        , CASE WHEN sic.is_included_column = 0 AND key_ordinal > 0 THEN sc.name ELSE NULL END   AS indexKeys
        , CASE WHEN sic.is_included_column = 1 THEN sc.name ELSE NULL END                       AS includedColumns
        , sic.partition_ordinal                                                                 AS partitionOrdinal
        , CASE WHEN sic.partition_ordinal > 0 THEN sc.name ELSE NULL END                        AS partitionColumns
        , si.is_primary_key                                                                     AS isPrimaryKey
        , si.is_unique                                                                          AS isUnique
        , si.is_unique_constraint                                                               AS isUniqueConstraint
        , si.has_filter                                                                         AS isFilteredIndex
        , COALESCE(si.filter_definition, '')                                                    AS filterDefinition
    FROM sys.tables                         AS st
    INNER JOIN sys.indexes                  AS si 
        ON si.object_id =   st.object_id
    INNER JOIN sys.index_columns            AS sic 
	    ON sic.object_id=si.object_id
        AND sic.index_id=si.index_id 
    INNER JOIN sys.columns                  AS sc 
	    ON sc.object_id = sic.object_id 
	    and sc.column_id = sic.column_id
) 
 
SELECT DISTINCT 
      @@SERVERNAME                                      AS ServerName
    , DB_NAME()                                         AS DatabaseName
    , tableName
    , indexName
    , indexType
    , STUFF((
            SELECT ', ' + indexKeys
                FROM indexCTE
            WHERE objectID = cte.objectID
                AND indexID = cte.indexID
                AND indexKeys IS NOT NULL 
            ORDER BY ordinalPosition
                FOR XML PATH(''), 
      TYPE).value('.','varchar(max)'),1,1,'')           AS indexKeys
    , COALESCE(STUFF((
            SELECT ', ' + includedColumns
                FROM indexCTE
            WHERE objectID = cte.objectID
                AND indexID = cte.indexID
                AND includedColumns IS NOT NULL 
            ORDER BY columnID
                FOR XML PATH(''), 
      TYPE).value('.','varchar(max)'),1,1,''), '')      AS includedColumns
    , COALESCE(STUFF((
            SELECT ', ' + partitionColumns
                FROM indexCTE
            WHERE objectID = cte.objectID
                AND indexID = cte.indexID
                AND partitionColumns IS NOT NULL 
            ORDER BY partitionOrdinal
                FOR XML PATH(''), 
      TYPE).value('.','varchar(max)'),1,1,''), '')      AS partitionKeys
    , isPrimaryKey
    , isUnique
    , isUniqueConstraint
    , isFilteredIndex
    , FilterDefinition
FROM indexCTE AS cte
--WHERE tableName = 'SalesOrderDetail'
ORDER BY tableName
    , indexName;

Multi-Database Version

IF OBJECT_ID('tempdb..#IndexAudit') IS NOT NULL
    DROP TABLE #IndexAudit;
 
CREATE TABLE #IndexAudit
(
      serverName                SYSNAME
    , databaseName              SYSNAME
    , tableName                 VARCHAR(128)
    , indexName                 VARCHAR(128)
    , indexType                 NVARCHAR(60)
    , indexKeys                 VARCHAR(8000)
    , includedColumns           VARCHAR(8000)
    , partitionColumns          VARCHAR(8000)
    , isPrimaryKey              BIT
    , isUnique                  BIT
    , isUniqueConstraint        BIT
    , isFilteredIndex           BIT
    , FilterDefinition          VARCHAR(8000)
);
 
EXECUTE sp_foreachdb 'USE ?;
WITH indexCTE AS
(
    SELECT st.object_id                                                                         AS objectID
        , st.name                                                                               AS tableName
        , si.index_id                                                                           AS indexID
        , si.type_desc                                                                          AS indexType
        , si.name                                                                               AS indexName
        , sc.column_id                                                                          AS columnID
        , sc.name + CASE WHEN sic.is_descending_key = 1 THEN '' DESC'' ELSE '''' END            AS columnName
        , sic.key_ordinal                                                                       AS ordinalPosition
        , CASE WHEN sic.is_included_column = 0 AND key_ordinal > 0 THEN sc.name ELSE NULL END   AS indexKeys
        , CASE WHEN sic.is_included_column = 1 THEN sc.name ELSE NULL END                       AS includedColumns
        , sic.partition_ordinal                                                                 AS partitionOrdinal
        , CASE WHEN sic.partition_ordinal > 0 THEN sc.name ELSE NULL END                        AS partitionColumns
        , si.is_primary_key                                                                     AS isPrimaryKey
        , si.is_unique                                                                          AS isUnique
        , si.is_unique_constraint                                                               AS isUniqueConstraint
        , si.has_filter                                                                         AS isFilteredIndex
        , COALESCE(si.filter_definition, '''')                                                  AS filterDefinition
    FROM sys.tables                         AS st
    INNER JOIN sys.indexes                  AS si 
        ON si.object_id =   st.object_id
    INNER JOIN sys.index_columns            AS sic 
	    ON sic.object_id=si.object_id
        AND sic.index_id=si.index_id 
    INNER JOIN sys.columns                  AS sc 
	    ON sc.object_id = sic.object_id 
	    and sc.column_id = sic.column_id
) 
 
INSERT INTO #IndexAudit
SELECT DISTINCT 
      @@SERVERNAME                                              AS ServerName
    , DB_NAME()                                                 AS DatabaseName
    , tableName
    , indexName
    , indexType
    , STUFF((
            SELECT '', '' + indexKeys
                FROM indexCTE
            WHERE objectID = cte.objectID
                AND indexID = cte.indexID
                AND indexKeys IS NOT NULL 
            ORDER BY ordinalPosition
                FOR XML PATH(''''), 
      TYPE).value(''.'',''varchar(max)''),1,1,'''')             AS indexKeys
    , COALESCE(STUFF((
            SELECT '', '' + includedColumns
                FROM indexCTE
            WHERE objectID = cte.objectID
                AND indexID = cte.indexID
                AND includedColumns IS NOT NULL 
            ORDER BY columnID
                FOR XML PATH(''''), 
      TYPE).value(''.'',''varchar(max)''),1,1,''''), '''')      AS includedColumns
    , COALESCE(STUFF((
            SELECT '', '' + partitionColumns
                FROM indexCTE
            WHERE objectID = cte.objectID
                AND indexID = cte.indexID
                AND partitionColumns IS NOT NULL 
            ORDER BY partitionOrdinal
                FOR XML PATH(''''), 
      TYPE).value(''.'',''varchar(max)''),1,1,''''), '''')      AS partitionKeys
    , isPrimaryKey
    , isUnique
    , isUniqueConstraint
    , isFilteredIndex
    , FilterDefinition
FROM indexCTE AS cte
ORDER BY tableName
    , indexName;
';
 
-- For multi-server testing, dump results to a temp table and compare tables
SELECT *
FROM #IndexAudit
WHERE databaseName NOT IN ('tempdb', 'master', 'msdb', 'model')
ORDER BY serverName
    , databaseName
    , tableName
    , indexName;

Example Results

ServerName   DatabaseName       tableName            indexName                      indexType    indexKeys                                                    includedColumns                                    partitionKeys isPrimaryKey isUnique isUniqueConstraint isFilteredIndex FilterDefinition
------------ ------------------ -------------------- ------------------------------ ------------ ------------------------------------------------------------ -------------------------------------------------- ------------- ------------ -------- ------------------ --------------- --------------------------------
SQLFOOL\2012 AdventureWorks2012 SalesOrderDetail     AK_SalesOrderDetail_rowguid    NONCLUSTERED  rowguid                                                                                                                      0            1        0                  0                                               
SQLFOOL\2012 AdventureWorks2012 SalesOrderDetail     FIX_SalesOrderDetail_1         NONCLUSTERED  CarrierTrackingNumber, SalesOrderID, ProductID               OrderQty, SpecialOfferID, LineTotal, rowguid                    0            0        0                  1               ([ModifiedDate]>='2006-01-01')  
SQLFOOL\2012 AdventureWorks2012 SalesOrderDetail     IX_SalesOrderDetail_ProductID  NONCLUSTERED  ProductID                                                                                                                    0            0        0                  0                                               
SQLFOOL\2012 AdventureWorks2012 SalesOrderDetail     PK_SalesOrderDetail_SalesOrder CLUSTERED     SalesOrderID, SalesOrderDetailID                                                                                             1            1        0                  0

Go Daddy Insiders

I don’t hide the fact that I work at Go Daddy. All discussions of advertising methods aside, it’s a great company to work for. Not only am I treated well as an employee, I also get to work in a world-class technical environment. However, the marketing campaigns tend to steal the spotlight. As a result, few people are aware of technology that it takes to be the #1 hosting provider in the world. Some examples of little-known facts about Go Daddy:

  • 10 billion DNS queries answered daily
  • Over 35,000 servers & 100,000 square feet of state-of-the-art global data centers
  • 25 petabytes — yes, petabytes! — of networked data storage

Pretty cool, huh? Go Daddy has launched a new blog called Inside Go Daddy as a way to share all the nitty gritty details of what it takes to support this kind of environment. Here’s a blurb from the site:

This is your inside source for what’s going on with Go Daddy’s tech experts. You’ll get insight and opinions from Go Daddy’s tech leaders on industry topics, company projects & open source initiatives … the leading edge, unconventional, “behind-the-scenes” information you won’t find anywhere else. It’s not PR, it’s not executive talk, it’s the story straight from Go Daddy’s developers, engineers & IT personnel.

Shockingly, I’ve signed up to blog about database scalability. ;) I’ve just started a new series that explores the tuning and design changes required to support 27k transactions per second during the airing of Go Daddy’s Super Bowl commercials. Those who attended my Summit 2009 session might recognize some of the high-level content, but this series will explore the topics in depth and with never-before-revealed detail. My first article, Scaling the Database: Data Types, is now live.

If you find the content helpful or interesting, please share the article or leave a comment. My employer monitors blog traffic, and we have a bit of a contest going on to see what topics get the most hits. Quite frankly, it’d be cool if the SQL Server topics outperformed the NoSQL topics. ;)

Also, I’ll entertain topic requests, so if there’s something you’re just dying to know about what we do or how we do it, let me know. :)

Index Defrag Script, v4.1

It’s been quite some time since my last index defrag script update. A big part of the reason for that is because I wanted to implement many of the suggestions I’ve received, but I just haven’t had the time. I still have those changes planned, but I’m not sure quite when I’ll get to it. Rather than continue to wait for a major release, I’m releasing a small update to my defrag that will take care of the most common complaints I receive.

Change Log:

  • Bug fix for databases containing spaces or special characters
  • Support for case-sensitive databases
  • Re-executable CREATE script (for those who want to re-run the whole script)
  • Comma-delimited list of databases is now supported for the @database parameter

Feature List:

  • Defrag a single database, a list of databases, or all databases (@database)
  • Time Limitations: stop defragging after the specified amount of time has elapsed (@timeLimit). Please note, it will not kill a defrag that is currently in process, even if it exceeds the threshold.
  • Optional stop-and-resume functionality: pick up where your defrag last left off without having to rescan sys.dm_db_index_physical_stats. (@forceRescan)
  • Defrag scheduling: choose which days to defrag certain indexes, or exclude certain indexes altogether, by using the dbo.dba_indexDefragExclusion table.
  • Defrag priority: choose whether to defrag indexes in ascending or descending order by range_scan_count (default), fragmentation, or page_count.
  • Current partition exclusion: choose whether or not to exclude the right-most populated partition from the defrag process, common for sliding-window tables (@excludeMaxPartition)
  • Commands-only mode: Choose to just log the current defrag status and print the defrag commands, rather than executing them, by using @executeSQL.
  • … and tons more! Please read the parameter list and notes section for details of all the options available.

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?”
Yes, you can.

“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.

Special thanks to Richard Yanger for his assistance with beta testing. :)

You can download a text file of this script here: dba_indexDefrag_sp_v41

/*** Scroll down to the see important notes, disclaimers, and licensing information ***/
 
/* Let's create our parsing function... */
IF EXISTS ( SELECT  [object_id]
            FROM    sys.objects
            WHERE   name = 'dba_parseString_udf' )
    DROP FUNCTION dbo.dba_parseString_udf;
GO
 
CREATE FUNCTION dbo.dba_parseString_udf
(
          @stringToParse VARCHAR(8000)  
        , @delimiter     CHAR(1)
)
RETURNS @parsedString TABLE (stringValue VARCHAR(128))
AS
/*********************************************************************************
    Name:       dba_parseString_udf
 
    Author:     Michelle Ufford, http://sqlfool.com
 
    Purpose:    This function parses string input using a variable delimiter.
 
    Notes:      Two common delimiter values are space (' ') and comma (',')
 
    Date        Initials    Description
    ----------------------------------------------------------------------------
    2011-05-20  MFU         Initial Release
*********************************************************************************
Usage: 		
    SELECT *
    FROM dba_parseString_udf(<string>, <delimiter>);
 
Test Cases:
 
    1.  multiple strings separated by space
        SELECT * FROM dbo.dba_parseString_udf('  aaa  bbb  ccc ', ' ');
 
    2.  multiple strings separated by comma
        SELECT * FROM dbo.dba_parseString_udf(',aaa,bbb,,,ccc,', ',');
*********************************************************************************/
BEGIN
 
    /* Declare variables */
    DECLARE @trimmedString  VARCHAR(8000);
 
    /* We need to trim our string input in case the user entered extra spaces */
    SET @trimmedString = LTRIM(RTRIM(@stringToParse));
 
    /* Let's create a recursive CTE to break down our string for us */
    WITH parseCTE (StartPos, EndPos)
    AS
    (
        SELECT 1 AS StartPos
            , CHARINDEX(@delimiter, @trimmedString + @delimiter) AS EndPos
        UNION ALL
        SELECT EndPos + 1 AS StartPos
            , CHARINDEX(@delimiter, @trimmedString + @delimiter , EndPos + 1) AS EndPos
        FROM parseCTE
        WHERE CHARINDEX(@delimiter, @trimmedString + @delimiter, EndPos + 1) <> 0
    )
 
    /* Let's take the results and stick it in a table */  
    INSERT INTO @parsedString
    SELECT SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)
    FROM parseCTE
    WHERE LEN(LTRIM(RTRIM(SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)))) > 0
    OPTION (MaxRecursion 8000);
 
    RETURN;   
END
GO
 
/* First, we need to take care of schema updates, in case you have a legacy 
   version of the script installed */
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);
 
IF EXISTS ( SELECT  [object_id]
            FROM    sys.indexes
            WHERE   name = 'PK_indexDefragLog' ) 
    EXECUTE sp_rename dba_indexDefragLog, @indexDefragLog_rename;
 
IF EXISTS ( SELECT  [object_id]
            FROM    sys.indexes
            WHERE   name = 'PK_indexDefragExclusion' ) 
    EXECUTE sp_rename dba_indexDefragExclusion, @indexDefragExclusion_rename;
 
IF NOT EXISTS ( SELECT  [object_id]
                FROM    sys.indexes
                WHERE   name = 'PK_indexDefragLog_v40' )
BEGIN
 
    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';
 
END
 
IF NOT EXISTS ( SELECT  [object_id]
                FROM    sys.indexes
                WHERE   name = 'PK_indexDefragExclusion_v40' )
BEGIN
 
    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';
 
END
 
IF NOT EXISTS ( SELECT  [object_id]
                FROM    sys.indexes
                WHERE   name = 'PK_indexDefragStatus_v40' )
BEGIN
 
    CREATE TABLE dbo.dba_indexDefragStatus
    (
         databaseID         INT             NOT NULL
       , databaseName       NVARCHAR(128)   NOT NULL
       , objectID           INT             NOT NULL
       , indexID            INT             NOT NULL
       , partitionNumber    SMALLINT        NOT NULL
       , fragmentation      FLOAT           NOT NULL
       , page_count         INT             NOT NULL
       , range_scan_count   BIGINT          NOT NULL
       , schemaName         NVARCHAR(128)   NULL
       , objectName         NVARCHAR(128)   NULL
       , indexName          NVARCHAR(128)   NULL
       , scanDate           DATETIME        NOT NULL
       , defragDate         DATETIME        NULL
       , printStatus        BIT DEFAULT (0) NOT NULL
       , exclusionMask      INT DEFAULT (0) NOT NULL
 
        CONSTRAINT PK_indexDefragStatus_v40 
            PRIMARY KEY CLUSTERED (databaseID, objectID, indexID, partitionNumber)
    );
 
    PRINT 'dba_indexDefragStatus Table Created';
 
END;
 
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 one or more database names, separated by commas; 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
    2011-04-28  MFU         4.1     Bug fixes for databases requiring []
                                    , cleaned up the create table section
                                    , updated syntax for case-sensitive databases
                                    , comma-delimited list for @database now supported
*********************************************************************************
    Example of how to call this script:
 
        EXECUTE 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
            , @database             = 'sandbox,sandbox_caseSensitive';
*********************************************************************************/																
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(s)' 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 */
        /* If @database is NULL, it means we want to defrag *all* databases */
        IF @database IS NULL
        BEGIN
 
            INSERT INTO #databaseList
            SELECT database_id
                , name
                , 0 -- not scanned yet for fragmentation
            FROM sys.databases
            WHERE [name] NOT IN ('master', 'tempdb')-- exclude system databases
                AND [state] = 0 -- state must be ONLINE
                AND is_read_only = 0;  -- cannot be read_only
 
        END;
        ELSE
        /* Otherwise, we're going to just defrag our list of databases */
        BEGIN
 
            INSERT INTO #databaseList
            SELECT database_id
                , name
                , 0 -- not scanned yet for fragmentation
            FROM sys.databases AS d
            JOIN dbo.dba_parseString_udf(@database, ',') AS x
                ON d.name = x.stringValue
            WHERE [name] NOT IN ('master', 'tempdb')-- exclude system databases
                AND [state] = 0 -- state must be ONLINE
                AND is_read_only = 0;  -- cannot be read_only
 
        END; 
 
        /* 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

Disposable Indexes

Today I had to run an ad hoc query on a 8.5 billion row table. The table had a dozen columns of a variety of data types and was clustered on a bigint identity. There were no other indexes on the table. My query involved a join to a smaller table with a date range restriction. Without an adequate index to use, SQL Server was going to be forced to scan this 8.5 billion row table. Now, I don’t have much patience for waiting for long running queries. I want to run the ad hoc, e-mail the results, and forget about it. But short of adding a nonclustered index, which would take a very long time to build and probably require additional space requisitioned from the SAN team, what could I do? Enter disposable indexes. Now, you might be asking yourself, “What the frilly heck does she mean by a disposable index? Is that new in Denali?” No, dear reader. I am actually referring to filtered indexes, which is available in SQL Server 2008 and 2008 R2. I call them “disposable” because I create them to significantly speed up ad hoc queries, then I drop them when I’m done.

Here, allow me to demonstrate using the AdventureWorks2008R2 database. Although the tables are smaller, this query is very similar in structure to what I needed to run today.

Select Count(Distinct sod.SalesOrderID) As 'distinctCount'
From AdventureWorks2008R2.Sales.SalesOrderDetail As sod
Join AdventureWorks2008R2.Production.Product As p
    On sod.ProductID = p.ProductID
Where sod.ModifiedDate Between '2008-01-01' And '2008-07-31'
    And p.MakeFlag = 0;

Now, let’s take a look at the type of indexes we currently have available:

Select name, has_filter, filter_definition
From sys.indexes
Where object_id = object_id('Sales.SalesOrderDetail');
name                                                    has_filter filter_definition
-----------------------------------------------------------------------------------------
PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID     0          NULL
AK_SalesOrderDetail_rowguid                             0          NULL
IX_SalesOrderDetail_ProductID                           0          NULL
 
(3 row(s) affected)

We need an index on ModifiedDate and ProductID, which it doesn’t look like we have currently. Without this, we’re going to end up scanning on the clustered index. That means SQL Server will have to evaluate each and every single row in the table to see if the row matches the criteria of our query. Not pretty, and certainly not fast. So instead, let’s create a filtered index on date. But we can greatly speed up the time it takes to create our filtered index by doing a little investigating upfront and finding a range of clustering key values that will cover the query. Doing this allows SQL Server to seek on the clustered index, greatly reducing the amount of reads necessary to create our filtered index. So let’s see this in action. First, let’s find out the current max value of the table:

Select Max(SalesOrderDetailID) As 'maxID'
From AdventureWorks2008R2.Sales.SalesOrderDetail;
maxID
-----------
121317

Now we get to do a little guessing. Let’s go back and see what date we get if we look at half of the records:

Select SalesOrderDetailID, ModifiedDate
From AdventureWorks2008R2.Sales.SalesOrderDetail
Where SalesOrderDetailID = (121317/2);
SalesOrderDetailID ModifiedDate
------------------ -----------------------
60658              2007-11-01 00:00:00.000

Okay, SalesOrderDetailID 60658 gets us back to 11/1/2007. That’s a little too far. Let’s see how a SalesOrderDetailID value of 75000 does…

Select SalesOrderDetailID, ModifiedDate
From AdventureWorks2008R2.Sales.SalesOrderDetail
Where SalesOrderDetailID = 75000;
SalesOrderDetailID ModifiedDate
------------------ -----------------------
75000              2007-12-27 00:00:00.000

Okay, SalesOrderDetailID 75000 takes us back to 12/27/2007. That’s close enough to 1/1/2008 for my purposes. Of course, depending on the size of the table, in real life it may make sense to try to get closer to the value you’re looking for. But for now, this will do. And because we’re looking for data through the “current date” (7/31/2008 in the AdventureWorks2008R2 database), we already know our outer limit is 121317.

Now let’s take these ranges and create a filtered index that will cover our query:

Create Nonclustered Index IX_SalesOrderDetail_filtered
    On Sales.SalesOrderDetail(ModifiedDate, ProductID)
    Include (SalesOrderID)
    Where SalesOrderDetailID >= 75000
      And SalesOrderDetailID <  121317;

By having this range identified, SQL Server can perform a seek on the clustered index to create the nonclustered index on just the subset of records that you need for your query. Remember that 8.5 billion row table I mentioned earlier? I was able to create a filtered index that covered my query in 10 seconds. Yes, that’s right… 10 SECONDS.

The last thing we need to do is include our filtered index definition in our ad hoc query to ensure that the filtered index is used. It also doesn’t hurt to explicitly tell SQL Server to use the filtered index if you’re absolutely sure it’s the best index for the job.

Select Count(Distinct sod.SalesOrderID) As 'distinctCount'
From AdventureWorks2008R2.Sales.SalesOrderDetail As sod With (Index(IX_SalesOrderDetail_filtered))
Join AdventureWorks2008R2.Production.Product As p
    On sod.ProductID = p.ProductID
Where sod.ModifiedDate Between '2008-01-01' And '2008-07-31'
    And p.MakeFlag = 0
    And sod.SalesOrderDetailID >= 75000
    And sod.SalesOrderDetailID <  121317;

That’s all there is to it. Using this method, I was able to complete my ad hoc request in 40 seconds: 10 seconds to create the filtered index and 30 seconds to actually execute the ad hoc. Of course, it also took a couple of minutes to write the query, look at existing indexes, and search for the correct identity values. All in all, from the time I received the request to the time I send the e-mail was about 5 minutes. All because of disposable filtered indexes. How’s that for some SQL #awesomesauce? :)

Summit 2010 Abstract Submission

Jeremiah Peschka just tweeted about the looming deadline to submit an abstract for Summit 2010. I’ve been trying to think of a good topic to present on, and this finally got my butt in gear to submit one! For those interested, here’s what I submitted:

Heaps of Trouble, Clusters of Glory – A Look At Index Internals

Indexes are a crucial component of SQL Server, especially in performance tuning, yet many DBA’s don’t fully understand how indexes work. In this in-depth session, Michelle will examine the anatomy of indexes, from how they’re stored to how they get fragmented. And to make our inner geeks happy, she’ll look at page data to show you what’s happening behind the scenes. Topics covered will include index structure, fragmentation and defragmentation, index partitioning, and index filtering. If you’ve ever wondered just *what* goes on in an index, don’t miss this session!

Special thanks to Chris for help with the session title. :)

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

Filtered Indexes Work-Around

Recently, I needed to create a stored procedure that queried a rather large table. The table has a filtered index on a date column, and it covers the query. However, the Query Optimizer was not using the index, which was increasing the execution time (not to mention IO!) by at least 10x. This wasn’t the first time I’ve had the Optimizer fail to use a filtered index. Normally when this happens, I use a table hint to force the filtered index — after I verify that it is indeed faster, of course. However, since this was a stored procedure, I was receiving the following error message whenever I tried to execute the proc:

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

SQL Server would not allow me to execute the stored procedure using the filtered index hint. If I removed the hint, it executed, but it used a different, non-covering and far more expensive index. For those of you not familiar with this issue, allow me to illustrate the problem.

First, create a table to play with and populate it with some bogus data:

Create Table dbo.filteredIndexTest
(
      myID   int Identity(1,3)
    , myDate smalldatetime 
    , myData char(100)
 
    Constraint PK_filteredIndexTest
        Primary Key Clustered(myID)
);
 
Set NoCount On;
Declare @date smalldatetime = '2010-01-01';
 
While @date < '2010-02-01'
Begin
 
    Insert Into dbo.filteredIndexTest
    (
          myDate
        , myData
    )
    Select @date
        , 'Date: ' + Convert(varchar(20), @date, 102);
 
    Set @date = DateAdd(minute, 1, @date);
 
End;
 
Select Count(*) From dbo.filteredIndexTest;

It looks like this will generate 44,640 rows of test data… plenty enough for our purposes. Now, let’s create our filtered index and write a query that will use it:

Create NonClustered Index IX_filteredIndexTest_1
    On dbo.filteredIndexTest(myDate)
    Include (myData)
    Where myDate >= '2010-01-27';
 
Select Distinct myData
From dbo.filteredIndexTest
Where myDate >= '2010-01-28';

If you look at the execution plan for this query, you’ll notice that the Optimizer is using the filtered index. Perfect! Now let’s parameterize it.

Declare @myDate1 smalldatetime = '2010-01-28';
 
Select Distinct myData
From dbo.filteredIndexTest
Where myDate >= @myDate1;

Uh oh. Looking at the execution plan, we see that SQL Server is no longer using the filtered index. Instead, it’s scanning the clustered index! Why is this? There’s actually a good explanation for it. The reason is that I could, in theory, pass a date to my parameter that fell outside of the filtered date range. If that’s the case, then SQL Server could not utilize the filtered index. Personally, I think it’s a bug and SQL Server should identify whether or not a filtered index could be used based on the actual value submitted, but… that’s a whole other blog post. :)

So what can we do? Well, dynamic SQL may be able to help us out in this case. Let’s give it a go. First, let’s try parameterized dynamic SQL.

Declare @mySQL1 nvarchar(2000)
    , @myParam nvarchar(2000) = '@p_myDate2 smalldatetime'
    , @myDate2 smalldatetime = '2010-01-28';
 
Set @mySQL1 = 'Select Distinct myData
              From dbo.filteredIndexTest
              Where myDate >= @p_myDate2';
 
Execute sp_executeSQL @mySQL1, @myParam, @p_myDate2 = @myDate2;

Looking at the execution plan, we see we’re still scanning on the clustered index. This is because the parameterized dynamic SQL resolves to be the exact same query as the one above it. Let’s try unparameterized SQL instead:

Declare @mySQL2 nvarchar(2000)
    , @myDate3 smalldatetime = '2010-01-28';
 
Set @mySQL2 = 'Select Distinct myData
              From dbo.filteredIndexTest
              Where myDate >= ''' + Cast(@myDate3 As varchar(20)) + '''';
 
Execute sp_executeSQL @mySQL2;
 
-- Drop Table dbo.filteredIndexTest;

Voila! We have a seek on our filtered index. Why? Because the statement resolves to be identical to our first query, where we hard-coded the date value in the WHERE clause.

Now, I want to stress this fact: you should always, ALWAYS use parameterized dynamic SQL whenever possible. Not only is it safer, but it’s also faster, because it can reuse cached plans. But sometimes you just cannot accomplish the same tasks with it. This is one of those times. If you do end up needing to use unparameterized dynamic SQL as a work-around, please make sure you’re validating your input, especially if you’re interfacing with any sort of external source.

There’s an even easier work-around for this problem that Dave (http://www.crappycoding.com) shared with me: recompile.

Adding “Option (Recompile)” to the end of your statements will force the Optimizer to re-evaluate which index will best meet the needs of your query every time the statement is executed. More importantly, it evaluates the plan based on the actual values passed to the parameter… just like in our hard-coded and dynamic SQL examples. Let’s see it in action:

DECLARE @myDate4 SMALLDATETIME = '2010-01-28';
 
SELECT DISTINCT myData
FROM dbo.filteredIndexTest 
WHERE myDate >= @myDate4
OPTION (RECOMPILE);
 
DECLARE @myDate5 SMALLDATETIME = '2010-01-20';
 
SELECT DISTINCT myData
FROM dbo.filteredIndexTest 
WHERE myDate >= @myDate5
OPTION (RECOMPILE);

If we look at the execution plans for the 2 queries above, we see that the first query seeks on the filtered index, and the second query scans on the clustered index. This is because the second query cannot be satisfied with the filtered index because we initially limited our index to dates greater than or equal to 1/27/2010.

There are, of course, trade-offs associated with each approach, so use whichever one best meets your needs. Do you have another work-around for this issue? If so, please let me know. :)

Update:

Alex Kuznetsov (http://www.simple-talk.com/author/alex-kuznetsov/) shared this method too:

DECLARE @myDate1 SMALLDATETIME = '2010-01-28';
 
SELECT DISTINCT myData
FROM dbo.filteredIndexTest
WHERE myDate = @myDate1
AND myDate >= '2010-01-27';

Like the other examples, this will result in an index seek on the filtered index. Basically, by explicitly declaring the start date of your filter, you’re letting the Optimizer know that the filtered index can satisfy the request, regardless of the parameter value passed. Thanks for the tip, Alex! :)

Index Defrag Script Updates – Beta Testers Needed

Update: Wow! I’ve received a ton of responses to my request for beta testers. Thank you all! The SQL Community is really amazing. I’ll hopefully have the new version online in just a few days. :)

Over the last few months, I’ve received many great comments and suggestions regarding my Index Defrag Script v3.0. I’ve just recently had time to implement most of these suggestions, plus some other things that I thought would be useful. :)

Here’s some of what you can look forward to shortly:

  • Probably the single most requested feature, the new version of the script allows you to set a time limit for index defrags.
  • There’s now a static table for managing the status of index defrags. This way, when your time limit is reached, you can pick up where you left off the next day, without the need to rescan indexes.
  • There’s now an option to prioritize defrags by range scan counts, fragmentation level, or page counts.
  • For those using partitioning, there is now an option to exclude the right-most populated partition from defrags (in theory, the one you’re writing to in a sliding-window scenario).
  • Options such as page count limits and SORT_IN_TEMPDB are now parameterized.
  • I’ve enhanced error logging.
  • … and more!

Right now, I’m looking for a few folks who are willing to beta test the script. If you’re interested, please send me an e-mail at michelle at sqlfool dot com with the editions of SQL Server you can test this on (i.e. 2005 Standard, 2008 Enterprise, etc.).

Thank you! :)

#PASSAwesomeness

Allen Kinsel on Twitter (@sqlinsaneo) recently started a new Twitter tag, #PASSAwesomeness, about all of the cool things about PASS Summit. I really like the tag, so I’m going to blatantly steal borrow it for this post. :)

First, and long overdue, I want to give a brief recap of the East Iowa SQL Saturday. On October 17th, our local PASS chapter, 380PASS, sponsored our first ever SQL Saturday at the University of Iowa in Iowa City. By all accounts, the event was a great success! We had 90 attendees, 11 speakers, and 21 sessions. We received numerous compliments on the quality of the speakers, the niceness of the facilities, and the abundance of food. Not too shabby for our first time hosting the event, if I do say so myself. :)

I’d like to thank all of our wonderful speakers, especially those who traveled from out of town and out of state, for making this event such a success. I’d also like to thank our amazing volunteers for helping put this all together. Lastly, but certainly not least, I’d like to thank our generous sponsors, without whom this event would not be possible. Because this event went so smoothly and was so well received in the community, we’ve already started planning our next big SQL event! In the meantime, don’t forget to check out our monthly 380PASS meetings to tide you over.

I’d also like to take a moment to discuss the PASS Summit. Unless you’re a DBA who’s been living under a rock, you’ve probably heard of the PASS Summit. If you *have* been living under a rock — and hey, I’m not poking fun, I used to live under a rock, too! — then what you need to know is that the Summit is the largest SQL Server conference in the world. It’s a gathering of Microsoft developers and SQL Server gurus; the rest of us show up to try to absorb as much from them as possible. Since I’ve recently moved to the Business Intelligence team, I’m extremely excited to delve into the amazing amount of BI content offered.

I’m also deeply honored to be presenting at the Summit this year on some of the performance tuning techniques I’ve used with great success in my production environments. The session is titled, Super Bowl, Super Load – A Look At Performance Tuning for VLDB’s. If you’re interested in performance tuning or VLDB (very large database) topics, consider stopping by to catch my session. From what I can tell, I’ll be presenting on Tuesday from 10:15am – 11:30am in room(s?) 602-604.

If you read my blog, or if we’ve ever interacted in any way on the internet — Twitter, LinkedIn, e-mails, blog comments, etc. — please stop by and say “hi”! Aside from all of the awesome SQL Server content, I’m really looking forward to meeting as many new folks as possible.

And on that note…

Getting to meet all of the amazing SQL Server professionals out there who have inspired and encouraged me in so many ways #PASSAwesomeness