none
Rebuild indexes of ALL databases, ALL Tables with provided fragmentation

    Question

  • hi ,Experts I need some help on rebuild indexes of All databases on All fragmented tables , Indexes ..for some reason i am not allowed to use http://ola.hallengren.com/ Scripts ....

    I use below script from mssqltips.com to rebuild Indexes of all Tables indexes per database,  1 Database at a time ...can anyone help me in rewriting the query to go through each database and rebuild  or re organize indexes based on  fragmentation % provided ..

    ---THIS WORKS FOR INDIVIDUAL DATABSES ONLY 


    --http://www.mssqltips.com/sqlservertip/1791/fixing-index-fragmentation-in-sql-server-2005-and-sql-server-2008/
    --Description : This script reorganizes and rebuilds the index if the fragmentation level is higher the given threshold
    -- You can define the threshold for reorganize as well as for rebuild and script will work accordingly
    -- INPUTS : @fillfactor - While rebuilding index what would be FILLFACTOR for new index
    -- @FragmentationThresholdForReorganizeTableLowerLimit - Fragmentation Level lower threshold to check for reorganizing the table, if the fragmentation is higher than this level, it will be considered for reorganize
    -- @@FragmentationThresholdForRebuildTableLowerLimit - Fragmentation Level lower threshold to check for rebuilding the table, if the fragmentation is higher than this level, it will be considered for rebuild
    -- NOTES : PRINT statements are all queued up and don't show up until the entire script is printed. However, there is an alternative to PRINTing messages. 
    -- You can raise an error that isn't really an error (code of 0) and you'll get the same effect--message will be printed immediately.
    DECLARE @cmd NVARCHAR(1000) 
    DECLARE @Table VARCHAR(255) 
    DECLARE @SchemaName VARCHAR(255)
    DECLARE @IndexName VARCHAR(255)
    DECLARE @AvgFragmentationInPercent DECIMAL
    DECLARE @fillfactor INT 
    DECLARE @FragmentationThresholdForReorganizeTableLowerLimit VARCHAR(10)
    DECLARE @FragmentationThresholdForRebuildTableLowerLimit VARCHAR(10)
    DECLARE @Message VARCHAR(1000)

    SET NOCOUNT ON

    --You can specify your customized value for reorganize and rebuild indexes, the default values
    --of 10 and 30 means index will be reorgnized if the fragmentation level is more than equal to 10 
    --and less than 30, if the fragmentation level is more than equal to 30 then index will be rebuilt
    SET @fillfactor = 90 
    SET @FragmentationThresholdForReorganizeTableLowerLimit = '10.0' -- Percent
    SET @FragmentationThresholdForRebuildTableLowerLimit = '30.0' -- Percent

    BEGIN TRY

    -- ensure the temporary table does not exist
    IF (SELECT OBJECT_ID('tempdb..#FramentedTableList')) IS NOT NULL
    DROP TABLE #FramentedTableList;

    SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Retrieving indexes with high fragmentation from ' + DB_NAME() + ' database.'
    RAISERROR(@Message, 0, 1) WITH NOWAIT


    SELECT OBJECT_NAME(IPS.OBJECT_ID) AS [TableName], avg_fragmentation_in_percent, SI.name [IndexName], 
    schema_name(ST.schema_id) AS [SchemaName], 0 AS IsProcessed INTO #FramentedTableList
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) IPS
    JOIN sys.tables ST WITH (nolock) ON IPS.OBJECT_ID = ST.OBJECT_ID
    JOIN sys.indexes SI WITH (nolock) ON IPS.OBJECT_ID = SI.OBJECT_ID AND IPS.index_id = SI.index_id
    WHERE ST.is_ms_shipped = 0 AND SI.name IS NOT NULL
    AND avg_fragmentation_in_percent >= CONVERT(DECIMAL, @FragmentationThresholdForReorganizeTableLowerLimit) 
    ORDER BY avg_fragmentation_in_percent DESC

    SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Retrieved indexes with high fragmentation from ' + DB_NAME() + ' database.'

    RAISERROR(@Message, 0, 1) WITH NOWAIT
    RAISERROR('', 0, 1) WITH NOWAIT

    WHILE EXISTS ( SELECT 1 FROM #FramentedTableList WHERE IsProcessed = 0 )
    BEGIN

      SELECT TOP 1 @Table = TableName, @AvgFragmentationInPercent = avg_fragmentation_in_percent, 
      @SchemaName = SchemaName, @IndexName = IndexName
      FROM #FramentedTableList
      WHERE IsProcessed = 0

      --Reorganizing the index
      IF((@AvgFragmentationInPercent >= @FragmentationThresholdForReorganizeTableLowerLimit) AND (@AvgFragmentationInPercent < @FragmentationThresholdForRebuildTableLowerLimit))
      BEGIN
        SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Reorganizing Index for [' + @Table + '] which has avg_fragmentation_in_percent = ' + CONVERT(VARCHAR, @AvgFragmentationInPercent) + '.'
        RAISERROR(@Message, 0, 1) WITH NOWAIT
        SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REORGANIZE' 
        EXEC (@cmd)
        --PRINT @cmd 
        SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Reorganize Index completed successfully for [' + @Table + '].' 
        RAISERROR(@Message, 0, 1) WITH NOWAIT
        RAISERROR('', 0, 1) WITH NOWAIT
      END
      --Rebuilding the index
      ELSE IF (@AvgFragmentationInPercent >= @FragmentationThresholdForRebuildTableLowerLimit )
      BEGIN
        SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Rebuilding Index for [' + @Table + '] which has avg_fragmentation_in_percent = ' + CONVERT(VARCHAR, @AvgFragmentationInPercent) + '.'
        RAISERROR(@Message, 0, 1) WITH NOWAIT
        SET @cmd = 'ALTER INDEX ' + @IndexName + ' ON [' + RTRIM(LTRIM(@SchemaName)) + '].[' + RTRIM(LTRIM(@Table)) + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ', STATISTICS_NORECOMPUTE = OFF)' 
        EXEC (@cmd)
        --PRINT @cmd
        SET @Message = 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' - Rebuild Index completed successfully for [' + @Table + '].'
        RAISERROR(@Message, 0, 1) WITH NOWAIT
        RAISERROR('', 0, 1) WITH NOWAIT
      END

      UPDATE #FramentedTableList
      SET IsProcessed = 1 
      WHERE TableName = @Table
      AND IndexName = @IndexName
    END

    DROP TABLE #FramentedTableList 

    END TRY

    BEGIN CATCH
      PRINT 'DATE : ' + CONVERT(VARCHAR, GETDATE()) + ' There is some run time exception.'
      PRINT 'ERROR CODE : ' + CONVERT(VARCHAR, ERROR_NUMBER()) 
      PRINT 'ERROR MESSAGE : ' + ERROR_MESSAGE()
    END CATCH 


    Thanks


    Friday, November 02, 2012 12:43 PM

Answers

  • easiest way, create this script as store procedure on all database bases.

    & run below code from your sql job or on adhoc basis to rebuild indexes of all databases.

    USE SP_MSFOREACHDB' USE [?]

    exec USP_rebuildindexes'

    rebuild index is a costly process, analysis the time fram very well to schedule it.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, November 02, 2012 1:54 PM

All replies

  • easiest way, create this script as store procedure on all database bases.

    & run below code from your sql job or on adhoc basis to rebuild indexes of all databases.

    USE SP_MSFOREACHDB' USE [?]

    exec USP_rebuildindexes'

    rebuild index is a costly process, analysis the time fram very well to schedule it.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, November 02, 2012 1:54 PM
  • Hi,

    I see this type of request all the time, possibly voting for this connect item to be re-opened and addressed will help the many others that have the same issue where companies do not allow internet scripts i.e. ola hallengren, on their servers https://connect.microsoft.com/SQLServer/feedback/details/490434/improve-maintenance-plans-to-rebuild-reorg-indexes-based-on-fragmentation-percent (this was requested back in 2009)

    Friday, November 02, 2012 9:57 PM