SQLServer保守

SQLServerにおいて特にデータが増えるにつれてデータベース保守を意識して運用させることが重要になってきます。

◇クエリオプティマイザと統計情報
SQLServerはクエリオプティマイザという機能がSQLステートメントを実行するのにもっとも最適な方法（どのインデックスを使うかなど）を選択します。最適なインデックスを選択するには、テーブル内にどのようなデータがどれだけ入っているかを統計情報として格納しています。この統計情報が古いとパフォーマンスに影響がでることがあります。

・通常データベースを作成するとデータベースプロパティで自動更新ONになっています。（イメージ）

・手動で更新するには？
　→ UPDATE STATISTICS {テーブル名／ビュー名}　…　テーブル／ビュー個別の統計情報更新
　→ sp_updatestats　…　データベース内の全テーブルの統計情報更新

この統計情報はデータが更新されれば変化するので、自動あるいは手動で定期的に更新する必要があります。

◇インデックス保守
データが更新されるとページ分割が発生し、データのフラグメンテーションが発生します。
フラグメンテーションが発生すると、スキャン密度が低くなったりページの肥大化、インデックススキャン・再構築の低下によりパフォーマンスが低下してしまいます。

フラグメンテーション情報を調べるには？
　→　DBCC SHOWCONTIG([テーブル名])

【実行結果の見所】

①- スキャンされたページ数.........................: 20219
②- スキャンされたエクステント数...................: 3046
③- 切り替えられたエクステント数...................: 3224　　　　　　　　⇒ ②値に近い方が良い
④- エクステントごとの平均ページ数.................: 6.6
⑤- スキャン密度 [最善 :実際]......................: 78.39% [2528:3225]　⇒ すべてが連続的にリンクしている場合100に近づく。100未満は断片化発生
⑥- 論理スキャンフラグメンテーション .............: 0.93% 　　　　　　　⇒ 0値に近い方が良い
⑦- エクステントスキャンフラグメンテーション ....: 58.54%　　　　　　　⇒ 0値に近い方が良い(ｲﾝﾃﾞｯｸｽが複数ﾌｧｲﾙに渡す場合値が高くなる)
⑧- ページごとの平均空きバイト数...................: 172.4 　　　　　　　⇒ 低い方が良い
⑨- 平均ページ密度 (全体)..........................: 97.87%　　　　　　　⇒ 高い方が良い

フラグメンテーションを解消するには？
　→ インデックス再構築
　　　　　クラスタ化インデックスを削除し、FillFactor値を指定して再作成
　　　　　※FillFactior：0と100では同義の設定。デフォルト値はサーバープロパティ（イメージ）で変更可。

CREATE UNIQUE CLUSTERD INDEX インデックス名 ON テーブル名
(列名１,列名２,…)
WITH DROP_EXISTING,FILLFACTOR=80

DBCC DBREINDEX( ①, ② )
① … ( 'ﾃｰﾌﾞﾙ名' , 'ｲﾝﾃﾞｯｸｽ名' , FillFactor値 )
② … ( 'ﾃｰﾌﾞﾙ名' , '' , FillFactor値 )

　→ インデックスデフラグを実行して論理順序を並べ替えてスキャン精度向上
　　　　　論理スキャンフラグメンテーション15%以上、スキャンページ100ページ以上で実行した方が良い

DBCC INDEXDEFRAG( ①, ②, ③ )
① … { 'ﾃﾞｰﾀﾍﾞｰｽ名' | ﾃﾞｰﾀﾍﾞｰｽID | 0 } 「0」を指定すると現在のデータベースを指定
② … { 'ﾃｰﾌﾞﾙ名' | ﾃｰﾌﾞﾙID | 'ﾋﾞｭｰ名' | ﾋﾞｭｰID }
③ … { 'ｲﾝﾃﾞｯｸｽ名' | ｲﾝﾃﾞｯｸｽID }

ただ、このコマンドの不便なところはｲﾝﾃﾞｯｸｽを１つずつ指定しなければならない。
→論理ｽｷｬﾝﾌﾗｸﾞﾒﾝﾃｰｼｮﾝが？％以上のｲﾝﾃﾞｯｸｽだけをﾃﾞﾌﾗｸﾞするｻﾝﾌﾟﾙｽｸﾘﾌﾟﾄ

◇ストアドプロシージャと統計情報
ストアドプロシージャは一度コンパイルされるとコンパイル時に最適化された実行プランを永遠に使用するため
手動で統計情報を更新する必要があります。

【再コンパイル方法】
①作成時に指定
　　CREATE PROCEDURE WITH RECOMPILE を使用して作成したｽﾄｱﾄﾞﾌﾟﾛｼｰｼﾞｬはﾌﾟﾛｼｰｼﾞｬｷｬｯｼｭに残らず毎回ｺﾝﾊﾟｲﾙされる
②実行時に指定
　　EXECUTE WITH RECOMPILE を使用して実行すると、その実行時に再コンパイルする
③次回実行時に再コンパイル指定
　　sp_recompile オブジェクト名を使用すると次回実行時に再コンパイルされるよう設定する
④プロシージャキャッシュのクリア
　　DBCC FREEPROCCACHE を実行すると、プロシージャキャッシュをすべて削除

◇データベース保守管理
SQLServerには、［SQLServerAgent］－［ジョブ］による保守計画や、［データベース保守計画］－［保守計画］により「最適化」「圧縮」「整合性チェック」「バックアップ」ができるようになっています。
これらを使用した保守や、バッチの一部としてコマンド実行するなどデータベース保守としても考慮していく必要があります。