SQL Server / SQL Database の機能検証を実施する際に意識しておきたいポイント

SQL Server / SQL Database のような SQL Server ベースのデータベースエンジンで機能検証を実施する際に、どのような内容を確認したほうが良いかについて個人的な確認ポイントを。

既知の情報の確認

• SQL Server トラブルシューティング
• SQL Server データベース エンジンと Azure SQL Database のパフォーマンス センター

ハードウェアリソースへの影響

• 各リソースの使用状況への影響
  • CPU
  • メモリ
  • ディスク
  • ネットワーク

実行環境による動作の違い

• SQL Server
• SQL Database
• Azure SQL Managed Instance
• Synapse Analytics (Dedicated SQL Pool)
• Azure Arc Enabled SQL Managed Instance
• Azure SQL Edge
• ホストインストールタイプ / コンテナーベース
  • Windows / Linux

現状、SQL Server 2022 が最新のデータベースエンジンとなるため、2022 で動作が確認できる内容は他の実行基盤でも類似の動作となる可能性が高い
Windows と Linux はデータベースエンジンで使用しているファイルは同一だがそれ以外の機能 / コマンド (例: bcp / sqlcmd) については、各 OS 向けに実装されたツールとなっているため、OS の違いによる影響が発生する可能性が高い

バージョンアップのよる機能向上

• CU の適用 / メジャーバージョンアップにより、該当機能の機能向上が実行されていないか
  • バージョンアップにより機能向上している部分は以前のバージョンでは機能が不足 / 特定のユースケースでネックになっていた箇所となる

プロセス

• 内部プロセス
  • sqlservr.exe 内で実行されるか
• 外部プロセス
  • sqlserr.exe 外で実行されるか
    • 外部プロセスが起動する
      • 例: PolyBase / レプリケーション
    • SQL CLR としてロードされているか
      • 例: Azure SQL Edge は　SQL CLR が使用できない -> CLR ベースで実装されている関数 (FORMAT 等) が実行できないというような影響の確認にもつなげられる

設定の有効化

• 参照整合性制約が設定されている場合の影響
• SCHEMA BINDING が設定されている場合の影響
• 特定の SET 句のオプションが有効化されていないと使用できないか？
  • 例: インデックス付きビュー
  • 機能を有効化した対象を参照しているオブジェクト (例: インデックス付きビューを参照するストアド) でも特定の SET が適用されていないと問題になるケースも意識

実行プラン

• 機能を有効化することで、実行プランに追加の操作が発生しないか
  • 例: 変更の追跡によるサイドテーブルへのデータ挿入 / インデックス付きビューにより実体化されたビューへのデータ投入
• SELECT / INSERT / UPDATE / DELETE / TRUNCATE への影響
• 並列プランを阻害する要因とならないか
  • 例: ユーザー定義関数
• 新機能 / 互換性レベルによる実行プランの変化
  • スカラー UDF のインライン化
  • カーディナリティ推定 (SQL Server)
• 基数推定への影響
  • 固定的な基数推定になっている箇所への影響 (例: 一時テーブル系)
  • 複数述語による検索時の基数推定への影響

同時実行性

• ラッチ

  • 取得されるラッチ

• ロック

  • SCH-M が取得されないか
  • ロックが取得されている時間
    • 機能の利用時だけでなく、機能の有効化 / 無効化というようなポイントでの作業についても考慮
  • 長時間実行されている SELECT が存在していても機能の利用に影響はないか
  • オンライン処理が提供されているか
    • オンライン処理の場合、最終フェーズでは SCH-M が取得されるため、瞬間的な同時実行性の低下は避けられない
    • 非同期処理が提供されているか (例: 非同期統計情報の更新)
    • 低優先ロック (LOW_PRIORITY) による処理が提供されているか

• 使用されるスレッド

  • シングルスレッド / マルチスレッドのどちらで動作するか
    • シングルスレッドで実行される場合は、リソースに余裕があっても単一処理性能当たりの限界に達するケースがある
      • システム系の処理だと、バージョンによってシングルスレッドでしか動作しないものもある
      • 処理単位でマルチスレッド化ケースと、DB 単位にスレッドを起動してマルチスレッド化されるケースがある
    • マルチスレッドで実行される場合、何スレッド使用されているか (該当の処理によって CPU リソースを限界まで使用しないか)
  • コールスタック
    • 新しい待機事象に遭遇した場合は、どのような事象により該当の待機が発生するのかをコールスタックから類推する

• キャッシュのフラッシュ / チェックポイント

  • 機能を実行した場合に、データキャッシュのフラッシュやチェックポイントが発生しないか
    • SHRINK を実行した場合のメモリからデータファイルへのフラッシュの発生
  • チェックポイント実行時の追加のオーバーヘッドにつながらないか
    • 例: 変更の追跡が有効になっていると、チェックポイント実行時にトランザクション管理用のサイドテーブルへのフラッシュが発生する

トランザクション

• トランザクション

  • 暗黙なトランザクションが実行されていないか
    • システムストアドプロシージャを利用する機能だと、ストアド内で新しいトランザクションが実行されているケースがある
    • 新しいトランザクションが開始されている場合、エラー時にトランザクションがロールバックされるか (XACT_ABORT の明示的な指定が必要か)
  • Longest Transaction への影響
    • 機能の利用が Longest Transaction になり、tempdb / トランザクションログの解放に影響を与えないか
  • トランザクションミックス (SELECT / 更新系) になっている場合の影響

• 分散トランザクション (DTC) を使用した場合の影響

  • 例: DBM / 特定バージョン以前の AlwaysOn では DB を跨いだトランザクションの In-Doubt のサポートが含まれていない

• トランザクション分離レベル

  • SQL Server / Managed Instance: Read Committed
  • SQL Database: Read Committed Snapshot Isolation (RCSI)
  • Always On Read Only Replica: Snapshot ISOLATION

テーブルの構成

• ヒープ / B-Tree / 列ストアインデックスのテーブル構造で違いが出ないか
  • ヒープについては、ヒープ固有の問題が発生するケースがある
    • 例: Bulk Insert による未使用領域の増加 / 特定条件下の DELETE による未使用領域の増加
• 過度なパーティショニング
  • 数 10 のパーティションでは影響は見れないが、数 100 のパーティションになった場合に影響が出ないか
• データ型
  • データ型による影響を受けないな
    • 例: datetime2 の場合、精度を既定のものから変更するとパーティション除去が想定して行われないケースがある

データベース

• データファイル
  • ADR によるデータファイルへの影響
    • tempdb のバージョニング情報 / ADR の PVS への影響
  • クリーンアップされるタイミング
    • 履歴管理されているデータ: 例: 変更の追跡 / CDC / システムバージョニング
  • パーティショニングすることで効率的なアクセスができるか
  • 列ストアインデックスと組み合わせることができるか
• トランザクションログファイル
  • トランザクションログへの書き込みレコード数
  • 取得されるロック
  • どのシステムテーブルが操作されているか
    • 操作内容によっては、DAC / シングルユーザーモードで確認
  • 履歴系の機能の場合、トランザクションログファイルを読み取るアーキテクチャになっているか？
    • 例:
      • 変更の追跡 -> トリガーに近い動作
      • レプリケーション / CDC / AlwaysOn AG / Synapse Link: トランザクションログから読み取る -> トランザクションログの切り捨てに影響が出る可能性も考慮
• 互換性レベル
  • 上位の互換性レベルでのみ適用される改善はないか
• データベースレベルの構成
  • データベースレベルの構成で制御できるか
• トレースフラグ
  • 導入された機能向けのトレースフラグが提供されているか

tempdb

• tempdb の使用状況
• tempdb がクリーンアップされるタイミング

情報の取得

• 新機能向けの情報が追加されていないか (以前のバージョンとの比較)
  • システムビュー / DMV
  • 待機事象
  • 拡張イベント