SQL Server: 本番作業ガイドライン - インデックス作成

背景

DBのスキーマを変更する際や、データを大量に更新する際はブロッキングによるタイムアウトエラー多発などの障害が発生しやすいです。
組織によっては専門のチームがスキーマの変更をすべて請け負う体制になっている場合もあると思いますが、各開発者やSREが本番環境にリリースする場合もあるかと思います。

そのようなケースで事故なく本番環境へスキーマ変更等をリリースできるように、ガイドラインを整備したいと思い考えてみました。専門知識を持った人がリリースを請け負うのがDBA的な志向だとすると、誰でも安全にリリースできるような仕組みを整えるのはDBRE的な志向だといえます。

今回はインデックス作成についてまとめました。

必須のknowledge

ロック

リスクと問題発生時のサービス影響

リスク	問題発生時のサービス影響
(standard edition限定)インデックス作成中は該当テーブルへのあらゆる更新がブロックされる	運用中サービスの一部ページの読み込みがいつまでも終わらず、ユーザーにエラーが返る
データファイル、トランザクションログファイルの容量が枯渇し、更新処理が一切できなくなることがある	運用中サービス内の一部機能が使えなくなる

実施手順

１．インデックスを作成するDBのエディションを確認する

select @@version

Standard Edition / Enterprise Edition のいずれかが表示されます。（それ以外のエディションは今回対象外とします）

Standard Edition：オンラインでのインデックス作成ができないため、実施タイミングを検討します。
Enterprise Edition：オンラインでインデックス作成が可能なので、実施タイミングを気にする必要はありません。

２．インデックスを作成しても容量的に問題が無いかを確認

以下のクエリでデータファイルおよびログファイルの空き容量を確認します。

SELECT db_name() AS 'DB名'
    ,(case when type_desc = 'ROWS' then 'データファイル'
          when type_desc = 'LOG' then 'トランザクションログファイル'
          else type_desc end) as 'ファイル種別'
    ,cast(size * 8.0 / 1024 as int) AS '割り当て済みサイズ(MB)'
    ,cast(fileproperty(name, 'SpaceUsed') * 8.0 / 1024 as int) as '使用中のサイズ(MB)'
    ,cast((size * 8.0 / 1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024) as int) as '空きサイズ(MB)'
FROM sys.database_files

次に、インデックスのサイズを確認するクエリを実行し、既存インデックスのサイズを確認します。

declare @tablename varchar(100) = 'table_name' --テーブル名を指定
 
select top 100 object_name(sys.indexes.object_id) as table_name
  ,sys.indexes.name as index_name
  ,sys.dm_db_partition_stats.row_count as row_count
  ,cast(sys.dm_db_partition_stats.reserved_page_count * 8.0 / 1024 as numeric(10,1)) as size_mb
  ,type_desc
from sys.dm_db_partition_stats
left join sys.indexes on sys.dm_db_partition_stats.object_id = sys.indexes.object_id
  and sys.dm_db_partition_stats.index_id = sys.indexes.index_id
left join sys.dm_db_index_usage_stats on sys.dm_db_partition_stats.object_id = sys.dm_db_index_usage_stats.object_id
  and sys.dm_db_partition_stats.index_id = sys.dm_db_index_usage_stats.index_id
where object_name(sys.indexes.object_id) = @tablename
order by sys.dm_db_partition_stats.reserved_page_count desc

基本的にはクラスタ化インデックス(type_desc=CLUSTERED)のサイズより非クラスタ化インデックスのサイズが大きくなることはありません。したがって、
クラスタ化インデックスのサイズ << データファイルおよびログファイルの空き容量
という関係が確認できれば、容量的には問題ありません。

３．(Standard Editionのみ)インデックス作成タイミングの検討

Standard Editionの場合、インデックス作成中は該当テーブルへの更新がブロックされます。
例えば、tableAにインデックスを作成している間は、Insert/Update/DeleteがブロックされるためtableAを更新する全てのサービスがタイムアウトしエラーとなってしまいます。
一方で、対象テーブルのレコード数が少ない場合（目安：10万レコード以下）はインデックスの作成処理にそこまで時間がかからないと想定されます。ブロッキングが発生しても、タイムアウトが多発する前に作成完了できれば問題ないという考え方もできます。
このように、対象テーブルの性質によって作成するタイミングを検討してください。
場合によってはサービスメンテナンスを実施したり、該当テーブルに関連した処理だけメンテ期間を設けることも必要になってきます。

４．インデックス作成クエリの実行

■ Standard Edition
インデックス作成中はサーバーで現在実行中のクエリリストを定期的に確認し、ブロッキングの発生状況を常にチェックするようにします。

select TOP (1000)
     der.session_id
    ,der.blocking_session_id
    ,datediff(s, der.start_time, getdate()) as elapsed_time_sec
    ,db_name(der.database_id) as db_name
    ,des.host_name
    ,des.program_name
    ,der.status
    ,dest.text as command_text
    ,replace(replace(replace(substring(dest.text, 
    (der.statement_start_offset / 2) + 1, 
    ((case der.statement_end_offset
    when -1 then datalength(dest.text)
    else der.statement_end_offset
    end - der.statement_start_offset) / 2) + 1),char(13), ' '), char(10), ' '), char(9), ' ') as current_running_stmt
    ,wait_resource
    ,wait_type
    ,last_wait_type
    ,der.wait_time as wait_time_ms
    ,der.open_transaction_count
    ,der.command
    ,der.cpu_time
    ,(case der.transaction_isolation_level
      when 0 then 'unspecified'
      when 1 then 'readuncomitted'
      when 2 then 'readcommitted'
      when 3 then 'repeatable'
      when 4 then 'serializable'
      when 5 then 'snapshot'
    else cast(der.transaction_isolation_level as varchar) end) as transaction_isolation_level
    ,der.granted_query_memory * 8 as granted_query_memory_kb --キロバイト単位
from
    sys.dm_exec_requests der
join sys.dm_exec_sessions des on des.session_id = der.session_id
outer apply sys.dm_exec_sql_text(sql_handle) as dest
where
    des.is_user_process = 1
and datediff(s, der.start_time, getdate()) >= 1
order by
    datediff(s, der.start_time, getdate()) desc

■ Enterprise Edition
「WITH(ONLINE=ON)」オプションを付けて実行することで、他の更新クエリをブロックせずにインデックスを作成できます。

CREATE NONCLUSTERED INDEX [ix_t1_c1] ON [dbo].[t1]
(
    [c1] ASC
) WITH(ONLINE=ON)

一応、インデックス作成中はサーバーで現在実行中のクエリリストを定期的に確認し、ブロッキングの発生状況を常にチェックするようにします。

所要時間

該当テーブルのレコード数に依存します。レコード数が多いほど、所要時間も伸びる傾向にあります。

問題が発生したとき

クエリ実行を取り消しましょう。
明示的にトランザクションを開いている場合は必ずrollbackしてトランザクションを終了させます。

実施後の確認項目

• アプリケーションのエラーログでエラーが急に多発するようになっていないか確認
• サーバーで現在実行中のクエリリストを確認し、スロークエリが多発していないかを確認
  • インデックス作成で基本的にはクエリが高速化されますが、稀に低速なクエリが発生する可能性もあります。その場合は作成したインデックスを一度DROPして事象が解消するか確認します。
• インデックスの利用状況を確認するクエリで、該当のインデックスが意図した使われ方をしているか確認
  • 基本的には、Index SeekとIndex Scanの割合が10:0に近いほど良いです。ただし、バッチ処理など大量のレコードを読む処理はIndex Scanの方が望ましいこともあります。

注意事項

• トランザクションを明示的に開始（begin tran）した場合は、必ずcommitまたはrollbackさせます。ロックを獲得する時間を最小限に抑えましょう。
  • 基本的に、インデックス作成時は明示的なトランザクション開始は不要です。