これでスッキリ！！　SQLServerダイエット！

どもuozy☆です。

突然ですが、今話題の「おにぎりダイエット」！！お腹まわりがちょっと気になるお年頃のuozy☆は今日から始めてみます！！

さて、今回はDBに負荷をかけているクエリを探す方法を書いてみます。MySQLではスロークエリログを設定する事で処理が遅いクエリを取得できますが、SQLServerではもっとお手軽に動的管理Viewをselectする事で同様に取得する事ができます。
※厳密に取得したい場合はトレースログを設定する事をおすすめします。

実行時間の長いクエリを取得

実はトレースログを仕込んでいなくても、ある程度SQLServerの中に実行時のデータが保存されています。そのデータを使用し実行時間の長いクエリを抽出する事ができます。dm_exec_query_statsという動的管理Viewがそれです。これをselectするとサーバーの内部情報が見えてきます。まずは実行時間をみてみましょう。

select top 30
    total_elapsed_time / execution_count / 1000 as AvgElapsedTime
    ,total_physical_reads / execution_count as AvgPhysicalIOCount
    ,[text] as SQLtext
    ,object_name(objectid) as objectname
from
    sys.dm_exec_query_stats
    cross apply sys.dm_exec_sql_text(sql_handle)
order by 
    AvgElapsedTime desc;

--total_elapsed_time：このプランの実行完了までの経過時間の合計 
--execution_count：前回のコンパイル時以降に、プランが実行された回数
--total_physical_reads：コンパイル後にこのプランの実行で行われた物理読み取りの合計数

物理IOが多いクエリを取得

「実行時間の長いクエリを取得」とほとんど同じですが、少し変更すると物理IOが多いクエリを取得する事ができます。DBの最大のボトルネックはディスクの読み出し速度が遅い事に起因するので、物理IOを減らしてあげれば必然的に速度もあがり、同時にサーバー負荷も減ります。なので物理IOを把握する事は超重要ですので是非チェックしてみてください。

select top 30
    total_elapsed_time / execution_count / 1000 as AvgElapsedTime
    ,total_physical_reads / execution_count as AvgPhysicalIOCount
    ,[text] as SQLtext
    ,object_name(objectid) as objectname
from
    sys.dm_exec_query_stats
    cross apply sys.dm_exec_sql_text(sql_handle)
order by 
    AvgPhysicalIOCount desc;

CPU時間の長いクエリを取得

またまた似たようなクエリですが、今度はCPU使用率の高いクエリを取得してみます。CPUが100%で張り付く場合などはコレをチェックしてみてください。uozy☆の経験上「暗黙の型変換」が発生している場合はCPUが上がるので、その辺も注意してみて貰えればと思います。

select top 30
    total_elapsed_time / execution_count / 1000 as AvgElapsedTime
    ,total_worker_time  / execution_count / 1000 as AvgCPUTime
    ,[text] as SQLtext
    ,object_name(objectid) as objectname
from
    sys.dm_exec_query_stats
    cross apply sys.dm_exec_sql_text(sql_handle)
order by 
    AvgCPUTime desc;

--total_worker_time：コンパイル後にプランの実行で使用された CPU 時間の合計

Indexの使用状況

次のクエリでは実際にIndexが使われてるのかどうかを、調査する事ができます。不要なIndexがあるとDBの領域を増やしてしまい、バックアップ時間の遅延やIndexメンテナンスの遅延などに繋がります。Indexを削除する事でDB領域のダイエットもできるので、思い切って削除してしまいましょう！！いらないものは捨てるのです！

select
    obj.name as TableName
  , idx.name as IndexName
  , sta.user_seeks as IndexSeekCount
  , sta.user_scans as IndexScanCount
from
    sys.objects as obj
    inner join sys.indexes as idx on obj.object_id = idx.object_id
    inner join sys.dm_db_index_usage_stats as sta on idx.index_id = sta.index_id
where
    obj.type = 'U' --ユーザーオブジェクトのみに限定
    and database_id = db_id('dbname');

おまけ

これはクエリチューニング時にちょっと使えるクエリです。SQLServerはディスクから一度読み出したデータはメモリ上に保持していて、次回そのデータを使用する場合はメモリから取得します。これはとても素晴らしい仕組みなのですが、それを知らずにクエリチューニングをすると「1回目は遅いんだけど次からは速い・・・なぜ？」なんて事になってしまいます。以下のクエリを実行すると、メモリ上に保持しているデータバッファをクリアできます。つまりいつも1回目の状態が再現できるわけです。