[SQLServer] クエリのパフォーマンス調査・ボトルネックの特定

ホーム
BLOG
Programming｜開発
[SQLServer] クエリのパフォーマンス調査・ボトルネックの特定

[SQLServer] クエリのパフォーマンス調査・ボトルネックの特定

Programming｜開発
2017.08.08

「前まで早かったんだけど、データが増えてきて遅くなってきた・・・」等、パフォーマンスにまつわる問題は切っても切れない関係になってきています(ﾉд･｡)今回は、SQLServer上でパフォーマンス調査する際に役立つクエリをご紹介します！ボトルネック特定等にお役立て下さい(｡･ω･｡)

[SQLServer] クエリのパフォーマンス調査・ボトルネックの特定

SQL Serverでクエリのパフォーマンス測定方法

1. 実行プランをONにする

パフォーマンスを考える際は、まずはクエリの実行プランを見ます。実行プランを見て、SQL Serverが意図した形で検索をしているかを確認しましょう。動作下記コマンドを実行することで、実行プランをONにできます。

--# 実行プランのON・OFF方法
SET SHOWPLAN_TEXT ON

通常のSELECTの場合、PKのインデックスが適用されます。

SELECT * FROM table_sample

--# [結果]
SELECT * FROM table_sample WITH(INDEX(idx_table_sample_01,idx_table_sample_02,idx_table_sample_03))

インデックスを指定してSELECTの場合、指定したインデックスが適用されます。

SELECT * FROM table_sample WITH(INDEX(idx_table_sample_01,idx_table_sample_02,idx_table_sample_03))

--# [結果]
  |--Nested Loops(Inner Join, OUTER REFERENCES:([di1].[dbo].[table_sample_syn].[tr_contract_no], [di1].[dbo].[table_sample_syn].[detail_no]))
       |--Hash Match(Inner Join, HASH:([di1].[dbo].[table_sample_syn].[tr_contract_no], [di1].[dbo].[table_sample_syn].[detail_no])=([di1].[dbo].[table_sample_syn].[tr_contract_no], [di1].[dbo].[table_sample_syn].[detail_no]), RESIDUAL:([di1].[dbo].[table_sample_syn].[tr_contract_no] = [di1].[dbo].[table_sample_syn].[tr_contract_no] AND [di1].[dbo].[table_sample_syn].[detail_no] = [di1].[dbo].[table_sample_syn].[detail_no]))
       |    |--Hash Match(Inner Join, HASH:([di1].[dbo].[table_sample_syn].[tr_contract_no], [di1].[dbo].[table_sample_syn].[detail_no])=([di1].[dbo].[table_sample_syn].[tr_contract_no], [di1].[dbo].[table_sample_syn].[detail_no]), RESIDUAL:([di1].[dbo].[table_sample_syn].[tr_contract_no] = [di1].[dbo].[table_sample_syn].[tr_contract_no] AND [di1].[dbo].[table_sample_syn].[detail_no] = [di1].[dbo].[table_sample_syn].[detail_no]))
       |    |    |--Index Scan(OBJECT:([di1].[dbo].[table_sample_syn].[idx_table_sample_01]))
       |    |    |--Index Scan(OBJECT:([di1].[dbo].[table_sample_syn].[idx_table_sample_02]))
       |    |--Index Scan(OBJECT:([di1].[dbo].[table_sample_syn].[idx_table_sample_03]))
       |--Clustered Index Seek(OBJECT:([di1].[dbo].[table_sample_syn].[table_sample_primary_key]), SEEK:([di1].[dbo].[table_sample_syn].[tr_contract_no]=[di1].[dbo].[table_sample_syn].[tr_contract_no] AND [di1].[dbo].[table_sample_syn].[detail_no]=[di1].[dbo].[table_sample_syn].[detail_no]) LOOKUP ORDERED FORWARD)

まずは、実行クエリが意図した動作になっているかどうかを確認しましょう

2. 使用頻度の少ないインデックスの一覧を取得

--# --------------------------------------------
--# 使用頻度の少ないインデックスの一覧を取得
--# --------------------------------------------
SELECT
  o.name 'テーブルID'
  , i.name 'インデックスID'
  , s.user_seeks 'Index Seek数'
  , s.user_scans 'Index Scan数'
  , s.user_updates 'UPDATE数'
  , s.user_seeks + s.user_scans + s.user_updates '合計'
FROM
  sys.objects o
  INNER JOIN sys.indexes i
    ON
      o.object_id = i.object_id
  INNER JOIN sys.dm_db_index_usage_stats s 
    ON
      i.index_id = s.index_id
WHERE
  o.type = 'U' --# ユーザテーブルを対象
  AND database_id = DB_ID()
ORDER BY
  s.user_seeks + s.user_scans + s.user_updates

3. CPU利用の高いクエリを取得する

CPU利用が高い＝計算処理等頭を使う処理が多いと言えます。SQLServerのCPU使用率が100％に近い場合は、下記クエリで原因を特定しましょう。

--# -----------------------------------------------------------
--# CPU利用の多いクエリを取得する
--# -----------------------------------------------------------
SELECT TOP 100
  total_worker_time / execution_count / 1000.0 AS [平均 CPU 時間（ミリ秒）]
  , total_elapsed_time / execution_count / 1000.0 AS [平均実行時間（ミリ秒）]
  , SUBSTRING(
        text
        , (statement_start_offset / 2) + 1
        , (
            (CASE statement_end_offset
                WHEN -1 THEN DATALENGTH(text)
                ELSE statement_end_offset
                END - statement_start_offset
             ) / 2
           ) + 1
        ) AS sql
FROM
  sys.dm_exec_query_stats 
CROSS APPLY
  sys.dm_exec_sql_text(sql_handle)
ORDER BY

4. オブジェクトごとのメモリ使用量を取得

メモリ使用量が多い場合、ほか処理でメモリが利用できず、処理速度が低下します。不要なメモリ使用量を減らし、処理速度を高めましょう。

--# ------------------------------------------------
--# オブジェクトごとのメモリ使用量を取得
--# ------------------------------------------------
SELECT
  DB_NAME (b.database_id) ,OBJECT_NAME(p.object_id) ,p.index_id
  , COUNT(*) As 使用ページ数
FROM
  sys.allocation_units a
  INNER JOIN sys.partitions p
    ON
      a.container_id = p.hobt_id
  INNER JOIN sys.dm_os_buffer_descriptors b
    ON
      a.allocation_unit_id = b.allocation_unit_id
WHERE
  b.database_id = DB_ID() --# 現在接続中の DB のみ
GROUP BY
  b.database_id, p.object_id, p.index_id
ORDER BY
  使用ページ数 DESC

5. アクセスコストが高いテーブルTOP100を取得する（インデックス不足起因）

インデックスが必要な項目にはインデックスを追加して、処理高速化を目指します( • ̀ω•́ )✧

--# ----------------------------------------------------------
--# アクセスコストが高いテーブルTOP100（インデックス不足起因）
--#   等号列：インデックスを張っていないが、WHERE句に指定している列
--#   不等号列：
--#   付加列：インデックスを張る際に、付加列として必要な列
--# ----------------------------------------------------------
SELECT TOP 100
  last_user_seek AS '最後にシークした時間'
  , [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact *(user_seeks + user_scans),0)
  , avg_user_impact
  , statement AS 'オブジェクト名'
  , equality_columns AS '等号列'
  , inequality_columns AS '不等号列'
  , included_columns   AS '包括列'
  , unique_compiles AS 'コンパイルおよび再コンパイルの数'
  , user_seeks AS 'クエリによって発生したシーク数'
FROM
  sys.dm_db_missing_index_groups mig
  INNER JOIN sys.dm_db_missing_index_group_stats migs
    ON
      migs.group_handle = mig.index_group_handle
  INNER JOIN sys.dm_db_missing_index_details mid
    ON
      mid.index_handle = mig.index_handle
ORDER BY
  [Total Cost] DESC

上記にて取得したレコードに対しては、インデックスを作成しましょう(｡･ω･｡)

--# 上記でコストが高いものに対して、下記のようにインデックスを張る
CREATE NONCLUSTERED INDEX [インデックス名]
ON [オブジェクト名] ([等号列])
INCLUDE ([包括列を、カンマ区切で列挙])

6. テーブルの断片化率を調査

テーブルの断片化率を調査し、断片化率が高いものはインデックス再構築等を実施しますv(｡･ω･｡)ｨｪｨ♪

--# --------------------------------------------------------
--# インデックスの断片化率調査
--# --------------------------------------------------------
--# 定数
DECLARE @DBID NVARCHAR(10) = '[データベースのID]'
DECLARE @PERCENT NVARCHAR(10) = '[断片化率]'

--# インデックスの断片化率取得クエリ
SELECT
  DB_NAME(a.database_id)
  , OBJECT_NAME(a.object_id)
  , a.index_type_desc
  , b.name
  , a.fragment_count
  , a.avg_fragmentation_in_percent
FROM
  sys.dm_db_index_physical_stats(DB_ID(@DBID), NULL, NULL, NULL, 'LIMITED') a
  LEFT JOIN sys.indexes b
    ON
      b.object_id = a.object_id
      AND b.index_id = a.index_id
WHERE
  a.avg_fragmentation_in_percent > @PERCENT

上記にて取得したレコードに対しては、インデックスの再構築等を検討しましょう(｡･ω･｡)

7. 待機中のリソース確認（リアルタイム）

待機中のリソースを確認し、待ちが発生している原因を調査します

--# ------------------------------------
--# 待機中のリソース分析（リアルタイム）
--# ------------------------------------
SELECT
  a.session_id 'セッションID'
  , a.wait_duration_ms '待機時間（ms）'
  , a.wait_type '待機タイプ'
  , b.waiting_tasks_count '総待機発生回数'
  , b.wait_time_ms '総待機時間（ms）'
  , b.max_wait_time_ms '過去最大待機時間（ms）'
  , a.resource_description 'リソース説明'
  , c.name '対象DB（<db-id>:<file-id>:<page-in-file>）'
FROM
  sys.dm_os_waiting_tasks a --# 待機中リソースの確認（リアルタイム）
  LEFT JOIN sys.dm_os_wait_stats b --# 待機中リソースの総計
    ON
      b.wait_type = a.wait_type
  LEFT JOIN sys.databases c --# DB情報
  ON
    c.database_id = LEFT(a.resource_description,1)
WHERE
  1 = 1
--  AND a.wait_type LIKE 'PAGE%LATCH%' --# ページラッチ待ち（Buffer Latch）を取得 
--  AND a.wait_type LIKE 'LATCH%' --# ページラッチ待ち（Non Buffer Latch）を取得 
--  AND a.wait_type LIKE 'WRITELOG' --# ログ書き込み待ちを取得

上記で多く待ちが発生しているものがあれば、待ちの深掘り分析を開始しましょう(｡･ω･｡)