SQLServerが遅いぞ問題、まだ戦ってます。
そんな中、SQL使い回しがどれくらいできているのか、つまり、全体のSQL処理数のうち、どれくらいが再利用なのか、を誰でも判るようにしておかないといけない流れの予感。そこで、これらを数値的に出してみよう、というのが今回のお話。
その前に、SQLServerのSQL文実行する場合の思考回路のおさらいw
SQLを実行しろ、と渡される→すでにそのSQLを使ったことがあるか考える
→んなもんシラネーヨ→CacheInert
→使ったことあるかも→CacheHit
→なーんか使ったと思うけど、ちょっと微妙、ちがうかもしんない、自信ないから、やっぱCacheに入れなおすわ→Recompile
つまり、発行され処理されるSQLは必ず上の3つのどれかのルートを通って、SQLServerで処理されていることになります。そこでProfilerを使ってトレースを集め、これらの分布を調べれば、どれくらい再利用されているのか出来てないのかがわかるって寸法w
まずはProfilerを起動し、調べたいサーバーにつないでテンプレートを新規作成。イベントの選択は以下のようにしておきます。
今回の場合必要となる列は、TextData,Database があれば良いんで、他は無視しますけど、一通りのイベントに関する情報を取得したい場合には、トレーステンプレート作成時に「全ての列を選択」して実行し、ログをためておく必要があるので注意してください。自分が「取るよーん」と指定した列項目以外がNULLで保存されてしまいます。
準備できたら、トレース開始。後は好きにソフトウェアをいじるなり、なんなり(笑) ログをたっぷり貯めたら、「ファイル」>「名前をつけて保存」>「トレースファイル」を選択してtrcファイルを保存します。
さて、保存したファイルを料理しましょう。まずは下準備、ログの内容を全てテーブルに変換します。あ、もちろんですが、保存したトレースファイルは分析処理をする予定のPCというかサーバーの物理パスにおいて、SQLServerから読める状態にしてくださいね(^^;
USE hoge;
GO
SELECT IDENTITY(int, 1, 1) AS RowNumber, * INTO temp_trc
FROM fn_trace_gettable('c:\temp\my_trace.trc', default);
GO
さて、テーブルできてますか?
入っているのを確認したら、後は分析のSQLを走らせれば万事OK
select eventclass , count(*) from temp_trc group by eventclass
でイベントごとの実行数を取得。テーブルに格納されているイベントクラスは数字ですが、ちゃんと意味があります。
10 |
RPC:Completed |
11 |
RPC:Starting |
12 |
SQL:BatchCompleted |
13 |
SQL:BatchStarting |
14 |
Login |
15 |
Logout |
16 |
Attention |
17 |
ExistingConnection |
18 |
ServiceControl |
19 |
DTCTransaction |
20 |
Login Failed |
21 |
EventLog |
22 |
ErrorLog |
23 |
Lock:Released |
24 |
Lock:Acquired |
25 |
Lock:Deadlock |
26 |
Lock:Cancel |
27 |
Lock:Timeout |
28 |
DOP Event |
33 |
Exception |
34 |
SP:CacheMiss |
35 |
SP:CacheInsert |
36 |
SP:CacheRemove |
37 |
SP:Recompile |
38 |
SP:CacheHit |
39 |
SP:ExecContextHit |
40 |
SQL:StmtStarting |
41 |
SQL:StmtCompleted |
42 |
SP:Starting |
43 |
SP:Completed |
44 |
Reserved |
45 |
Reserved |
46 |
Object:Created |
47 |
Object:Deleted |
48 |
Reserved |
49 |
Reserved |
50 |
SQL Transaction |
51 |
Scan:Started |
52 |
Scan:Stopped |
53 |
CursorOpen |
54 |
Transaction Log |
55 |
Hash Warning |
58 |
Auto Update Stats |
59 |
Lock:Deadlock Chain |
60 |
Lock:Escalation |
61 |
OLE DB Errors |
67 |
Execution Warnings |
68 |
Execution Plan |
69 |
Sort Warnings |
70 |
CursorPrepare |
71 |
Prepare SQL |
72 |
Exec Prepared SQL |
73 |
Unprepare SQL |
74 |
CursorExecute |
75 |
CursorRecompile |
76 |
CursorImplicitConversion |
77 |
CursorUnprepare |
78 |
CursorClose |
79 |
Missing Column Statistics |
80 |
Missing Join Predicate |
81 |
Server Memory Change |
82 |
User Configurable |
83 |
User Configurable |
84 |
User Configurable |
85 |
User Configurable |
86 |
User Configurable |
87 |
User Configurable |
88 |
User Configurable |
89 |
User Configurable |
90 |
User Configurable |
91 |
User Configurable |
92 |
Data File Auto Grow |
93 |
Log File Auto Grow |
94 |
Data File Auto Shrink |
95 |
Log File Auto Shrink |
96 |
Show Plan Text |
97 |
Show Plan ALL |
98 |
Show Plan Statistics |
99 |
Reserved |
100 |
RPC Output Parameter |
101 |
Reserved |
102 |
Audit Statement GDR |
103 |
Audit Object GDR |
104 |
Audit Add/Drop Login |
105 |
Audit Login GDR |
106 |
Audit Login Change Property |
107 |
Audit Login Change Password |
108 |
Audit Add Login to Server Role |
109 |
Audit Add DB User |
110 |
Audit Add Member to DB |
111 |
Audit Add/Drop Role |
112 |
App Role Pass Change |
113 |
Audit Statement Permission |
114 |
Audit Object Permission |
115 |
Audit Backup/Restore |
116 |
Audit DBCC |
117 |
Audit Change Audit |
118 |
Audit Object Derived Permission |
くわしくは、
http://support.microsoft.com/kb/283786/EN-US/
毎度毎度、日本語でなくてごめんなさい。でもこのページのサンプルSQLは中々の力作ですよん。
というわけで、これで統計出してプレゼン資料も……多分、大丈夫w