MS-SQL / SQL Server
SQL Server CPU 使用率が高いクエリを検索する方法
SQL ServerからCPU使用率が高くなった時に、どのクエリの影響でCPU使用率が高くなっているかkpidとspidで調べる方法に説明する。
まず、「スタート」→「管理ツール」→「パフォーマンスモニター」を起動する。
もしくは、「スタート」→「ファイル名を指定して実行」を選択するか「Windowキー」+「R」を押して「ファイル名を指定して実行」ウィンドウを開く。
「ファイル名を指定して実行」の「名前」に「perfmon」を入力して「OK」ボタンをクリックする。
パフォーマンスモニターで左の①の「パフォーマンスモニター」をクリックすると、
右に上記のようなグラフの画面が表示される。
②の追加ボタンを押してカウントを追加しよう。
まず、①の使用可能なカウンターリストから「Tread」項目を選択し、
下位項目では「%Processor Time」と「ID Thread」を選択する。
②の「<すべてのインスタンス>」を選んで「検索」ボタンを押すと
③のように選択したオブジェクトのインスタンスリストが表示される。
その中では「sqlserver/数字」になっている項目をすべて選択する。
④の「追加」ボタンを押して右の「追加されたカウンター」リストに追加する。
⑤の「OK」ボタンをクリックする。
これでカウンターが追加されグラフにも表示されるようになった。
今はサーバーに負荷がかかる、つまりSQL ServerにCPU使用率が高いクエリがないのでグラフに変動がない。
では、テストの為下記のクエリを作成したので、実行してCPU使用率が高くしてみよう。
DECLARE @Str varchar(100)
WHILE (1=1)
BEGIN
SET @Str = @@version
END
上記のクエリを実行してから「パフォーマンスモニター」にどんな変化があるかをみよう。
上記のクエリを実行した瞬間からグラフに変化が起きている。
では「グラフの種類を変更」をクリックして「レポート」に変更しよう。
「グラフの種類を変更」を「レポート」に変更したら下記のような画面に変わります。
Processor Informationの% Processor TimeのTotal値が上がっている。
Threadの部分でどのThreadからCPU使用率が上がっているのか調べてみよう。
「sqlserver 70」から「%Processor Time」が高くなっている。
では、あのThreadについて詳しく調べてみる方法について説明しよう。
問題がるThreadのIDは「6588」だ。
SELECT spid, kpid, dbid, cpu, memusage , status, open_tran
FROM sys.sysprocesses
WHERE kpid = 6588
上記のSQLでkpid値に問題があるThread ID「6588」を検索する。
Statusを見ると現在実行中だと表示されている。
どのデータベースからかは「dbid」を見れば分かる！
「dbid」は2。
SELECT database_id, name
FROM sys.databases
WHERE database_id = 2
上記の「database_id」に「dbid」値の２を入れて検索すると問題のクエリが実行されているデータベースが分かるのだ。
このテストはtempdbからしているので、tempdbだと表示されている。
では、どんなクエリが問題になっているのかを調べるためには下記のSQLを実行すると分かる。
dbcc inputbuffer(spid)
spid値は上記にkpidに6544を検索した結果にspid列に53と入っているので、
実際は下記のように作成して実行する。
dbcc inputbuffer(53)
実行結果
現在、テストの為に実行しているSQLが表示された。
これで問題があるSQLが分かるので、この部分を修正して行けば、
パフォーマンス改善ができる。