この記事では、TempDBデータベースを正常な状態に保ち、予期しないデータベースの増加を防ぐために従うべきベストプラクティスと、この予期しない増加が発生した場合にそれを検出するために従うことができる手順について説明します。
TempDBの概要
TempDBデータベースは、最も重要なSQL Serverシステムデータベースの一つであり、ユーザーによって定義された一時テーブル、テーブル値関数の実行、一時ストアドプロシージャ、テーブル変数、またはインデックスから返された一時テーブルなどの一時ユーザーオブジェクトを格納するために使用されます。
ユーザーオブジェクトに加えて、TempDBは、中間ソート、スプール、集計、カーソル操作などのさまざまな内部操作中にSQL Serverデータベースエンジンによって作成された内部オブ
TempDBシステムデータベースは、トランザクションのスナップショット分離レベル、オンラインインデックス再構築、複数のアクティブな結果セット機能など、表の行で実行された変更を追跡する必要がある機能をサポートするために、行のバージョンを格納するためにも使用されます。
TempDBデータベースは、SQL Serverサービスが再起動されるたびに削除され、データベースの新しいクリーンコピーから開始されて再作成されます。 この事実に基づいて、このデータベースに格納されているすべてのユーザーと内部データベースは、SQL Serverサービスが再起動されたとき、またはこれらのオブジェクトが したがって、バックアップ操作と復元操作はTempDBでは使用できません。
なぜTempDB
TempDBシステムデータベースは、SQL Serverパフォーマンスチューニングプロセスで重要な役割を果たします。 これは、さまざまな種類のユーザーデータベースオブジェクトを格納し、SQL Serverデータベースエンジン関連のプロセスを高速化するためにシステム内部オブジェク
TempDBシステムデータベースは、SQL Serverインスタンス全体のパフォーマンスを向上させる上で重要な役割を果たすため、TempDBデータベースのデータとログファイルに初期サイズ、自動拡張、および場所のベストプラクティスを適用することは非常に重要です。 これらのベストプラクティスをsql Serverインスタンスのワークロードに合った方法で適用すると、TempDBデータベースのデータとログファイルが非常に頻繁に拡張されなくなります。 この割り当てプロセスに必要な時間とリソースを想像することができます。
すべてのベストプラクティスを既に適用していて、予期しない拡張操作が発生した場合は、データベースインスタントファイル初期化オプションを使用して割り当てプロセスを高速化することができます。
ベストプラクティス
その共有リソースから最高のパフォーマンスを得て、基になるディスク領域を埋める可能性のあるTempDBデータベースデータまたはログ
これらの構成には、次のものが含まれます:
- TempDBデータファイルの数は、SQL Serverインスタンスがインストールされているコンピュータ上の論理プロセッサの数（最大8つのファイル）と一致する必要があり そのマシン上の論理プロセッサの数が8より大きい場合は、データファイルの数を8に設定し、TempDB競合の問題がある場合に備えて4の倍数で拡張する機能を備えています
- データファイル内にデータを書き込むために使用される比例塗りつぶし書き込みメカニズムにより、TempDBファイルのサイズが同じファイルグループ内で同じであることを確認してください。 詳細については、”SQL Server Proportional fill algorithmについて”を参照してください
- TempDBデータベースのデータとログファイルを、これらのファイルに書き込まれる予想されるデータ量に適合する合理的な初期サイズと自動拡張量、およびSQL Serverインスタンスで実行されるさまざまなタイプのワークロードを構成します。 I/Oボトルネック
- TempDBを、ユーザーデータベースや他のシステムデータベースから離れた別のディスクドライブに配置します。 このようにして、TempDBで実行される大量の読み取り操作と書き込み操作は、ユーザーデータベースのワークロードに影響を与えたり、影響を受けたりしないため、SQL Serverイ
適用先
SQL Server2016より前のバージョンでは、Sql Serverインスタンスをインストールした後、データベースのプロパティページからTempDBサイズの割り当てを実行できます。 ここでは、現在のSQL Serverインスタンスに適合するデータベースデータファイルの数、各データベースデータとログファイルの初期サイズ、自動拡張オプションの適
これらの変更を実行するには、SQL Server Management Studioでデータベースノードを展開し、システムデータベースノードを展開してから、tempdbデータベースを右クリックし、以下のよう:
開いたデータベースのプロパティウィンドウから、ファイルページを参照し、以下に示す異なるデータベースのデータとログファイルの設定を確認または変:
SQL Server2016バージョン以降では、インストールウィザードで既に提案されている現在のサーバーリソースに適合するベストプラクティスを使用して、これらのすべての変更を確認し、SQL Serverインスタンスのインストールプロセス中に実行できます。:
Temdbデータベースファイルの増加の追跡
TemDBリソースを消費しているクエリは、TempDBファイルに関連付けられたパフォーマンスモニ
TempDBデータベースファイルの増加を追跡するためのデータベース管理者のわかりやすい方法は、次の動的管理ビューを照会することです:
- sys.dm_db_file_space_usageデータベース内の各ファイルの領域使用状況情報を返し、その領域を消費したセッションまたはタスクを表示せずに
- sys.dm_db_session_space_usage各タスクごとの割り当ておよび割り当て解除されたページの数
sys.dm_db_session_space_usage dmvによって返された結果は、sysから返された結果を要約していることに注dm_db_task_space_usage DMVすべてのタスクが正常に完了すると、同じクエリに関連する複数のタスクが同じセッションで実行されるためです。 これらのDmvのもう一つの利点は、TempDBリソースを消費するオブジェクトのタイプと、このオブジェクトが一時テーブルや変数テーブルなどのユーザーオブジェク したがって、各オブジェクトで消費されるページ数に基づいて、TempDBが内部オブジェクトまたはユーザーオブジェクトによって消費されるかどうかを指定し、
sys.dm_db_file_space_usage
sys.dm_db_file_space_usage DMVは、TempDBデータベースファイルで使用されるディスク領域を監視するために使用できます。 以下のクエリは、未割り当て領域、行バージョンの格納に使用される領域、内部オブジェクトによって消費される領域、および最終的にユーザーオブジェク:
1234567891011SELECT(SUM(unallocated_extent_page_count)*1.0/128)AS Tempdb_Freespaceamount_InmbFROM sys.dm_db_file_space_usage;SELECT(SUM(version_store_reserved_page_count)*1.0/128)AS TEMPDB_versionstorespaceamount_inmbFrom Sys.Dm_Db_File_Space_usage;select(sum(internal_object_reserved_page_count)*1.0/128)As Tempdb_Internalobjspaceamount_InmbFROM sys.dm_db_file_space_usage;SELECT(SUM(user_object_reserved_page_count)*1.0/128)AS Tempdb_Userobjspaceamount_InmbFROM sys.dm_db_File_Space_Usage;ローカルのSQL Serverインスタンスから返される結果から、TempDBファイルには未使用の60MBがあり、内部オブジェクトには0.5MB、ユーザーオブジェクトには1.5MB:
sys.dm_db_task_space_usageとsys.dm_db_session_space_usage
sys.dm_db_task_space_usageとsys.dm_db_session_space Dmvは、各タスクまたはセッションによって割り当てられ、割り当て解除されたページ数tempdbデータベース。 このようにして、TempDB領域を使用しているユーザーまたは内部オブジェクトを確認できます。
たとえば、sysを照会すると、以下の結果が返されます。TempDBデータベースの下のdm_db_task_space_usage DMVは、次のように、各ユーザーまたは内部タスクに割り当てられ、割り当て解除されたページ数を返します:
しかし、DbaはTempDBリソースを消費しているクエリの追跡を開始するために多くの詳細を必要としません。 そのため、以下のクエリを使用して、各セッションの前の結果を要約する必要があります:
12345678SELECT session_id,SUM(internal_objects_alloc_page_count)AS N U M O F P A G E S A L C A Tedintempdbforinternaltask,SUM(internal_objects_dealloc_page_count)AS N U M O F P A G E S D E L L O C A Tedintempdbforinternaltask,SUM(internal_objects_dealloc_page_count)AS N U M O F P A G E S D E L L O C A Tedintempdbforinternaltask,SUM(internal_objects_dealloc_page_count)AS N U M O F P A G E S D E L L O C A Tedintempdbforinternaltask USER_objects_alloc_page_count)as n u m o F P a g e s a l c a tedintempdbforusertask,Sum(User_Objects_Dealloc_page_count)as n u m o f p a g E S D e l l o c a tedintempdbforusertaskFROM sys.dm_db_task_space_usageSESSION_idによるグループORDER BY N U M O F P A G E S A L C A Tedintempdbforinternaltask DESC,N U M O F P A G E S A L C A Tedintempdbforusertask DESC,N U M O F P A G E S A L C A Tedintempdbforinternaltask DESC,N U M O F P A G E S A L C A Tedintempdbforusertask DESCそして、各セッションごとの要約されたページの消費結果は以下のようになります:
sysを照会することで同じ結果を返すことができます。dm_db_session_space_usage以下に示すように、同じセッションで実行されているすべてのタスクのページの割り当てと割り当て解除を要約したDMW:
以下のように、内部オブジェクトとユーザーオブジェクトの両方を使用してTempDBデータベースに対していくつかのアクションを実行しましょう:
次に、内部オブジェクトとユーザーオブジェクトによって割り当てられたページと割り当て解除されたページの数を返す統合クエリを実行し、完了したdm_db_session_space_usage DMV)および未完了の操作(sys.dm_db_task_space_usage DMVを使用)):
1234567891011121314SpacePerTaskを選択します。session_id,SpacePerSession.internal_objects_alloc_page_count+SUM(SpacePerTask.n U M O F P A G E S A L C A Tedintempdbforinternaltaskとして、SpacePerSession。internal_objects_dealloc_page_count+SUM(SpacePerTask.n U M O F P A G E S D E L L O C A Tedintempdbforinternaltaskとして、SpacePerSession。User_objects_alloc_page_count+SUM(SpacePerTask.User_objects_alloc_page_count)としてN U M O F P A G E S A L C A Tedintempdbforusertask,SpacePerSession。User_objects_dealloc_page_count+SUM(SpacePerTask.User_objects_dealloc_page_count)As N U M O F P A G E S D E L O C A Tedintempdbforusertaskfrom sys.dm_db_session_space_usage AS SpacePerSessionINNER JOIN sys.dm_db_task_space_usage AS SpacePerTask ON SpacePerSession.session_id=SpacePerTask。session_idSPACEPERTASKによるグループ化。session_id、SpacePerSession。内部オブジェクト_alloc_page_count、SpacePerSession。インターナル_オブジェクト_dealloc_page_count,SpacePerSession.User_Objects_Alloc_Page_Count,SpacePerSession.User_objects_dealloc_page_countORDER BY N U M O F P A G E S A L C A Tedintempdbforinternaltask DESC,N U M O F P A G E S A L C A Tedintempdbforusertask DESCまた、前の操作の結果にはクエリ出力が反映され、ユーザー一時テーブルを作成し、インデックスの再構築プロセス中にデータをソートすることによって割り当:
TempDBリソースを消費するクエリのテキストを取得するには、ys.dm_db_task_space_usage DMVの結果をsys.dm_exec_requestsやsysなどの他のDmvと結合することによdm_exec_sessionsをsys.dm_exec_sql_text動的管理関数で使用すると、このクエリのようにtempdbスペースを消費しているクエリが表示されます。
結果は以下のようになります:
- 著者
- 最近の投稿
Ahmad Yaseenは、SQL BI、SQL Serverデータベース管理および開発分野における深い知識と経験を持つMicrosoftビッグデータエンジニアです。
データ管理と分析のMicrosoft認定ソリューションエキスパート、SQLデータベースの管理と開発のMicrosoft認定ソリューションアソシエイト、Azure Developer Associate、Microsoft認定トレーナーです。
また、彼は多くのブログで彼のSQLのヒントに貢献しています。
Ahmad Yaseenによるすべての投稿を見るAhmad Yaseenによる最新の投稿(すべて見る)- Azure Data Factoryのインタビューの質問と回答-2021年2月11日
- Azure Data Factoryを監視する方法-2021年1月15日
- Azure Data Factoryでソース管理を使用する-1月12, 2021