今回のやることを説明してみる
TempDB に関して気にしない人、多いです。一時テーブル ( ローカル、グローバル )とか、インデックスのリビルド時とか ( SORT_IN_TEMPDB = ONのとき )、データの並べ替えなどお世話になることが多いです。そこで今回は、TempDB にどれだけお世話になっているか見てみましょう。
使用する環境を説明してみる
今回は、Microsoft Azure 上の仮想サーバではなく、私の Windows 8.1 Update1、64ビット 上で 動作している SQL Server 2014 です。使用するデータベースは、AdventureWorksDW2014 です。
実際にためしてみる
では、早速下記のクエリを SQL Server Management Studio で実行してみましょう。( なんか適当なクエリになってます。 ) 1ページあたりの容量は 8KByte なので、memory_usage、user_objects_alloc_page_count、user_objects_dealloc_page_count、internal_objects_alloc_page_count、internal_objects_dealloc_page_count に 8をかけることで、各予約・割り当て容量を算出しています。それ以外にも、発行されたクエリ、ヒットした行数、実行プランなども出力しています。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
SELECT [セッションID] = es.session_id,
[クライアント接続方法] = es.client_interface_name,
[ログイン] = es.login_name,
[メモリ使用量(Byte)] = es.memory_usage * 8,
[ユーザーオブジェクトに予約されたor割り当てられたページ数]
= su.user_objects_alloc_page_count,
[ユーザーオブジェクトへの割り当てが解除され予約されなくなったページ数]
= su.user_objects_dealloc_page_count,
[内部オブジェクトに予約されたor割り当てられたページ数]
= su.internal_objects_alloc_page_count,
[内部オブジェクトへの割り当てが解除され予約されなくなったページ数]
= su.internal_objects_dealloc_page_count,
[ユーザーオブジェクトに予約されたor割り当てられたページ(KByte)]
= su.user_objects_alloc_page_count * 8,
[ユーザーオブジェクトへの割り当てが解除され予約されなくなったページ(KByte)]
= su.user_objects_dealloc_page_count * 8,
[内部オブジェクトに予約されたor割り当てられたページ(KByte)]
= su.internal_objects_alloc_page_count * 8,
[内部オブジェクトへの割り当てが解除され予約されなくなったページ(KByte)]
= su.internal_objects_dealloc_page_count * 8,
[行数] = es.row_count,
[SQL文] = SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN
DATALENGTH(st.text)
ELSE
qs.statement_end_offset
END - qs.statement_start_offset)/2
) + 1),
[実行プラン] = qp.query_plan,
[トランザクション分離レベル] = CASE es.transaction_isolation_level
WHEN 0 THEN
'Unspecified'
WHEN 1 THEN
'ReadUncomitted'
WHEN 2 THEN
'ReadCommitted'
WHEN 3 THEN
'Repeatable'
WHEN 4 THEN
'Serializable'
WHEN 5 THEN
'Snapshot'
END
FROM sys.dm_db_session_space_usage su
INNER JOIN sys.dm_exec_sessions es
ON su.session_id = es.session_id
INNER JOIN sys.dm_exec_connections ec
ON su.session_id = ec.session_id
FULL OUTER JOIN sys.dm_exec_query_stats qs
ON ec.most_recent_sql_handle = qs.sql_handle
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY es.login_name
|
実際に TempDB を使用するようなクエリを実行してみましょう。70万件くらいのデータを全件ソートするような無茶なクエリです。
1
2
|
USE AdventureWorksDW2014
SELECT * FROM [dbo].[FactProductInventory] Order BY MovementDate
|
再度 TempDB の調査クエリを実行します。赤枠部分に上図で実行されたクエリの結果が出力されています。TempDB を55Mくらい使用したことが判断できます。
おわりにひとこといってみる
TempDB が自動拡張されていたり、使用率が異常に高かったりすると、結果として ディスク装置へのI/Oが高くなり、DBMS に悪影響を及ぼすので注意しましょう。
