SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Customized SqlFool script - Index Reorg/Rebuild
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

portrman
Starting Member

24 Posts

Posted - 03/19/2010 :  12:47:27  Show Profile  Reply with Quote
Here's my customized SqlFool.com sql. I'm a relative newbie to some of this, but I had a former DBA here look my changes over and liked them. The one change he suggested that I haven't done is make #indexDefragList a global table ##indexDefragList so you can access it directly from another session. I also want to add more to the log table to include what type of reorg/rebuild was done and fragmentation post work. The last major thing I want to add is if offline isn't allowed, then go do a reorg instead, so at least something is done.

Major changes:
UpdateStats should not run if executeSQL = 0
, added option to prevent offline rebuilds
, Clustered index can now be rebuild online if no LOBs in table
, NonClustered index can also be rebuild online if no lobs on index
, fixed bug where index names in log were off


USE [DCArchive]
GO
/****** Object:  StoredProcedure [dbo].[dba_indexDefrag_sp]    Script Date: 03/19/2010 09:37:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
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 */
    , @executeSQL           BIT             = 1     
        /* 1 = execute; 0 = print command only */
    , @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 */
    , @scanMode             VARCHAR(10)     = N'LIMITED'
        /* Options are LIMITED, SAMPLED, and DETAILED */
    , @onlineRebuild        BIT             = 1     
        /* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
    , @allowOfflineRebuild BIT				= 0
		/* 1 = offline rebuilds allowed;  0 = offline rebuilds will not take place */
    , @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 */
    , @rebuildStats         BIT             = 1
        /* option to rebuild stats after completed index defrags */
 
AS
/*********************************************************************************
    Name:       dba_indexDefrag_sp
 
    Author:     Michelle Ufford, http://sqlfool.com
 
    Purpose:    Defrags all indexes for the current database
 
    Notes:
 
    CAUTION: TRANSACTION LOG SIZE SHOULD BE MONITORED CLOSELY WHEN DEFRAGMENTING.
 
      @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
 
      @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.
 
      @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.
 
      @onlineRebuild        1 = online rebuild; 
                            0 = offline rebuild

      @allowOfflineRebuild  1 = offline rebuilds allowed; 
                            0 = offline rebuilds will not take place
 
      @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
 
      @rebuildStats         Affects only statistics that need to be rebuilt
                            1 = rebuild stats
                            0 = do not rebuild stats
 
    Called by:  SQL Agent Job or DBA
 
    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)
    2010-03-15  CMB         3.1		UpdateStats should not run if executeSQL = 0
									, added option to prevent offline rebuilds
									, Clustered index can now be rebuild online if no LOBs in table
									, NonClustered index can also be rebuild online if no lobs on index
									, fixed bug where index names in log were off
*********************************************************************************
    Exec dbo.dba_indexDefrag_sp
          @executeSQL           = 0
        , @allowOfflineRebuild  = 0
        , @database				= 'CiqCentral'
        , @minFragmentation		= 1.0
        , @rebuildThreshold		= 1.0
        , @onlineRebuild		= 1
        , @printCommands        = 1
        , @debugMode            = 1
        , @printFragmentation   = 1;
*********************************************************************************/																
 
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;
SET Quoted_Identifier ON;
 
BEGIN
 
    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
            , @indexType			NVARCHAR(60)
            , @fragmentation        FLOAT
            , @pageCount            INT
            , @sqlCommand           NVARCHAR(4000)
            , @rebuildCommand       NVARCHAR(200)
            , @dateTimeStart        DATETIME
            , @dateTimeEnd          DATETIME
            , @tableContainsLOB     BIT
            , @indexContainsLOB		BIT
            , @editionCheck         BIT
            , @debugMessage         VARCHAR(128)
            , @updateSQL            NVARCHAR(4000)
            , @partitionSQL         NVARCHAR(4000)
            , @partitionSQL_Param   NVARCHAR(1000)
            , @LOB_SQL              NVARCHAR(4000)
            , @LOB_SQL_Param        NVARCHAR(1000)
            , @rebuildStatsID       INT
            , @rebuildStatsSQL      NVARCHAR(1000)
            , @indexDefrag_id       INT;
 
    /* Create our temporary tables */
    CREATE TABLE #indexDefragList
    (
          databaseID        INT
        , databaseName      NVARCHAR(128)
        , objectID          INT
        , indexID           INT
        , partitionNumber   SMALLINT
        , indexType			NVARCHAR(60)
        , fragmentation     FLOAT
        , page_count        INT
        , defragStatus      BIT
        , schemaName        NVARCHAR(128)   Null
        , objectName        NVARCHAR(128)   Null
        , indexName         NVARCHAR(128)   Null
    );
 
    CREATE TABLE #databaseList
    (
          databaseID        INT
        , databaseName      VARCHAR(128)
        , scanStatus        BIT
        , statsStatus       BIT
    );
 
    CREATE TABLE #processor 
    (
          [INDEX]           INT
        , Name              VARCHAR(128)
        , Internal_Value    INT
        , Character_Value   INT
    );
 
    IF @debugMode = 1 RAISERROR('Beginning validation...', 0, 42) WITH NoWait;
 
    /* 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';
 
    IF @scanMode Not In ('LIMITED', 'SAMPLED', 'DETAILED')
        SET @scanMode = 'LIMITED';
 
    /* 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
 
    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
        , 0 -- statistics not yet updated
    FROM sys.databases
    WHERE name = IsNull(@DATABASE, name)
        And [name] Not In ('master', 'tempdb')-- exclude system databases
        And [STATE] = 0; -- state must be ONLINE
 
    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 #indexDefragList
        SELECT
              database_id AS databaseID
            , QUOTENAME(DB_NAME(database_id)) AS 'databaseName'
            , [OBJECT_ID] AS objectID
            , index_id AS indexID
            , partition_number AS partitionNumber
            , index_type_desc as indexType
            , avg_fragmentation_in_percent AS fragmentation
            , page_count 
            , 0 AS 'defragStatus' /* 0 = unprocessed, 1 = processed */
            , Null AS 'schemaName'
            , Null AS 'objectName'
            , Null AS 'indexName'
        FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode)
        WHERE avg_fragmentation_in_percent >= @minFragmentation 
            And index_id > 0 -- ignore heaps
            And page_count > 8 -- ignore objects with less than 1 extent
            And index_level = 0 -- leaf-level nodes only, supports @scanMode
        OPTION (MaxDop 2);
 
        /* Keep track of which databases have already been scanned */
        UPDATE #databaseList
        SET scanStatus = 1
        WHERE databaseID = @databaseID;
 
    END
 
    CREATE CLUSTERED INDEX CIX_temp_indexDefragList
        ON #indexDefragList(databaseID, objectID, indexID, partitionNumber);
 
    /* Delete any indexes from our to-do that are also in our exclusion list for today */
    DELETE idl
    FROM #indexDefragList AS idl
    Join dbo.dba_indexDefragExclusion AS ide
        ON idl.databaseID = ide.databaseID
        And idl.objectID = ide.objectID
        And idl.indexID = ide.indexID
    WHERE exclusionMask & POWER(2, DATEPART(weekday, GETDATE())-1) > 0;
 
    SELECT @debugMessage = 'Looping through our list... there''s ' + CAST(COUNT(*) AS VARCHAR(10)) + ' indexes to defrag!'
    FROM #indexDefragList;
 
    IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait;
 
    /* Begin our loop for defragging */
    WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0
    BEGIN
 
        IF @debugMode = 1 RAISERROR('  Picking an index to beat into shape...', 0, 42) WITH NoWait;
 
        /* Grab the most fragmented index first to defrag */
        SELECT TOP 1 
              @objectID         = objectID
            , @indexID          = indexID
            , @databaseID       = databaseID
            , @databaseName     = databaseName
            , @fragmentation    = fragmentation
            , @partitionNumber  = partitionNumber
            , @indexType        = indexType
            , @pageCount        = page_count
        FROM #indexDefragList
        WHERE defragStatus = 0
        ORDER BY fragmentation DESC;
 
        IF @debugMode = 1 RAISERROR('  Looking up the specifics for our index...', 0, 42) WITH NoWait;
 
        /* Look up index information */
        SELECT @updateSQL = N'Update idl
            Set schemaName = QuoteName(s.name)
                , objectName = QuoteName(o.name)
                , indexName = QuoteName(i.name)
            From #indexDefragList As idl
            Inner Join ' + @databaseName + '.sys.objects As o
                On idl.objectID = o.object_id
            Inner Join ' + @databaseName + '.sys.indexes As i
                On o.object_id = i.object_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 idl.indexID = ' + CAST(@indexID AS VARCHAR(10)) + '
                And idl.databaseID = ' + CAST(@databaseID AS VARCHAR(10));

        EXECUTE SP_EXECUTESQL @updateSQL;
 
        /* Grab our object names */
        SELECT @objectName  = objectName
            , @schemaName   = schemaName
            , @indexName    = indexName
        FROM #indexDefragList
        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''s any LOBs to be handled...', 0, 42) WITH NoWait;
 
        /* Determine if the table contains LOBs */
        SELECT @LOB_SQL = ' Select @tableContainsLOB_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 = '@tableContainsLOB_OUT int OutPut';
 
        EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @tableContainsLOB_OUT = @tableContainsLOB OUTPUT;

        /* Determine if index has any LOB columns */
        SELECT @LOB_SQL = ' Select @indexContainsLOB_OUT = Count(*)
							From ' + @databaseName + '.sys.indexes i
							Join ' + @databaseName + '.sys.index_columns ic ON ic.object_id = i.object_id
							Join ' + @databaseName + '.information_schema.columns c ON c.ordinal_position = ic.column_id
							Where c.table_schema = ''' + @schemaName + ''''
								+ ' And c.table_name = ''' + @objectName + ''''
								+ ' And i.name = ''' + @indexName + ''''
								+ ' And ic.is_included_column = 1
								And (c.data_type In (''text'', ''ntext'', ''image'')
								Or c.character_maximum_length = -1);'
				, @LOB_SQL_Param = '@indexContainsLOB_OUT int OutPut';
		
		EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @indexContainsLOB_OUT = @indexContainsLOB OUTPUT;
 
        IF @debugMode = 1 RAISERROR('  Building our SQL statements...', 0, 42) WITH NoWait;
 
        SET @sqlCommand = N'Alter Index ' + @indexName + N' On ' + @databaseName + N'.' 
                                + @schemaName + N'.' + @objectName;
        
        /* If not a lot of fragmention
			OR multiple partitions
			OR a clustered index on a table with LOBs
		  THEN reorganize */
        IF @fragmentation < @rebuildThreshold 
			Or @partitionCount > 1
			Or (@indexType = 'CLUSTERED INDEX' And ISNULL(@tableContainsLOB, 0) = 1)
        BEGIN
			SET @sqlCommand = @sqlCommand + N' ReOrganize';
			
			IF @partitionCount > 1
				SET @sqlCommand = @sqlCommand + N' Partition = '
								+ CAST(@partitionNumber AS NVARCHAR(10));
        END;
        
        /* If the index is heavily fragments
			 and has no partitions
			 and 
				is a non-clustered index + index has no LOB columns
				OR
				is a clustered index + no LOB columns in table
			THEN rebuild */
        IF @fragmentation >= @rebuildThreshold 
			And @partitionCount <= 1
			And (
				(@indexType = 'NONCLUSTERED INDEX' And ISNULL(@indexContainsLOB, 0) = 0)
				OR
				(@indexType = 'CLUSTERED INDEX' And ISNULL(@tableContainsLOB, 0) = 0)
				)
			
        BEGIN
        
			/*  If OFFLINE will be used, see if offline is allowed, skip if not allowed */
			IF (@onlineRebuild != 1 OR @editionCheck != 1 )AND @allowOfflineRebuild = 0
			BEGIN
				SET @debugMessage = 'Offline Not Allowed, will not rebuild: ' 
								+ @indexName + N' On ' + @databaseName + N'.'
								+ @schemaName + N'.' + @objectName;
				SET @sqlCommand = '';
				IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait;
			END
			ELSE
			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 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 = @sqlCommand + @rebuildCommand;
            
            END
 
        END;
 
        /* Are we executing the SQL?  If so, do it */
        IF @executeSQL = 1
        BEGIN
 
            IF @debugMode = 1 RAISERROR('  Executing SQL statements...', 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
            )
            SELECT
                  @databaseID
                , @databaseName
                , @objectID
                , @objectName
                , @indexID
                , @indexName
                , @partitionNumber
                , @fragmentation
                , @pageCount
                , @dateTimeStart;
 
            SET @indexDefrag_id = SCOPE_IDENTITY();
            
            IF @printCommands = 1 PRINT '       Executing: ' + IsNull(@sqlCommand, 'error!');
 
            /* 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;
 
            /* Just a little breather for the server */
            WAITFOR Delay @defragDelay;
        END
        ELSE
        BEGIN
			IF @printCommands = 1 PRINT '       Printing: ' + IsNull(@sqlCommand, 'error!');
        END
 
        IF @debugMode = 1 RAISERROR('  Updating our index defrag status...', 0, 42) WITH NoWait;
 
        /* Update our index defrag list so we know we've finished with that index */
        UPDATE #indexDefragList
        SET defragStatus = 1
        WHERE databaseID       = @databaseID
          And objectID         = @objectID
          And indexID          = @indexID
          And partitionNumber  = @partitionNumber;
 
    END
 
    /* Do we want to output our fragmentation results? */
    IF @printFragmentation = 1
    BEGIN
 
        IF @debugMode = 1 RAISERROR('  Displaying fragmentation results...', 0, 42) WITH NoWait;
 
        SELECT databaseID
            , databaseName
            , objectID
            , objectName
            , indexID
            , indexName
            , fragmentation
            , page_count
        FROM #indexDefragList;
 
    END;
 
    /* Do we want to rebuild stats? */
    IF @rebuildStats = 1
    BEGIN
 
        WHILE Exists(SELECT TOP 1 * FROM #databaseList WHERE statsStatus = 0)
        BEGIN
 
            /* Build our SQL statement to update stats */
            SELECT TOP 1 @rebuildStatsSQL = 'Use [' + databaseName + ']; ' + 
                                            'Execute sp_updatestats;'
                    , @rebuildStatsID = databaseID
            FROM #databaseList
            WHERE statsStatus = 0;
 
            SET @debugMessage = 'Printing Stats Rebuild: ' + @rebuildStatsSQL;
 
            IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait;
 
            IF @executeSQL = 1
			BEGIN
				/* Execute our stats update! */
				EXECUTE SP_EXECUTESQL @rebuildStatsSQL;
				
				IF @debugMode = 1 RAISERROR('Executing update stats...', 0, 42) WITH NoWait;
			END

 
            /* Keep track of which databases have been updated */
            UPDATE #databaseList 
            SET statsStatus = 1
            WHERE databaseID = @rebuildStatsID;
 
        END;
    END;
 
    /* When everything is said and done, make sure to get rid of our temp table */
    DROP TABLE #indexDefragList;
    DROP TABLE #databaseList;
    DROP TABLE #processor;
 
    IF @debugMode = 1 RAISERROR('DONE!  Thank you for taking care of your indexes!  :)', 0, 42) WITH NoWait;
 
    SET NOCOUNT OFF;
    RETURN 0
END

Edited by - portrman on 05/17/2012 14:11:42

portrman
Starting Member

24 Posts

Posted - 03/19/2010 :  12:52:13  Show Profile  Reply with Quote
Umm... don't use the example exec statement. I used that for testing only. Either comment out the @minFragmentation & @rebuildThreshold params or set reasonable ones such as 10 and 30, respectively.

Exec dbo.dba_indexDefrag_sp
@executeSQL = 0
, @allowOfflineRebuild = 0
, @database = 'CiqCentral'
, @minFragmentation = 10
, @rebuildThreshold = 30
, @onlineRebuild = 1
, @printCommands = 1
, @debugMode = 1
, @printFragmentation = 1;
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 03/19/2010 :  13:12:44  Show Profile  Reply with Quote
Thanks, upgraded my script from SQL 2000 is on my ToDo list - so this will be food for thought, along with Tara'sscript.

For completeness here is a link to the original:

http://sqlfool.com/2009/06/index-defrag-script-v30/

(It would help if you re-edit your post and put [CODE] ... [/CODE] tags around your code so it formats nicely )

Edited by - Kristen on 03/19/2010 13:14:06
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 03/19/2010 :  15:55:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by portrman

Here's my customized SqlFool.com sql. I'm a relative newbie to some of this, but I had a former DBA here look my changes over and liked them. The one change he suggested that I haven't done is make #indexDefragList a global table ##indexDefragList so you can access it directly from another session. I also want to add more to the log table to include what type of reorg/rebuild was done and fragmentation post work. The last major thing I want to add is if offline isn't allowed, then go do a reorg instead, so at least something is done.



When wouldn't OFFLINE be allowed?

I don't understand the point of making the temp table global. Who elese would want to access it? I certainly wouldn't want any other process running DBA scripts like this is running.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.47 seconds. Powered By: Snitz Forums 2000