Posted By

g8rpal on 09/30/10


Tagged


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Tyster


Index defrag script (21005/2008)


 / Published in: SQL
 

URL: http://www.sqlservercentral.com/scripts/Reindex/70985/

It can either rebuild/reorg all databases or just a selected database. Options are there to prevent small indexes from being redone (there is not much point in rebuilding an idex with only a few pages). You can also prevent it from reindexing large indexes, one that you may prefer to do manually. If the edition of SQL supports it, it will do online rebuilds. You can also prevent index from being rebuilt that are over a certain threshold, either in pages or percentage (you may want to do manually large indexes manually).

You can set your own threshold for reorg versus rebuild (defaults to 5/30). There is also an option to change the recovery model to simple for the duration of the action.

The only time this may fail is if your server is case sensitive, I did not build in the checks to allow for that.

If you want to rebuild all indexes with a new fillfactor, just set the @ReOrgThreshold to 1.01 and @ReBuildThreshold to 1.02...or do it manually.

Not much else to add, the SP has a good bit of documentation.

  1. /***********************************************************************************************************************
  2.         Version 1.0
  3.         19 Aug 2010
  4.  
  5.         Gregory Ferdinanddsen
  6.         [email protected]
  7.  
  8.  
  9.             This SP will rebuild/reorg indexes.
  10.  
  11.         Parameters:
  12.             @DB = Either 'All' or the name of one DB. If 'All' all databases on the server are examined; otherwise the name of a single DB.
  13.             @Stats = Statistical Sampling Method (Limited, Sampled, or Detailed) for determining what index will be impacted.
  14.                 --LIMITED - It is the fastest mode and scans the smallest number of pages.
  15.                         For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned
  16.                 --SAMPLED - It returns statistics based on a 1 percent sample of all the pages in the index or heap.
  17.                         If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.
  18.                 --DETAILED - It scans all pages and returns all statistics.
  19.             @MinPageCount = Since index with few pages usually don't defrag (and a table scan is preferred), ignores small indexes
  20.             @MaxPageCount = Maximum number of index pages to be considered. This can preclude very large indexes
  21.             @Fill Factor = Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page
  22.                     during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0.
  23.             @PAD_Index = The percentage of free space that is specified by FILLFACTOR is applied to the intermediate-level pages of the index.
  24.                 If FILLFACTOR is not specified at the same time PAD_INDEX is set to ON, the fill factor value stored in sys.indexes is used.
  25.             @SortInTempDB = The intermediate sort results that are used to build the index are stored in tempdb.
  26.                 If tempdb is on a different set of disks than the user database, this may reduce the time needed to create an index.
  27.                 However, this increases the amount of disk space that is used during the index build.
  28.             @Online = Online rebuild, for editions that support online rebuild (for editions that do not support online rebuild, this is ignored)
  29.             @ReBuildTheshold = The threshold for deciding to rebuild v reorg (MSFT recomend's 30)
  30.             @ReOrgThreshold = The threshold for deciding to rebuild v reorg (MSFT recomend's 5)
  31.             @MaxFrag = The maximum amount of fragmentation to defrag (i.e. you don't want to defrag an index over 80%)
  32.             @ChangeRecoveryModel = Set's the DB's in simple recovery mode prior to starting, reverts back to original mode on completion.
  33.  
  34.             NB:
  35.             @Fill_Factor, @PAD_Index will only be applied to index that are rebuilt (Fragmentation >= @ReBuildTheshold)
  36.  
  37.  
  38.             Alter Index -- http://technet.microsoft.com/en-us/library/ms188388.aspx
  39.             sys.dm_db_index_physical_stats -- http://msdn.microsoft.com/en-us/library/ms188917.aspx
  40.  
  41.         examples:
  42.             exec dbadmin..sp_Defrag_Indexes, @FillFactor = 75, @PAD_Index = 'true', @Stats = 'Detailed'
  43.  
  44.             exec dbadmin..sp_Defrag_Indexes
  45.                 @DB = 'changepoint',
  46.                 @FillFactor = 65,
  47.                 @PAD_Index = 'true',
  48.                 @Stats = 'Detailed',
  49.                 @ChangeRecoveryModel = 'true',
  50.                 @minpagecount = 150
  51. ***********************************************************************************************************************/
  52.  
  53. ALTER procedure [dbo].[sp_Defrag_Indexes]
  54.     (
  55.     @DB varchar(256) = 'all',
  56.     @Stats varchar(8) = 'sampled',
  57.     @MinPageCount int = 20,
  58.     @MaxPageCount float = 1000000000000000, --A very large default number
  59.     @FillFactor int = NULL,
  60.     @PAD_Index varchar(8) = 'false',
  61.     @SortInTempDB varchar(8) = 'true',
  62.     @OnlineReq varchar(8) = 'true',
  63.     @ReBuildTheshold real = 30.0,
  64.     @ReOrgThreshold real = 5.0,
  65.     @MaxFrag real = 100.0,
  66.     @ChangeRecoveryModel varchar(8) = 'false'
  67.     )
  68.  
  69.     AS
  70.  
  71.     declare @SQLCmd AS varchar (8000)
  72.     declare @SQLCmdBk AS varchar(4096)
  73.     declare @SQLCmdWith AS varchar(4096)
  74.     declare @SQLCmdFill varchar(512)
  75.     declare @SQLCmdOnline varchar(512)
  76.     declare @SQLCmdPad varchar(512)
  77.     declare @SQLCmdSort varchar(512)
  78.     declare @SQLCmdRecovery varchar(512)
  79.     declare @exit varchar(8)
  80.     declare @ErrorTxt AS varchar(128)
  81.     declare @SQLEdition AS varchar(64)
  82.     declare @Online AS varchar(8)
  83.     declare @DBName AS varchar(256)
  84.     declare @ObjectID int
  85.     declare @IndexID int
  86.     declare @PartitionNum AS bigint
  87.     declare @Frag AS float
  88.     declare @PageCount AS bigint
  89.     declare @PartitionCount AS bigint
  90.     declare @ParititionNum AS bigint
  91.     declare @IndexName AS varchar(128)
  92.     declare @SchemaName AS varchar(128)
  93.     declare @ObjectName AS varchar(128)
  94.     declare @ParmDef nvarchar(512)
  95.     declare @SQLCmdID AS nvarchar(1024)
  96.     declare @RecoveryModel AS varchar(16)
  97.  
  98.     --Verify that proper parameters were passed to SP
  99.     IF @Stats NOT IN ('limited', 'sampled', 'detailed')
  100.         begin
  101.             RaisError ('@Stats must be "limited", "sampled", or "detailed"', 16, 1)
  102.             RETURN
  103.         end
  104.  
  105.     IF @PAD_Index NOT IN ('true', 'false')
  106.         begin
  107.             RaisError ('@PAD_Index must be "true" or "false"', 16, 1)
  108.             RETURN
  109.         end
  110.  
  111.     IF @SortInTempDB NOT IN ('true', 'false')
  112.         begin
  113.             RaisError ('@SortInTempDB must be "true" or "false"', 16, 1)
  114.             RETURN
  115.         end
  116.  
  117.     IF @OnlineReq NOT IN ('true', 'false')
  118.         begin
  119.             RaisError ('@OnlineReq must be "true" or "false"', 16, 1)
  120.             RETURN
  121.         end
  122.  
  123.     IF @FillFactor NOT BETWEEN 0 AND 100
  124.         begin
  125.             RaisError ('@FillFactor must be between 0 and 100', 16, 1)
  126.             RETURN
  127.         end
  128.  
  129.     IF @ReBuildTheshold NOT BETWEEN 1 AND 100
  130.         begin
  131.             RaisError ('@ReBuildTheshold must be between 1 and 100', 16, 1)
  132.             RETURN
  133.         end
  134.  
  135.     IF @ReOrgThreshold NOT BETWEEN 1 AND 100
  136.         begin
  137.             RaisError ('@ReOrgThreshold must be between 1 and 100', 16, 1)
  138.             RETURN
  139.         end
  140.  
  141.     --There would be nothing returned if MaxFrag was less than the reorg threshold.
  142.     IF @MaxFrag NOT BETWEEN @ReOrgThreshold AND 100
  143.         begin
  144.             RaisError ('@MaxFrag must be between the @ReOrgThreshold value (default of 5) and 100', 16, 1)
  145.             RETURN
  146.         end
  147.  
  148.     IF @MinPageCount < 0
  149.         begin
  150.             RaisError ('@MinPageCount must be positive', 16, 1)
  151.             RETURN
  152.         end
  153.  
  154.     IF @MaxPageCount < 10
  155.         begin
  156.             RaisError ('@MaxPageCount must be greater than 10', 16, 1)
  157.             RETURN
  158.         end
  159.  
  160.     IF @ChangeRecoveryModel NOT IN ('true', 'false')
  161.         begin
  162.             RaisError ('@ChangeRecoveryModel must be "true" or "false"', 16, 1)
  163.             RETURN
  164.         end
  165.  
  166.     IF @MinPageCount > @MaxPageCount
  167.         begin
  168.             RaisError ('@MinPageCount cannot be greater than @MaxPageCount', 16, 1)
  169.             RETURN
  170.         end
  171.  
  172.     IF @DB <> 'All'
  173.         begin
  174.             IF NOT EXISTS (SELECT name FROM sys.DATABASES WHERE name = @DB)
  175.                 begin
  176.                     SET @ErrorTxt = 'The supplied database (' + @DB + ') does not exist.'
  177.                     RaisError (@ErrorTxt, 16, 1)
  178.                     RETURN
  179.                 end
  180.         end
  181.  
  182.     --You can't have rebuild be at a lower level than reorg
  183.     IF @ReBuildTheshold < @ReOrgThreshold SET @ReOrgThreshold = @ReBuildTheshold - 0.01
  184.  
  185.     --Determine SQL Edition (for online rebuild -- Enterprise and Developer support online rebuild)
  186.     SET @SQLEdition = cast(ServerProperty('Edition') AS varchar)
  187.     SET @SQLEdition =
  188.         case
  189.             when @SQLEdition = 'Enterprise Edition' then 'Enterprise'
  190.             when @SQLEdition = 'Standard Edition' then 'Standard'
  191.             when @SQLEdition = 'Developer Edition' then 'Developer'
  192.         end
  193.     IF @SQLEdition = 'Enterprise' OR @SQLEdition = 'Developer'
  194.         begin
  195.             SET @Online = 'true'
  196.         end
  197.     else SET @Online = 'false'
  198.  
  199.     --If only one database, then go to the innser cursor (and exit that cursor before the fetch next command)
  200.     SET @Exit = 'false'
  201.     IF @DB <> 'All'
  202.         begin
  203.             SET @Exit = 'true'
  204.             SET @DBName = @DB
  205.             goto ExecuteForEachDatabase
  206.         end
  207.  
  208.     --Outer Cursor for DBName
  209.     declare DatabaseNames cursor
  210.         FOR SELECT name FROM sys.DATABASES
  211.  
  212.         open DatabaseNames
  213.         fetch next FROM DatabaseNames INTO @DBName
  214.  
  215.         while @@fetch_status <> -1
  216.             begin
  217. ExecuteForEachDatabase:
  218.                 --Delete the Temp Table
  219.                 IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = '#Fragmentation' AND type IN('U'))
  220.                     begin
  221.                         DROP TABLE #Fragmentation
  222.                     end
  223.  
  224.                 --Determine Recovery Model
  225.                 SET @RecoveryModel = cast(DatabasePropertyEx(@DBName, 'Recovery') AS varchar(16))
  226.                 IF @RecoveryModel IN ('FULL', 'BULK_LOGGED') AND @ChangeRecoveryModel = 'true'
  227.                     begin
  228.                         SET @SQLCmdRecovery = 'alter database ' + @DBName + ' set recovery simple with no_wait'
  229.                         print @DBName + ' recovery model set to simple.'
  230.                         exec (@SQLCmdRecovery)
  231.                     end
  232.  
  233.                 --Index_ID of 0 is a heap index, no need to defrag
  234.                 SELECT object_id AS ObjectID, index_id AS IndexID, partition_number AS PartitionNum, avg_fragmentation_in_percent AS Frag
  235.                     INTO #Fragmentation
  236.                     FROM sys.dm_db_index_physical_stats (DB_ID(@DBName), NULL, NULL , NULL, @Stats)
  237.                     WHERE avg_fragmentation_in_percent >= @ReOrgThreshold AND avg_fragmentation_in_percent < = @MaxFrag
  238.                         AND index_id > 0
  239.                         AND Page_Count >= @MinPageCount AND Page_Count <= @MaxPageCount
  240.  
  241.                 --Inner Cursor (objects)
  242.                 declare CurPartitions cursor
  243.                     FOR SELECT * FROM #Fragmentation
  244.  
  245.                     open CurPartitions
  246.                     fetch next FROM CurPartitions INTO @ObjectID, @IndexID, @ParititionNum, @Frag
  247.  
  248.                     while @@fetch_status <> -1
  249.                         begin
  250.                             SET @SQLCmdID = 'select @ObjectName = quotename(obj.name), @SchemaName = quotename(sch.name) from ' + @DBName +
  251.                                 '.sys.objects as obj join ' + @DBName + '.sys.schemas as sch on sch.schema_id = obj.schema_id where obj.object_id = @ObjectID'
  252.                             --select @ObjectName = quotename(obj.name), @SchemaName = quotename(sch.name)
  253.                             --    from sys.objects as obj
  254.                             --    join sys.schemas as sch on sch.schema_id = obj.schema_id
  255.                             --    where obj.object_id = @ObjectID
  256.                             SET @ParmDef = N'@ObjectID int, @ObjectName sysname output, @SchemaName sysname output'
  257.                             exec sp_executesql @SQLCmdID, @ParmDef, @ObjectID= @ObjectID, @ObjectName = @ObjectName output, @SchemaName = @SchemaName output
  258.                                 
  259.  
  260.                             --select @IndexName = quotename(name)
  261.                             --    from sys.indexes
  262.                             --    where object_id = @ObjectID and index_id = @IndexID
  263.                             SET @SQLCmdID = 'select @IndexName = quotename(name) from ' + @DBName + '.sys.indexes where object_id = @ObjectID and index_id = @IndexID'
  264.                             SET @ParmDef = N'@ObjectId int, @IndexId int, @IndexName sysname output'
  265.                             exec sp_executesql @SQLCmdID, @ParmDef, @ObjectId = @ObjectId, @IndexId = @IndexId, @IndexName = @IndexName output
  266.  
  267.                             --select @PartitionCount = count (*)
  268.                             --    from sys.partitions
  269.                             --    where object_id = @ObjectID and index_id = @IndexID
  270.                             SET @SQLCmdID = 'select @PartitionCount = count (*) from ' + @DBName + '.sys.partitions where object_id = @ObjectID and index_id = @IndexID'
  271.                             SET @ParmDef = N'@ObjectId int, @IndexId int, @PartitionCount int output'
  272.                             exec sp_executesql @SQLCmdID, @ParmDef, @ObjectId = @ObjectId, @IndexId = @IndexId, @PartitionCount = @PartitionCount output
  273.  
  274.                             --ReOrg
  275.                             SET @SQLCmdBk = NULL
  276.                             IF @frag < @ReBuildTheshold
  277.                                 begin
  278.                                     SET @SQLCmdBk = 'alter index ' + @IndexName + ' on [' + @DBName + '].' + @SchemaName + '.' + @ObjectName + ' reorganize'
  279.                                 end
  280.                             IF @frag >= @ReBuildTheshold
  281.                                 begin
  282.                                     SET @SQLCmdBk = 'alter index ' + @IndexName + ' on [' + @DBName + '].' + @SchemaName + '.' + @ObjectName + ' rebuild'
  283.                                 end
  284.  
  285.                             --set options
  286.                             IF @FillFactor IS NOT NULL SET @SQLCmdFill = 'fillfactor = ' + cast(@FillFactor AS varchar(3))+ ', '
  287.                             IF @Online = 'true' AND @OnlineReq = 'true' SET @SQLCmdOnline = 'online = on, '
  288.                             IF @PAD_Index = 'true' SET @SQLCmdPad = 'PAD_Index = on, '
  289.                             IF @SortInTempDB = 'true' SET @SQLCmdSort = 'Sort_in_TempDB = on, '
  290.  
  291.                             IF @PartitionCount > 1 SET @SQLCmdBk = @SQLCmdBk + ' partition = ' + cast(@partitionnum AS nvarchar(10))
  292.  
  293.                             SET @SQLCmdWith = ' with ('
  294.  
  295.                             --With options only apply to rebuilds, not to re-org
  296.                             IF @frag >= @ReBuildTheshold
  297.                                 begin
  298.                                     IF @SQLCmdFill IS NOT NULL SET @SQLCmdWith = @SQLCmdWith + @SQLCmdFill
  299.                                     IF @SQLCmdOnline IS NOT NULL SET @SQLCmdWith = @SQLCmdWith + @SQLCmdOnline
  300.                                     IF @SQLCmdPad IS NOT NULL SET @SQLCmdWith = @SQLCmdWith + @SQLCmdPad
  301.                                     IF @SQLCmdSort IS NOT NULL SET @SQLCmdWith = @SQLCmdWith + @SQLCmdSort
  302.                                 end
  303.  
  304.                             IF @SQLCmdWith <> ' with (' SET @SQLCmdWith = LEFT(@SQLCmdWith, len(@SQLCmdWith) - 1) + ')'
  305.                             IF @SQLCmdWith <> ' with (' SET @SQLCmd = @SQLCmdBk + @SQLCmdWith
  306.                             else SET @SQLCmd = @SQLCmdBk
  307.  
  308.                             --Print and execute
  309.                             exec (@SQLCmd)
  310.                             print @SQLCmd
  311.  
  312.                             fetch next FROM CurPartitions INTO @ObjectID, @IndexID, @ParititionNum, @Frag
  313.                         end --CurPartitions
  314.                     close CurPartitions
  315.                     deallocate CurPartitions
  316.                     DROP TABLE #Fragmentation
  317.  
  318.                     --If DB was in Full or Bulk_Logged and tlogging was disabled, then re-enable
  319.                     IF @RecoveryModel IN ('FULL', 'BULK_LOGGED') AND @ChangeRecoveryModel = 'true'
  320.                         begin
  321.                             SET @SQLCmdRecovery = 'alter database ' + @DBName + ' set recovery ' + @RecoveryModel + ' with no_wait'
  322.                             print @DBName + ' recovery model set to ' + @RecoveryModel + ' recovery model.'
  323.                             exec (@SQLCmdRecovery)
  324.                         end
  325.                     IF @Exit = 'true' RETURN
  326.  
  327.                 fetch next FROM DatabaseNames INTO @DBName
  328.             end --DatabaseNames
  329.     close DatabaseNames
  330.     deallocate DatabaseNames

Report this snippet  

You need to login to post a comment.