SQL Server のデータベースは自動拡張設定を使用することで、データベースの割り当て領域が不足した際に、
自動的に拡張するように設定することができます。
SQL Server のソート等で使用される tempdb にもこの設定は適用することが可能です。
tempdb の領域が不足するとソート時などに以下のエラーが発生します。
|
メッセージ 1105、レベル 17、状態 2、行 1 |
# tempdb にはソートの一時結果が保存されますので、これはメモリ上でソートができる場合でも保存されますので、
メモリが足りていても tempdb が足りていないとソートに失敗します。
保険のために自動拡張を有効にして領域不足を解消するのは有効だと思います。
ただし、tempdb はサービスが再起動するタイミングで再作成されています。
自動拡張がされていても、サービスが再起動されると、明示的に設定をしていたサイズで再作成され、
自動拡張後のサイズはクリアされてしまいます。
[自動拡張前の tempdb のサイズ]
現在は 8.5MB 割り当てられています。
この状態で 600MB 程度のデータが入っているテーブルをソートしてみます。
[自動拡張後の tempdb のサイズ]
671MB まで拡張されました。
今の設定では 10% 単位で拡張がされるようになっています。
自動拡張のイベントを確認してみると、データファイルの自動拡張イベントが大量に出力されています。
今回の自動拡張イベントの処理時間の合計だけで、[34,939 ミリ秒] かかっています。
自動拡張の発生で 30 秒。処理時間としてはもったいないですね。
# 瞬時初期化ありの状態でこの秒数です。
今回はクエリエディタから実行しているのでタイムアウトしていませんが、プログラムから実行した場合は、
クエリタイムアウトの秒数に引っ掛かりエラーとなる可能性も。
この状態で、SQL Server のサービスを再起動してみます。
[再起動後の tempdb のサイズ]
再起動後は初期サイズの 8.5MB になってしまっています。
これは、ユーザーデータベースの場合は自動拡張が発生すると、初期サイズが変更されるのですが、tempdb に関しては
自動拡張が発生しても初期サイズが変更されないためです。
自動拡張後にデータベースのプロパティを確認してみると、600MB 超のサイズが初期設定にはなっておらず、
明示的に設定したサイズとなっています。
?
サーバーの定期メンテナンス等で SQL Server を再起動する前に、tempdb の現在のサイズを確認して、
初期サイズの見直しが必要かの判断は重要かな～と思います。
