SQL Serverにてインデックスの再構築を行うストアドプロシージャ備忘録

SQL Serverにて、断片化したインデックスのみを再構築(Rebuild)や再構成(Reorganize)する必要があり、その時使ったストアドプロシージャの備忘録です。

再構築(Rebuild)

CREATE PROCEDURE [dbo].[usp_RebuildFragmentedIndexed]
  @threshold FLOAT = 30.0
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @base_sql NVARCHAR(max)
  DECLARE @edition NVARCHAR(max)

  SET @base_sql = 'ALTER INDEX @index_name On @table_name REBUILD @rebuild_index_option'

  SET @edition = CONVERT(NVARCHAR, SERVERPROPERTY('Edition'))
  
  SET @base_sql = REPLACE(@base_sql, '@rebuild_index_option', 
    CASE WHEN PATINDEX('%Enterprise%', @edition) > 0 OR PATINDEX('%Developer%', @edition) > 0
      THEN 'WITH (ONLINE=ON)' 
      ELSE '' END)

  DECLARE CUR CURSOR FOR
  SELECT
    CONCAT_WS('.', QUOTENAME(S.name), QUOTENAME(O.name)) AS TableName
    , QUOTENAME(I.name) AS IndexName
    --, PS.avg_fragmentation_in_percent
  FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS PS
    INNER JOIN sys.objects AS O ON PS.object_id = O.object_id
    INNER JOIN sys.schemas AS S ON O.schema_id = S.schema_id
    INNER JOIN sys.indexes AS I ON PS.object_id = I.object_id AND PS.index_id = I.index_id
  WHERE
    avg_fragmentation_in_percent > @threshold
    AND PS.index_id > 0;
  
  DECLARE @table_name SYSNAME
  DECLARE @index_name SYSNAME
  --DECLARE @avg_fragmentation_in_percent FLOAT
  DECLARE @sql_command NVARCHAR(max)

  OPEN CUR
  FETCH NEXT FROM CUR 
  INTO @table_name, @index_name
  --, @avg_fragmentation_in_percent

  WHILE @@FETCH_STATUS = 0 
  BEGIN 
    SET @sql_command = REPLACE(REPLACE(@base_sql, '@index_name', @index_name), '@table_name', @table_name) 
    
    --PRINT @sql_command
    EXECUTE sp_executesql @sql_command

    FETCH NEXT FROM CUR 
    INTO @table_name, @index_name    
  END

  CLOSE CUR 
  DEALLOCATE CUR
END

公式情報には、行ストアインデックスでの断片化を再構築する際の大まかな値として、avg_fragmentation_in_percent が30%より大きい場合を例示しています。

docs.microsoft.com

rebuild_index_optionですが、SQL Server 2014より ONLINE = ONが指定可能になっており、その際にはデフォルトで ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE )と解釈されます。
SQL Server 2017より RESUMABLE = ONが指定可能となっており、ONLINE = ONと同時に指定可能です。

docs.microsoft.com

再構成(Reorganize)

ALTER PROCEDURE [dbo].[usp_ReorganizeFragmentedIndexed]
  @threshold FLOAT = 5.0
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @base_sql NVARCHAR(max)

  SET @base_sql = 'ALTER INDEX @index_name ON @table_name REORGANIZE'

  DECLARE CUR CURSOR FOR
  SELECT
    CONCAT_WS('.', QUOTENAME(S.name), QUOTENAME(O.name)) AS TableName
    , QUOTENAME(I.name) AS IndexName
    --, PS.avg_fragmentation_in_percent
  FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS PS
    INNER JOIN sys.objects AS O ON PS.object_id = O.object_id
    INNER JOIN sys.schemas AS S ON O.schema_id = S.schema_id
    INNER JOIN sys.indexes AS I ON PS.object_id = I.object_id AND PS.index_id = I.index_id
  WHERE
    avg_fragmentation_in_percent >= @threshold
    AND PS.index_id > 0;
  
  DECLARE @table_name SYSNAME
  DECLARE @index_name SYSNAME
  --DECLARE @avg_fragmentation_in_percent FLOAT
  DECLARE @sql_command NVARCHAR(max)

  OPEN CUR
  FETCH NEXT FROM CUR 
  INTO @table_name, @index_name
  --, @avg_fragmentation_in_percent

  WHILE @@FETCH_STATUS = 0 
  BEGIN 
    SET @sql_command = REPLACE(REPLACE(@base_sql, '@index_name', @index_name), '@table_name', @table_name) 
    
    --PRINT @sql_command
    EXECUTE sp_executesql @sql_command

    FETCH NEXT FROM CUR 
    INTO @table_name, @index_name    
  END

  CLOSE CUR 
  DEALLOCATE CUR
END

参考にした情報：

sql55.com

fukusoft.blog.jp