Page Internals – Investigation Proc

As many of you know, I like to crawl around in page internals in my free time. It can be very enlightening, or just a good check to make sure that what you think is happening, is actually happening. To help with this process, I’ve created myself a little stored procedure that I can simply pass a few parameters to and have it return the page data for me. So for those who don’t have anything better to do who are as interested in page internals as I am, here’s my proc:

Create Procedure dbo.dba_viewPageData_sp
 
        /* Declare Parameters */
          @databaseName varchar(128)
        , @tableName    varchar(128)    = Null -- database.schema.tableName
        , @indexName    varchar(128)    = Null
        , @fileNumber   int             = Null
        , @pageNumber   int             = Null
        , @printOption  int             = 3    -- 0, 1, 2, or 3
        , @pageType     char(4)         = 'Leaf' -- Leaf, Root, or IAM
 
As
/*********************************************************************************
    Name:       dba_viewPageData_sp
 
    Author:     Michelle Ufford
 
    Purpose:    Retrieves page data for the specified table/page.
 
    Notes:      Can pass either the table name or the pageID, but must pass one, or
                you'll end up with no results. 
                If the table name is passed, it will return the first page.
 
        @tableName must be '<databaseName>.<schemaName>.<tableName>' in order to
            function correctly for cross-database joins.  
 
        @printOption can be one of following values:
            0 - print just the page header
            1 - page header plus per-row hex dumps and a dump of the page slot array
            2 - page header plus whole page hex dump
            3 - page header plus detailed per-row interpretation
 
        Page Options borrowed from: 
        https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/10/625659.aspx
 
        @pageType must be one of the following values:
            Leaf - returns the first page of the leaf level of your index or heap
            Root - returns the root page of your index
            IAM - returns the index allocation map chain for your index or heap
 
        Conversions borrowed from:
        http://sqlskills.com/blogs/paul/post/Inside-The-Storage-Engine-
        sp_AllocationMetadata-putting-undocumented-system-catalog-views-to-work.aspx
 
    Called by:  DBA
 
    Date        User    Description
    ----------------------------------------------------------------------------
    2009-05-06  MFU     Initial release for public consumption
*********************************************************************************
    Exec dbo.dba_viewPageData_sp
          @databaseName = 'AdventureWorks'
        , @tableName    = 'AdventureWorks.Sales.SalesOrderDetail'
        , @indexName    = 'IX_SalesOrderDetail_ProductID'
        --, @fileNumber   = 1
        --, @pageNumber   = 38208
        , @printOption  = 3
        , @pageType     = 'Root';
*********************************************************************************/
 
Set NoCount On;
Set XACT_Abort On;
Set Ansi_Padding On;
Set Ansi_Warnings On;
Set ArithAbort On;
Set Concat_Null_Yields_Null On;
Set Numeric_RoundAbort Off;
 
Begin
 
    Declare @fileID         int
        , @pageID           int
        , @sqlStatement     nvarchar(1200)
        , @sqlParameters    nvarchar(255)
        , @errorMessage     varchar(100);
 
    Begin Try
 
        If @fileNumber Is Null And @pageNumber Is Null And @tableName Is Null
        Begin
            Set @errorMessage = 'You must provide either a file/page number, or a table name!';
            RaisError(@errorMessage, 16, 1);
        End;
 
        If @pageType Not In ('Leaf', 'Root', 'IAM')
        Begin
            Set @errorMessage = 'You have entered an invalid page type; valid options are "Leaf", "Root", or "IAM"';
            RaisError(@errorMessage, 16, 1);
        End;
 
        If @fileNumber Is Null Or @pageNumber Is Null
        Begin
 
            Set @sqlStatement = 
            Case When @pageType = 'Leaf' Then
                'Select Top 1 @p_fileID = Convert (varchar(6), Convert (int, 
                    SubString (au.first_page, 6, 1) +
                    SubString (au.first_page, 5, 1)))
                , @p_pageID = Convert (varchar(20), Convert (int, 
                     SubString (au.first_page, 4, 1) +
                     SubString (au.first_page, 3, 1) +
                     SubString (au.first_page, 2, 1) +
                     SubString (au.first_page, 1, 1)))'
            When @pageType = 'Root' Then
                'Select Top 1 @p_fileID = Convert (varchar(6), Convert (int, 
                    SubString (au.root_page, 6, 1) +
                    SubString (au.root_page, 5, 1)))
                , @p_pageID = Convert (varchar(20), Convert (int, 
                     SubString (au.root_page, 4, 1) +
                     SubString (au.root_page, 3, 1) +
                     SubString (au.root_page, 2, 1) +
                     SubString (au.root_page, 1, 1)))'
            When @pageType = 'IAM' Then
                'Select Top 1 @p_fileID = Convert (varchar(6), Convert (int, 
                    SubString (au.first_iam_page, 6, 1) +
                    SubString (au.first_iam_page, 5, 1)))
                , @p_pageID = Convert (varchar(20), Convert (int, 
                     SubString (au.first_iam_page, 4, 1) +
                     SubString (au.first_iam_page, 3, 1) +
                     SubString (au.first_iam_page, 2, 1) +
                     SubString (au.first_iam_page, 1, 1)))'
            End + 
            'From ' + QuoteName(ParseName(@databaseName, 1)) + '.sys.indexes AS i
            Join ' + QuoteName(ParseName(@databaseName, 1)) + '.sys.partitions AS p
                On i.[object_id] = p.[object_id]
                And i.index_id = p.index_id
            Join ' + QuoteName(ParseName(@databaseName, 1)) + '.sys.system_internals_allocation_units AS au
                On p.hobt_id = au.container_id
            Where p.[object_id] = Object_ID(@p_tableName)
                And au.first_page > 0x000000000000 ' 
                + Case When @indexName Is Null 
                    Then ';' 
                    Else 'And i.name = @p_indexName;' End;
 
            Set @sqlParameters = '@p_tableName varchar(128)
                                , @p_indexName varchar(128)
                                , @p_fileID int OUTPUT
                                , @p_pageID int OUTPUT';
 
            Execute sp_executeSQL @sqlStatement
                        , @sqlParameters
                        , @p_tableName = @tableName
                        , @p_indexName = @indexName
                        , @p_fileID = @fileID OUTPUT
                        , @p_pageID = @pageID OUTPUT;
 
            End
            Else
            Begin
                Select @fileID = @fileNumber
                    , @pageID = @pageNumber;
            End;
 
        DBCC TraceOn (3604);
        DBCC Page (@databaseName, @fileID, @pageID, @printOption);
        DBCC TraceOff (3604);
 
    End Try
    Begin Catch
 
        Print @errorMessage;
 
    End Catch;
 
    Set NoCount Off;
    Return 0;
End
Go

This proc does have cross-database support, i.e. you can install it in your DBA database and use it to investigate data in other databases. Here’s an example…

    Exec dbo.dba_viewPageData_sp
          @databaseName = 'AdventureWorks'
        , @tableName    = 'AdventureWorks.Sales.SalesOrderDetail'
        , @indexName    = 'IX_SalesOrderDetail_ProductID';

… will return a nice data page:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
PAGE: (1:11000)
 
BUFFER:
 
BUF @0x0391F140
 
bpage = 0x0C0C0000                   bhash = 0x00000000                   bpageno = (1:11000)
bdbid = 7                            breferences = 0                      bUse1 = 35177
bstat = 0x1c00009                    blog = 0x21212159                    bnext = 0x00000000

[waits for the "oohs" and "aahs" to subside...]

I also give you the option to specify a specific page, in case you want to follow the page trail (i.e. m_nextPage). I’m not really providing support for partitions, although I do have a little dirty piece of code to return a hobt with data if possible (i.e. “first_page > 0×000000000000″).

Update: Special thanks to Jeremiah Peschka and Adam Machanic for showing me
QUOTENAME(PARSENAME(@databaseName, 1))! :)

Also, thanks to Paul Randal for his excellent blog posts on this very topic! Check out his blog post on DBCC Page. The conversion code was borrowed from his sp_AllocationMetadata proc.

Update 2: I’ve made some quick changes based on some feedback from Twitter. Thanks for the suggestions and hopefully you enjoy the updates.

Happy crawling!

Bulk Inserts with XML

Last week, I blogged about how to perform one-to-many inserts with table-valued parameters, a feature new in 2008. For those who do not yet have 2008 or will not have it in the near future, it may still be beneficial to use XML for bulk inserts.

Here’s a pretty simple example of how to accomplish this:

/* Create some tables to work with */
CREATE TABLE dbo.orders
(
      order_id      INT IDENTITY(1,1)   Not Null
    , orderDate     DATETIME            Not Null
    , customer_id   INT                 Not Null
 
    CONSTRAINT PK_orders
        PRIMARY KEY CLUSTERED(order_id)
);
 
CREATE TABLE dbo.orderDetails
(
      orderDetail_id    INT IDENTITY(1,1)   Not Null
    , order_id          INT                 Not Null
    , lineItem          INT                 Not Null
    , product_id        INT                 Not Null
 
    CONSTRAINT PK_orderDetails
        PRIMARY KEY CLUSTERED(orderDetail_id)
 
    CONSTRAINT FK_orderDetails_orderID
        FOREIGN KEY(order_id)
        REFERENCES dbo.orders(order_id)
);
Go
 
/* Create a new procedure using an XML parameter */
CREATE PROCEDURE dbo.insert_orderXML_sp
      @orderDate        DATETIME
    , @customer_id      INT
    , @orderDetailsXML  XML
AS
BEGIN
 
    SET NOCOUNT ON;
 
    DECLARE @myOrderID INT;
 
    INSERT INTO dbo.orders
    (
          orderDate
        , customer_id    
    )
    VALUES
    (
          @orderDate
        , @customer_id
    );
 
    SET @myOrderID = SCOPE_IDENTITY();
 
    INSERT INTO dbo.orderDetails
    (
          order_id
        , lineItem
        , product_id
    )
    SELECT @myOrderID
         , myXML.value('./@lineItem', 'int')
         , myXML.value('./@product_id', 'int')
    FROM @orderDetailsXML.nodes('/orderDetail') As nodes(myXML);
 
    SET NOCOUNT OFF;
 
END
GO
 
 
/* Call our stored procedure */
EXECUTE dbo.insert_orderXML_sp
      @orderDate = '2008-01-01'
    , @customer_id = 101
    , @orderDetailsXML = 
        '<orderDetail lineItem="1" product_id="123" />
         <orderDetail lineItem="2" product_id="456" />
         <orderDetail lineItem="3" product_id="789" />
         <orderDetail lineItem="4" product_id="246" />
         <orderDetail lineItem="5" product_id="135" />';
 
 
/* Check our data */
SELECT * FROM dbo.orders;
SELECT * FROM dbo.orderDetails;
 
 
/* Clean up our mess */
DROP PROCEDURE insert_orderXML_sp;
DROP TABLE dbo.orderDetails;
DROP TABLE dbo.orders;

I’ve found that this is more efficient when performing large parent/child inserts, i.e. 1 parent record to 100 child records. Keep in mind that there’s a point where doing an insert with XML is more expensive than using a traditional INSERT stored procedure. I haven’t run any tests yet to help define what that threshold is… more to come on this in the near future.

More on the Nodes() Method can be found here in Books Online: http://msdn.microsoft.com/en-us/library/ms188282(SQL.90).aspx

Update: I’ve just learned that the “value” keyword is case-sensitive. Apparently my code box plug-in was defaulting “value” to “VALUE.” :)

Here’s the error message you’ll get if you don’t have “value” in lower-case:
Cannot find either column “myXML” or the user-defined function or aggregate “myXML.VALUE”, or the name is ambiguous.

Large Updates on Replicated Tables

Late last night, I executed a 70mm update on a replicated table. This was a somewhat low priority update, so the primary goal (aside from the data change) was to avoid any issues on the publishing and subscribing servers, including replication latency errors. I have performed many large updates in the past, but this was the first on a replicated table.

To minimize impact, one of our system DBA’s suggested the use of a replicated stored procedure. Basically, instead of replicating each of the 70mm updates as a singleton transaction, the replicated stored procedure is called on the subscribing server, which then performs the bulk update locally. This was my first time using it and it worked beautifully.

Another of my colleagues, Jeff M., suggested the use of a control table. Normally, I would just output the last affected ID and update the script manually. However, this also worked so well that it will be adopted in all future update scripts.

Using the following pseudo-script and a replicated stored procedure, I was able to execute the update on 70mm records in 11.5 hours with *zero* impact on the servers and replication. I could’ve probably increased the batch size and reduced the execution time even further, but as I mentioned, this was a low priority update, so there was no need to push it.

This process should work equally well on non-replicated updates; merely replace the Execute statement with the actual update.

/************ Chunked Update Script with Control Table ************/
 
/* --------------------- Preparation Script --------------------- */
 
/* Note: Execute this section in a separate query window */
 
Use yourDatabase;
Go
 
Set NoCount On;
 
/* Create a control table to facilitate tweaking of parameters */
Create Table dbo.scratch_largeUpdate_control
(
      sizeOfBatch       int
    , waitForDelay      char(8)
    , minRecordToUpdate int 
    , maxRecordToUpdate int
);
 
/* Create your control data; you only want 1 row in this table */
Insert Into dbo.scratch_largeUpdate_control 
(sizeOfBatch, waitForDelay, minRecordToUpdate, maxRecordToUpdate)
Select 10000, '00:00:05', 40297132, 107459380;
 
/* Update Script */
Update dbo.scratch_largeUpdate_control
Set sizeOfBatch  = 100000
  , waitForDelay = '00:00:30';
 
 
/* ------------------------ Update Script ------------------------ */
Use yourDatabase;
Go
 
Set NoCount On;
 
Declare 
      @batchSize        int
    , @minID            int
    , @maxID            int
    , @procMinID        int
    , @procMaxID        int
    , @delay            char(8)
    , @statusMsg        varchar(1000);
 
Begin Try
 
    IF @@SERVERNAME Not In ('PRODSERVER')
        RAISERROR('Sorry, this cannot be executed here!', 16, 1);
 
    If Not Exists(
            Select object_id 
            From sys.objects 
            Where [name] = 'scratch_largeUpdate_control' 
            And type = 'U' )
        RaisError ('ERROR: Control table does not exist!', 16, 1)
            With NoWait;
    Else
        Select 
              @minID        = minRecordToUpdate 
            , @maxID        = maxRecordToUpdate 
            , @batchSize    = sizeOfBatch
            , @delay        = waitForDelay
        From dbo.scratch_largeUpdate_control With (NoLock);
 
    Set @statusMsg = 'Beginning update; batch size of ' 
        + Cast(@batchSize As varchar(10)) + ', delay of ' 
        + @delay + ' defined.  Estimate ' 
        + Cast((((@maxID - @minID) / @batchSize) + 1) As varchar(10)) 
        + ' iterations to be performed.'
 
    RaisError (@statusMsg, 10, 1) With NoWait;
 
    While @minID < @maxID
    Begin
 
        Select @procMinID = @minID
            , @procMaxID = (@minID + (@batchSize - 1));
 
        /* Execute actual update code here 
           OR 
           Call a replicated stored procedure, i.e. */
        Execute dbo.myReplicatedUpdateProc 
              @minRecordID = @procMinID
            , @maxRecordID = @procMaxID;
 
        Set @statusMsg = 
            'Updating records ' + Cast(@minID As varchar(10)) 
            + ' through ' + Cast((@minID + (@batchSize - 1)) 
            As varchar(10)) + '...';
 
        RaisError (@statusMsg, 10, 1) With NoWait;
 
        /* Update our control table with the last successfully
           updated record ID.  In the event of an error,
           we can start from here. */
        Update dbo.scratch_largeUpdate_control 
        Set minRecordToUpdate = @minID + @batchSize;
 
        Select @minID = @minID + @batchSize; 
        WaitFor Delay @delay; -- breather for the server
 
        /* Check to see if our control values have changed */
        If Not Exists(
            Select * 
            From dbo.scratch_largeUpdate_control With (NoLock) 
            Where @batchSize = sizeOfBatch And @delay = waitForDelay)
        Begin
 
            /* There was a change, so grab our new values */
            Select @batchSize = sizeOfBatch
                 , @delay = waitForDelay
            From dbo.scratch_largeUpdate_control With (NoLock)
 
            /* Print a status message with the new values */
            Set @statusMsg = 'Parameters changed:  batch size = ' 
                + Cast(@batchSize As varchar(10)) 
                + ', delay = ' + @delay;
 
            RaisError (@statusMsg, 10, 1) With NoWait;
 
        End
    End
 
    RaisError ('Success!', 10, 1) With NoWait;
 
End Try
/* Handle your errors */
Begin Catch
 
        Set @statusMsg = 'An error has occurred and the last '
                         + 'transaction has been rolled back. '
                         + 'Last record successfully updated was '
                         + 'record_id = ' 
                         + Cast((@minID + (@batchSize - 1)) 
                            As varchar(10));
 
        RaisError (@statusMsg, 16, 1) With NoWait;
 
        /* Return the error message */
        Select Error_Number()
            , Error_Procedure()
		    , DB_Name()
		    , Error_Line()
		    , Error_Message()
		    , Error_Severity()
		    , Error_State();
 
End Catch;
 
/* -------------------------- Clean-Up ----------------------------
Drop Table dbo.scratch_largeUpdate_control;
----------------------------------------------------------------- */

Index Interrogation Script

As a continuation of the last post on indexes, here’s a script I wrote for index interrogation.  This stored procedure was the result of repeatedly having to lookup information on included columns.

If ObjectProperty(Object_ID('dbo.dba_indexLookup_sp'), 
        N'IsProcedure') Is Null
Begin
    Execute ('Create Procedure dbo.dba_indexLookup_sp 
        As Print ''Hello World!''')
    RaisError('Procedure dbo.dba_indexLookup_sp created.', 10, 1);
End;
Go
 
Set ANSI_Nulls On;
Set Ansi_Padding On;
Set Ansi_Warnings On;
Set ArithAbort On;
Set Concat_Null_Yields_Null On;
Set NoCount On;
Set Numeric_RoundAbort Off;
Set Quoted_Identifier On;
Go
 
Alter Procedure dbo.dba_indexLookup_sp
 
        /* Declare Parameters */
        @tableName  varchar(128)  =  Null
As
/***********************************************************************
    Name:       dba_indexLookup_sp
 
    Author:     Michelle F. Ufford
 
    Purpose:    Retrieves index information for the specified table.
 
    Notes:      If the tableName is left null, it will return index 
                information for all tables and indexes.
 
    Called by:  DBA
 
    Date        User   Description
    --------------------------------------------------------------------
    2008-10-28  MFU    Initial Release
************************************************************************
    Exec dbo.dba_indexLookup_sp
        @tableName = 'myTableName';
***********************************************************************/
 
Set NoCount On;
Set XACT_Abort On;
 
Begin
 
    Declare @objectID int;
 
    If @tableName Is Not Null
        Set @objectID = Object_ID(@tableName);
 
    With indexCTE(partition_scheme_name
                , partition_function_name
                , data_space_id)
    As (
        Select sps.name
            , spf.name
            , sps.data_space_id
        From sys.partition_schemes As sps
        Join sys.partition_functions As spf
            On sps.function_id = spf.function_id
    )
 
    Select st.name As 'table_name'
        , IsNull(ix.name, '') As 'index_name'
        , ix.object_id
        , ix.index_id
		, Cast(
            Case When ix.index_id = 1 
                    Then 'clustered' 
                When ix.index_id =0
                    Then 'heap'
                Else 'nonclustered' End
			+ Case When ix.ignore_dup_key <> 0 
                Then ', ignore duplicate keys' 
                    Else '' End
			+ Case When ix.is_unique <> 0 
                Then ', unique' 
                    Else '' End
			+ Case When ix.is_primary_key <> 0 
                Then ', primary key' Else '' End As varchar(210)
            ) As 'index_description'
        , IsNull(Replace( Replace( Replace(
            (   
                Select c.name As 'columnName'
                From sys.index_columns As sic
                Join sys.columns As c 
                    On c.column_id = sic.column_id 
                    And c.object_id = sic.object_id
                Where sic.object_id = ix.object_id
                    And sic.index_id = ix.index_id
                    And is_included_column = 0
                Order By sic.index_column_id
                For XML Raw)
                , '"/><row columnName="', ', ')
                , '<row columnName="', '')
                , '"/>', ''), '')
            As 'indexed_columns'
        , IsNull(Replace( Replace( Replace(
            (   
                Select c.name As 'columnName'
                From sys.index_columns As sic
                Join sys.columns As c 
                    On c.column_id = sic.column_id 
                    And c.object_id = sic.object_id
                Where sic.object_id = ix.object_id
                    And sic.index_id = ix.index_id
                    And is_included_column = 1
                Order By sic.index_column_id
                For XML Raw)
                , '"/><row columnName="', ', ')
                , '<row columnName="', '')
                , '"/>', ''), '')
            As 'included_columns'
        , IsNull(cte.partition_scheme_name, '') 
            As 'partition_scheme_name'
        , Count(partition_number) As 'partition_count'
        , Sum(rows) As 'row_count'
    From sys.indexes As ix
    Join sys.partitions As sp
        On ix.object_id = sp.object_id
        And ix.index_id = sp.index_id
    Join sys.tables As st
        On ix.object_id = st.object_id
    Left Join indexCTE As cte
        On ix.data_space_id = cte.data_space_id
    Where ix.object_id = IsNull(@objectID, ix.object_id)
    Group By st.name
        , IsNull(ix.name, '')
        , ix.object_id
        , ix.index_id
		, Cast(
            Case When ix.index_id = 1 
                    Then 'clustered' 
                When ix.index_id =0
                    Then 'heap'
                Else 'nonclustered' End
			+ Case When ix.ignore_dup_key <> 0 
                Then ', ignore duplicate keys' 
                    Else '' End
			+ Case When ix.is_unique <> 0 
                Then ', unique' 
                    Else '' End
			+ Case When ix.is_primary_key <> 0 
                Then ', primary key' Else '' End As varchar(210)
            )
        , IsNull(cte.partition_scheme_name, '')
        , IsNull(cte.partition_function_name, '')
    Order By table_name
        , index_id;
 
    Set NoCount Off;
    Return 0;
End
Go

Index Defrag Script

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

Previously I posted that it’s important to keep your indexes in shape with weekly or (preferably) nightly defrags. Below is a script I use to execute nightly defrags in SQL 2005 Enterprise. I can’t claim complete credit… I believe this is a script I originally found on Microsoft and heavily modified to meet my needs.

You may want to modify the script if you’re doing much with XML or LOB’s. Also keep in mind that this is hitting the sys.dm_db_index_physical_stats table to view fragmentation information, which can be resource intensive.

If you’re not familiar with index defragmentation, check out “Alter Index” on Books Online.

If Not Exists(Select object_id From sys.tables 
                Where [name] = N'dba_indexDefragLog')
Begin
 
    Create Table dbo.dba_indexDefragLog
    (
          indexDefrag_id    int identity(1,1)   not null
        , objectID          int                 not null
        , objectName        nvarchar(130)       not null
        , indexID           int                 not null
        , indexName         nvarchar(130)       not null
        , partitionNumber   smallint            not null
        , fragmentation     float               not null
        , dateTimeStart     datetime            not null
        , durationSeconds   int                 not null
        Constraint PK_indexDefragLog 
            Primary Key Clustered (indexDefrag_id)
    );
 
    Print 'dba_indexDefragLog Table Created';
End
 
If ObjectProperty(Object_ID('dbo.dba_indexDefrag_sp'), 
        N'IsProcedure') Is Null
Begin
    Execute ('Create Procedure dbo.dba_indexDefrag_sp As 
                Print ''Hello World!''')
    RaisError('Procedure dba_indexDefrag_sp created.', 10, 1);
End;
Go
 
Set ANSI_Nulls On;
Set Ansi_Padding On;
Set Ansi_Warnings On;
Set ArithAbort On;
Set Concat_Null_Yields_Null On;
Set NoCount On;
Set Numeric_RoundAbort Off;
Set Quoted_Identifier On;
Go
 
Alter 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 */
    , @onlineRebuild        bit             = 1     
        /* 1 = online rebuild; 0 = offline rebuild */
    , @executeSQL           bit             = 1     
        /* 1 = execute; 0 = print command only */
    , @tableName            varchar(4000)   = Null  
        /* Option to specify a table name */
    , @printCommands        bit             = 0     
        /* 1 = print commands; 0 = do not print commands */
    , @defragDelay          char(8)         = '00:00:05'
        /* time to wait between defrag commands */
As
/***********************************************************************
    Name:       dba_indexDefrag_sp
 
    Author:     Michelle F. Ufford
 
    Purpose:    Defrags all indexes for the current database
 
    Notes:
 
    CAUTION: Monitor transaction log when executing for the first time!
 
      @minFragmentation     defaulted to 10%, will not defrag if 
                            fragmentation if less than specified 
 
      @rebuildThreshold     defaulted to 30% as recommended by 
                            Microsoft in BOL;
                            > than 30% will result in rebuild instead
 
      @onlineRebuild        1 = online rebuild; 
                            0 = offline rebuild
 
      @executeSQL           1 = execute the SQL generated by this proc; 
                            0 = print command only
 
      @tableName            Specify if you only want to defrag indexes 
                            for a specific table
 
      @printCommands        1 = print commands to screen; 
                            0 = do not print commands
 
      @defragDelay          time to wait between defrag commands; 
                            gives the server a little time to catch up 
 
    Called by:  SQL Agent Job or DBA
 
    Date        Initials	Description
    -------------------------------------------------------------------
    2008-10-27  MFU         Initial Release
***********************************************************************
    Exec dbo.dba_indexDefrag_sp
	      @executeSQL    = 0
        , @printCommands = 1;
***********************************************************************/
 
Set NoCount On;
Set XACT_Abort On;
 
Begin
 
    /* Declare our variables */
    Declare   @objectID         int
            , @indexID          int
            , @partitionCount   bigint
            , @schemaName       nvarchar(130)
            , @objectName       nvarchar(130)
            , @indexName        nvarchar(130)
            , @partitionNumber  smallint
            , @partitions       smallint
            , @fragmentation    float
            , @sqlCommand       nvarchar(4000)
            , @rebuildCommand   nvarchar(200)
            , @dateTimeStart    datetime
            , @dateTimeEnd      datetime
            , @containsLOB      bit;
 
    /* Just a little validation... */
    If @minFragmentation Not Between 0.00 And 100.0
        Set @minFragmentation = 10.0;
 
    If @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';
 
    /* Determine which indexes to defrag using our 
       user-defined parameters */
    Select
          Object_ID AS objectID
        , index_id AS indexID
        , partition_number AS partitionNumber
        , avg_fragmentation_in_percent AS fragmentation
        , 0 As 'defragStatus' 
            /* 0 = unprocessed, 1 = processed */
    Into #indexDefragList
    From sys.dm_db_index_physical_stats 
        (DB_ID(), Object_Id(@tableName), NULL , NULL, N'Limited')
    Where avg_fragmentation_in_percent > @minFragmentation 
        And index_id > 0
    Option (MaxDop 1);
 
    /* Create a clustered index to boost performance a little */
    Create Clustered Index CIX_temp_indexDefragList
        On #indexDefragList(objectID, indexID, partitionNumber);
 
    /* Begin our loop for defragging */
    While (Select Count(*) From #indexDefragList 
            Where defragStatus = 0) > 0
    Begin
 
        /* Grab the most fragmented index first to defrag */
        Select Top 1 
              @objectID         = objectID
            , @fragmentation    = fragmentation
            , @indexID          = indexID
            , @partitionNumber  = partitionNumber
        From #indexDefragList
        Where defragStatus = 0
        Order By fragmentation Desc;
 
        /* Look up index information */
        Select @objectName = QuoteName(o.name)
             , @schemaName = QuoteName(s.name)
        From sys.objects As o
        Inner Join sys.schemas As s 
            On s.schema_id = o.schema_id
        Where o.object_id = @objectID;
 
        Select @indexName = QuoteName(name)
        From sys.indexes 
        Where object_id = @objectID
            And index_id = @indexID
            And type > 0;
 
        /* Determine if the index is partitioned */
        Select @partitionCount = Count(*)
        From sys.partitions
        Where object_id = @objectID 
            And index_id = @indexID;
 
        /* Look for LOBs */
        Select Top 1 
            @containsLOB = column_id
        From sys.columns With (NoLock) 
        Where  
            [object_id] = @objectID
            And (system_type_id In (34, 35, 99) 
            -- 34 = image, 35 = text, 99 = ntext
                    Or max_length = -1); 
            -- varbinary(max), varchar(max), nvarchar(max), xml
 
        /* See if we should rebuild or reorganize; handle thusly */
        If @fragmentation < @rebuildThreshold And @partitionCount <= 1
            Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' 
                + @schemaName + N'.' + @objectName + N' ReOrganize';
 
        If @fragmentation >= @rebuildThreshold 
            And IsNull(@containsLOB, 0) = 0 
                -- Cannot rebuild if the table has one or more LOB
            And @partitionCount <= 1
        Begin
 
            /* We should always rebuild online if possible 
                (SQL 2005 Enterprise) */
            If @onlineRebuild = 0 
                Set @rebuildCommand = N' Rebuild With 
                    (Online = Off, MaxDop = 1)';
            Else
                Set @rebuildCommand = N' Rebuild With 
                    (Online = On, MaxDop = 1)';
 
            Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' 
                + @schemaName + N'.' + @objectName + @rebuildCommand;
        End;
 
        /* If our index is partitioned, we should always reorganize */
        If @partitionCount > 1
            Set @sqlCommand = N'Alter Index ' + @indexName + N' On ' 
                + @schemaName + N'.' + @objectName + N' ReOrganize'
                + N' Partition = ' 
                + Cast(@partitionNumber As nvarchar(10)); 
                -- no MaxDop needed, single threaded operation
 
        /* Are we executing the SQL?  If so, do it */
        If @executeSQL = 1
        Begin
 
            /* Grab the time for logging purposes */
            Set @dateTimeStart  = GetDate();
            Execute (@sqlCommand);
            Set @dateTimeEnd  = GetDate();
 
            /* Log our actions */
            Insert Into dbo.dba_indexDefragLog
            (
                  objectID
                , objectName
                , indexID
                , indexName
                , partitionNumber
                , fragmentation
                , dateTimeStart
                , durationSeconds
            )
            Select
                  @objectID
                , @objectName
                , @indexID
                , @indexName
                , @partitionNumber
                , @fragmentation
                , @dateTimeStart
                , DateDiff(second, @dateTimeStart, @dateTimeEnd);
 
            /* Just a little breather for the server */
            WaitFor Delay @defragDelay;
 
            /* Print if specified to do so */
            If @printCommands = 1
                Print N'Executed: ' + @sqlCommand;
        End
        Else
        /* Looks like we're not executing, just print 
            the commands */
        Begin
            If @printCommands = 1
                Print @sqlCommand;
        End
 
        /* Update our index defrag list when we've
            finished with that index */
        Update #indexDefragList
        Set defragStatus = 1
        Where objectID         = @objectID
          And indexID          = @indexID
          And partitionNumber  = @partitionNumber;
 
    End
 
    /* When everything is done, make sure to get rid of 
        our temp table */
    Drop Table #indexDefragList;
 
    Set NoCount Off;
    Return 0
End
Go
 
Set Quoted_Identifier Off 
Set ANSI_Nulls On
Go

20081117 UPDATE: Check out my Updated Index Defrag Script for SQL 2005 and 2008.