SQL Server パフォーマンスチューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム＞技術情報＞ SQL Server 2014 自習書 No.5 Microsoft Azure SQL Database 入門

SQL Server 2014 自習書シリーズ（HTML 版）
「No.5 Microsoft Azure SQL Database 入門」

松本美穂と松本崇博が執筆した SQL Server 2014 自習書シリーズの「No.5 Microsoft Azure SQL Database 入門」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2015年12月29日]

目次へ | 前のページへ | 次のページへ

3.6　DMV（動的管理ビュー）を利用した性能監視

（2015年1月時点での情報）

Azure SQL Database では、SQL Server での性能監視でお馴染みの「DMV」（動的管理ビュー：Dynamic Management View）も、主要なものがサポートされています。DMV は、最近のバージョンアップで、随時強化されているところになっていて、クエリチューニングでよく利用する「dm_exec_query_stats」（クエリ統計）や、インデックスの使用状況をチェックすることができる「dm_db_index_usage_stats」、Missing インデックス（不足しているインデックス）をチェックすることができる「dm_db_missing_index_~」などを利用できます。これらは、次のように利用できます。

CPU 利用の多いクエリの参照

CPU 利用の多いクエリは、dm_exec_query_stats ビューを利用して確認することができます。

-- CPU 利用の多いクエリ TOP 10
SELECT TOP 10
    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 1 DESC

インデックス サイズの確認

インデックスのサイズは、dm_db_partition_stats ビューを利用して確認することができます。

SELECT OBJECT_NAME(ps.object_id) AS objName
      ,ps.index_id, MAX(i.name) AS idxName
      ,SUM(ps.used_page_count) * 8 / 1024.0 AS sizeMB
      ,SUM(ps.row_count) AS rowCnt
FROM
sys.dm_db_partition_stats ps
     LEFT OUTER JOIN sys.indexes i
        ON ps.object_id = i.object_id
       AND ps.index_id = i.index_id
WHERE OBJECTPROPERTYEX(ps.object_id, 'IsUserTable') = 1
GROUP BY ps.object_id, ps.index_id

インデックスの断片化状況

インデックスの断片化状況は、dm_db_index_physical_stats を利用して確認することができます。

-- t1 テーブルの断片化状況を確認
SELECT OBJECT_NAME(object_id), index_id, avg_fragmentation_in_percent
,index_level, page_count, record_count
FROM
sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('t1'), NULL, NULL, 'DETAILED')
ORDER BY index_id, index_level DESC

未使用インデックス

一度も利用されたことがないインデックスは、dm_db_index_usage_statsを利用して確認することができます。

SELECT o.name AS table_name, i.name AS index_name, *
FROM sys.objects o
      INNER JOIN sys.indexes i ON o.object_id = i.object_id
WHERE o.type = 'U'
   AND i.index_id
       NOT IN
         ( SELECT s.index_id
            FROM sys.dm_db_index_usage_stats s
             WHERE s.object_id = i.object_id
               AND i.index_id = s.index_id
               AND database_id = DB_ID() )

Missing インデックス（不足しているインデックス）

Missing インデックス（不足しているインデックス）は、dm_db_missing_index_~ を利用して確認することができます。

SELECT group_handle, i.*, gs.*
FROM sys.dm_db_missing_index_group_stats gs
      INNER JOIN sys.dm_db_missing_index_groups g
              ON gs.group_handle = g.index_group_handle
      INNER JOIN sys.dm_db_missing_index_details i
              ON i.index_handle = g.index_handle

Missing インデックスは、SQL Server と同様、実行プランを利用しても、次のように確認することができます。

00261

プロシージャ キャッシュの参照

プロシージャキャッシュは、dm_exec_cached_plans を利用して確認することができます。

SELECT query_plan, size_in_bytes / 8192 AS [使用ページ数], *
FROM
  sys.dm_exec_cached_plans
    CROSS APPLY
      sys.dm_exec_query_plan (plan_handle)

ロック／ブロッキングに関する情報の参照

ロックに関する情報は、dm_tran_locks を利用して確認することができます。

SELECT * FROM sys.dm_tran_locks

ロックに関しては、sp_lock はサポートされていませんが、sp_who2 がサポートされているので、次のようにブロッキング情報（どのプロセスによってブロックされているのか）を確認することもできます。

00264

なお、現在の ”待機” の情報（ロック待ちだけでなく、さまざまな内部的な待機に関する情報）を確認することができる dm_os_waiting_tasks に関しては、執筆時点では、Standard エディションの S2 レベル以上であれば、利用できることを確認できました（2015年1月時点での情報）。

SELECT * FROM sys.dm_os_waiting_tasks

また、”待機” に関する統計情報（Wait Stats）についても、S2 レベル以上であれば、次のように利用できることを確認できました。

Wait Stats（待機の統計情報）の参照

-- Wait Stats（待機に関する情報の取得）
SELECT * FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

その他、S2 レベル以上であれば、次のようにパフォーマンスカウンターの情報（dm_os_performance_counters）や、メモリ使用状況の情報（dm_os_buffer_descriptors）についても、利用できることを確認できました。

パフォーマンス カウンター情報の参照

-- パフォーマンスカウンター情報の取得
SELECT * FROM sys.dm_os_performance_counters

メモリ使用量の内訳（オブジェクトごとのメモリ使用量）

-- オブジェクトごとのメモリ使用量の内訳
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()
GROUP BY b.database_id, p.object_id, p.index_id
ORDER BY 使用ページ数 DESC

このように、Azure SQL Database では、データベースを監視するための主要な DMV（動的管理ビュー）がサポートされているので、SQL Server を監視するのと同じように監視することができます（クエリチューニングやインデックスチューニング、トラブルシューティングを行っていくことができます）。

なお、DMV やインデックスチューニング、ロック（ブロッキング）などについては、SQL Server 2012 の自習書シリーズの「監視ツールの基本操作」や、「インデックスの基礎とメンテナンス」、「ロックと読み取り一貫性」編で詳しく説明しているので、こちらもぜひご覧いただければと思います。

目次へ | 前のページへ | 次のページへ