Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
22858 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
38200 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  
 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.48 seconds. Powered By: Snitz Forums 2000