SQL Server TempDBチュートリアル

投稿者：Eric Blinn|更新日：2020-07-17|コメント（2）|関連：その他>システムデータベース

問題

SQL Serverには、デフォルトで4つのシステムデー それらはマスター、モデル、msdb、およびTempDBです。 私はシステムデータベース”TempDB”が何であり、それが何のために使われているのかを知る必要があります。 これらの以前のチュートリアルは、非常に詳細にマスター、モデルとmsdbをカバーしています。

ソリューション

このヒントでは、TempDBデータベースについて説明します。 TempDBは、SQL Server内の多くの関数がありますが、明示的に呼び出されることはめったにありません。 それは、多くの場合、ほとんどのSQL Serverインスタンス上で最も忙しいデータベースではないにしても、最も忙しいの一つであることを非常に多くの機能 これらの機能の多くが何であるかを学ぶために読んでください！

SQL Server TempDBの概要

SQL Server TempDBの目的は何ですか?

TempDBの機能の一つは、オペレーティングシステムレベルのページやスワップファイルのようなものを動作させることです。 SQL Serverの操作が大きすぎてメモリ内で完了できない場合、またはクエリの初期メモリ付与が小さすぎる場合は、操作をTempDBのディスクに移動できます。

TempDBの別の機能は、一時テーブルを格納することです。 ポンドまたはハッシュ接頭辞（＃）またはdoublepound/ハッシュ接頭辞（＃＃）を使用してt-SQLで一時テーブルを作成した人は、TempDBにオブジェクトを作成しました。

CREATE TABLE #MSSQLTips (RowID int);

atriggerが実行されると、挿入された仮想テーブルと削除された仮想テーブルはTempDBに格納されます。

RCSI(COMMITTED SNAPSHOT ISOLATION)を使用するデータベースには、tempdbにバージョン管理情報が格納されます。

TempDBの上記の各機能は、実際に名前でtempdbを言及することなく完了しているため、TempDBの驚くべき使用法になる可能性があります。

TempDBは、いくつかの方法で明示的に呼び出すこともできます。 テーブルは、createステートメントでデータベースを参照することによってTempDBで生成できます。 このコードは通常のDDL操作とまったく似ていますが、TempDBで実行すると、テーブルは定義上一時テーブルになります。

CREATE TABLE TempDB.dbo.MSSQLTips(RowID int);

であることを示していますTempDBは、SQL Serverインスタンスの起動ごとに再生成されます。 前のセッション中にTempDBで作成された可能性のあるanyobjectは、サービスの再起動時には実行されません。 TempDBは、一般的に空またはほぼ空になるthemodelデータベースから初期オブジェクトリストを取得します。

インデックスメンテナンスは、ユーザーデータベースの空き領域を使用してソートを試みるのではなく、TempDBでソートを行うように要求することができます。

ALTER INDEX ON . REBUILD WITH (SORT_IN_TEMPDB = ON);

TempDBデータベースなしでSQL Serverを実行できますか？

Sql Serverは、TempDBなしでは実行できません(非常に極端な回復シナリオと非常に短い期間を除きます)。

TempDBデータベースのバージョンは、SQL Serverのバージョン、エディション、およびパッチレベルに固有ですか。

TempDBはほとんど空白のデータベースであり、SQL Serverのエディションやパッチレベルについて特にユニークなものはありません。

近年、バージョンレベルでTempDBに意味のある変更は2つしかありませんでした。 1つ目は、SQL Server2016以降では、トレースフラグT1117とT1118を有効にすることがベストプラクティスと見なされなくなるようにTempDBwasの動作が変更されたことです。 2つ目は、SQL Server2019以降、TempDBで使用されているシステムオブジェクトの一部がインメモリテーブルにアップグレードされ、競合を減らし、全体的なサーバーパフォーマンスを向上させたことです。

TempDBデータベースを削除または名前を変更できますか？

TempDBは、削除、デタッチ、オフラインにする、または名前を変更することはできません。 これらの操作のいずれかを試みると、エラーが返されます。 これは重要なシステムデータベースであるため、これを試みるべきではない理由はありません。

SQL Server TempDBデータベースの場所

TempDBデータベースをサポートする物理ファイルと名前は何ですか。

デフォルトの論理ファイル名は、dataの場合はtempdev、logの場合はtemplogです。それらはディスク上でtempdbとして見つけることができます。mdfおよびtemplog。ldfそれぞれ。 TempDBcommonlyには多くのデータファイルがあります。

TempDBデータファイルはいくつあるべきですか？

インスタンス上のスレッド/コア/vCPUごとに1つのTempDBデータファイルがあり、最大8です。

ディスク上とSSMS内のTempDBデータベースはどこにありますか？

ファイルはsysを照会することで見つけることができます。sysfiles dmvまたはdatabasepropertiesウィンドウのファイルペイン。

SELECT * FROM TempDB.sys.sysfiles;

TempDBデータベースファイルが存在する場所に違いがありますか（C：\、SANなどにある必要がありますか）。)?

ベストプラクティスでは、tempdbデータファイルは、アクティビティレベルのために最速のstorageavailableにする必要があります。 いくつかのインスタンスでは、これはユーザーデータベースのデータファイルでstoringthemを意味することができますが、利用可能な場合は、別のディスク上の別のボリ

TempDBログファイルには異なる意見がありますが、この作成者はこのファイルを他のトランザクションログファイルと一緒に置くことを好みます。

TempDBデータベースを移動できますか？

TempDBデータベースを移動できます。 TempDBを移動する方法は簡単です実装する。 他のシステムデータベースが移動されたときのようにファイルを移動する必要はありません。 SQLServerは、サービス開始時に新しい名前および/または新しい場所にファイルを作成します。 ただ戻って、取り残されるnowunused TempDBファイルを削除することを忘れないでください。

ALTER DATABASE MODIFY FILE ( NAME = N'tempdev', FILENAME = N'G:\DATA\tmp.mdf');GOALTER DATABASE MODIFY FILE ( NAME = N'templog', FILENAME = N'H:\LOG\tmplog.ldf');GO

SQL Server TempDBデータベースのサイズと拡張

TempDBデータベースに対して、データベースとトランザクションログの拡張設定をどのように構成する必要がありますか。

自動拡張の設定は、パーセンテージではなく、特定のMBの数に設定する必要があります。すべてのデータファイルには、拡張用に設定されたMb数が同じである必要があります。

データベースのファイルのサイズを正しく設定することは、一般的にはベストプラクティスですが、TempDBの場合よりもそうです。 インスタンスを監視し、通常の操作中にファイルが増加しているかどうかを判断します。その場合は、サービスが開始されるたびにSQL Serverがファイルを一貫して拡張しないように、開始サイズを変更します。

TempDBデータベースの典型的なサイズは何ですか？

この質問には良い答えはありません。 あるインスタンスに典型的なものは、別のインスタンスに典型的ではないかもしれません。 この著者は、TempDBが基本的に使用されていないインスタンスを見てきました任意の時点で使用されているスペースの数MBのみを平均化します。 それでも、othersareは常に照会され、特にインデックスのメンテナンスウィンドウ中にTBで測定されたintospaceでさえ、何百GBものスペースを使用しています。

新しいインスタンスでTempDB専用のスペースを決定するとき、実行できることはほとんどありません。 新しいインスタンスは、古いexistinginstanceを置き換えますか？ その場合は、perfmonトレースまたはスケジュールされたDMVキャプチャを使用して、数日間にわたってTempDBで使用されている領域を決定します。 そのキャプチャにメンテナンスウィンドウを含めるようにしてください。 追加のウィグルルームを使用してトレース中にキャプチャされた最大の使用スペースを考慮するために、新しいインスタンスに十分なスペースを割 次に、そのスペース割り当てに収まるようにファイルを適切なサイズにします。

以下の2つのクエリが役立ちます。 最初のファイルは、ファイル内で使用されるスペースと一緒にデータとログファイルのサイズのスナップショットを取得します。 これは、時間をかけて使用状況を追跡するために定期的に実行することができます。 2番目のクエリは、インスタンスが最後に再起動されてからログファイルが増加した回数を返します。

ガイドとして使用できる既存のワークロードがなく、おそらくソフトウェアベンダーからの他のガイダンスがない場合、インデックスmaintenancewindows中にTempDBでソートされる可能性のある最大の人工的な個々のオブジェクトを考慮するためのスペースがTempDBにあることを確認することしかできません。 そこから、使用状況を監視し、それに応じて調整します。

USE TempDB;GOSELECT GETUTCDATE() AS SnapshotDateTime, groupid --0 = data, 1 = log, SUM(size/128.) SizeOnDiskInMB, SUM(FILEPROPERTY(name, 'spaceused')/128.) MBUsedWithinFile FROM TempDB.sys.sysfilesGROUP BY groupid; SELECT * FROM sys.dm_os_performance_countersWHERE counter_name = 'Log Growths' AND instance_name = 'tempdb';

サーバー TempDBデータベースメンテナンス

TempDBデータベースのデータベースとトランザクションログを縮小できますか？

技術的にはそうですが、これは完了すべき操作ではありません。目標は常に適切なサイズのTempDBにする必要があります。 ある種の一回限りの操作がこれらのファイルを不可解に成長させると、次のサービスの再起動時に正常に戻ります。

インデックスを再構築する必要がありますか？

TempDBは、インデックス関連の保守計画の一部ではありません。 これは、インデックスの再構築、再編成、または統計の更新を意味しません。

整合性チェックを実行する必要がありますコードは何ですか？

完全性をチェックすべきかどうかについては意見が異なる。この著者は議論をレイアウトする読者が決定することを可能にするために、反対し、許可します。

Against:整合性チェック操作は、他のすべてのデータベースと同じ全体を持つTempDBをチェックすることはできません。TempDBに存在するデータは非常に一時的なので、整合性チェック操作はいつでも多くのチェックを見つけることはありません。同じ理由で、破損したデータは、整合性チェック操作が呼び出されるずっと前にディスクから読み取られたときに警告を引き起こすアクティブなデー

For:整合性チェックoperationcannotはTempDB内のすべてをチェックしますが、値がないことを意味しない他のデータベースでチェックします。TempDBは不可欠なデータベースであり、それについて知っている破損がある場合は、是正措置を取ることができるように不可欠です。

整合性チェックを実行するためのコードは以下の通りです。

DBCC CHECKDB ('TempDB'); 

SQL Server TempDBデータベースオブジェクト

SQL Serverの専門家が知っておくべき重要なTEMPDBデータベースのキーテーブルとプロシージャは何ですか？

何もありません。このデータベースは、一時的なストレージのために使用されますobjects.It anytablesかプロシージャと取付けられていて来ません。

自分のオブジェクトをTEMPDBデータベースに保存できますか？

オブジェクトは確かにTempDBで作成できますが、SQL Serverサービスのthenext startを過ぎても保持されません。

SQL Server TempDBデータベース権限

誰がTempDBデータベースにアクセスでき、さまざまなレベルの権限がありますか？

誰もがTempDBへのアクセス権と同じアクセス権を持っています。

SQL Server TempDBデータベースのバックアップ

TempDBデータベースをバックアップする必要がありますか？

TempDBはバックアップできません。 一時的なデータのみを保存し、サービスが開始されるたびに再作成されるため、災害時に回復するものは何もありません。

SQL Server TempDBデータベース回復モデル

TempDBデータベースにはどのような回復TempDBがあり、回復モデルを変更できますか？

TempDBは単純復旧中であり、これは変更できません。

SQL Server TempDBデータベースの復元

TempDBデータベースを復元する必要がありますか？

TempDBを復元できません。 回復するためにTempdbにデータとコードがないはずです。 TempDBが破損した場合は、次のサービスの起動時に新しいファイルを生成できるように、データとログファイルを削除するだけです。

Next Steps

• このヒントは、TempDBサイズとディスク領域内で見つかった問題をtomakeが警告する方法を示しています
• このヒントは、すべてのDbにとって重要な機

最終更新: 2020-07-17

著者について

Eric Blinnはシニアです。 データアーキテクト従者パットンBoggsのために。 彼はまた、SQLの著者であり、ローカルグループのリーダーを渡します。
すべての私のヒントを表示

関連リソース

• より多くのSQL Server DBAのヒント。..