都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

SQL Server のチューニングについてまとめてみる - その9 - ( TempDB の動きも気にしてみる )

今回のやることを説明してみる

 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

f:id:koogucc11:20141005124415p:plain

 実際に TempDB を使用するようなクエリを実行してみましょう。70万件くらいのデータを全件ソートするような無茶なクエリです。

1
2
USE AdventureWorksDW2014
SELECT * FROM [dbo].[FactProductInventory] Order BY MovementDate

f:id:koogucc11:20141005125130p:plain

  再度 TempDB の調査クエリを実行します。赤枠部分に上図で実行されたクエリの結果が出力されています。TempDB を55Mくらい使用したことが判断できます。

f:id:koogucc11:20141005125419p:plain

おわりにひとこといってみる

 TempDB が自動拡張されていたり、使用率が異常に高かったりすると、結果として ディスク装置へのI/Oが高くなり、DBMS に悪影響を及ぼすので注意しましょう。