動的管理ビューを使用して Microsoft Azure SQL Database のパフォーマンスを監視する
適用対象:
Azure SQL Database
Microsoft Azure SQL Database では、動的管理ビューのサブセットを使用して、クエリのブロックまたは長時間の実行、リソースのボトルネック、不適切なクエリ プランなどが原因で発生する可能性のあるパフォーマンスの問題を診断できます。
この記事では、T-SQL を使用して動的管理ビューにクエリを実行することで一般的なパフォーマンスの問題を検出する方法について説明します。 次のような任意のクエリ ツールを使用できます。
アクセス許可
Azure SQL Database では、コンピューティング サイズとデプロイ オプションに応じて、DMV のクエリを実行するには、VIEW DATABASE STATE または VIEW SERVER STATE アクセス許可が必要になる場合があります。 後者のアクセス許可は、##MS_ServerStateReader## サーバー ロールのメンバーシップを介して付与できます。
VIEW DATABASE STATE アクセス許可を特定のデータベース ユーザーに付与するには、例として次のクエリを実行します。
GRANT VIEW DATABASE STATE TO database_user;
##MS_ServerStateReader## サーバー ロールへのメンバーシップを Azure の論理サーバーのログインに付与するには、master データベースに接続し、例として次のクエリを実行します。
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [login];
SQL Server のインスタンスおよび Azure SQL Managed Instance では、動的管理ビューにサーバーの状態についての情報が返されます。 Azure SQL Database では、現在の論理データベースに関する情報のみが返されます。
CPU パフォーマンスに関する問題の特定
CPU 消費量が長時間にわたって 80% を超える場合は、CPU の問題が現在発生しているか、過去に発生したかに関係なく、次のトラブルシューティング手順を検討してください。
CPU に関する問題が現在発生している
現時点で問題が発生している場合、2 つのシナリオが考えられます。
個別のクエリが多数あり、大量の CPU が累積的に消費されている
上位のクエリ ハッシュを特定するには、次のクエリを使用します。
PRINT '-- top 10 Active CPU Consuming Queries (aggregated)--'; SELECT TOP 10 GETDATE() runtime, * FROM (SELECT query_stats.query_hash, SUM(query_stats.cpu_time) 'Total_Request_Cpu_Time_Ms', SUM(logical_reads) 'Total_Request_Logical_Reads', MIN(start_time) 'Earliest_Request_start_Time', COUNT(*) 'Number_Of_Requests', SUBSTRING(REPLACE(REPLACE(MIN(query_stats.statement_text), CHAR(10), ' '), CHAR(13), ' '), 1, 256) AS "Statement_Text" FROM (SELECT req.*, SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1) AS statement_text FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ) AS query_stats GROUP BY query_hash) AS t ORDER BY Total_Request_Cpu_Time_Ms DESC;
実行時間の長いクエリが CPU を消費しており、実行中のままである
これらのクエリを特定するには、次のクエリを使用します。
PRINT '--top 10 Active CPU Consuming Queries by sessions--'; SELECT TOP 10 req.session_id, req.start_time, cpu_time 'cpu_time_ms', OBJECT_NAME(ST.objectid, ST.dbid) 'ObjectName', SUBSTRING(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset / 2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text)ELSE req.statement_end_offset END-req.statement_start_offset)/ 2)+1), CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ORDER BY cpu_time DESC; GO
CPU に関する問題が過去に発生した
過去に問題が発生していて、根本原因分析を行いたい場合は、クエリ ストアを使用します。 データベースにアクセスできるユーザーは、T-SQL を使用して、クエリ ストア データにクエリを実行できます。 クエリ ストアの既定の構成では、1 時間の粒度が使用されます。
大量の CPU を消費するクエリのアクティビティを確認するには、次のクエリを使用します。 このクエリは、CPU の消費が上位 15 のクエリを返します。 必ず
rsi.start_time >= DATEADD(hour, -2, GETUTCDATE()を変更してください。-- Top 15 CPU consuming queries by query hash -- Note that a query hash can have many query ids if not parameterized or not parameterized properly WITH AggregatedCPU AS ( SELECT q.query_hash ,SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms ,SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_ms ,MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms ,MAX(max_logical_io_reads) max_logical_reads ,COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans ,COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids ,SUM(CASE WHEN rs.execution_type_desc = 'Aborted' THEN count_executions ELSE 0 END) AS Aborted_Execution_Count ,SUM(CASE WHEN rs.execution_type_desc = 'Regular' THEN count_executions ELSE 0 END) AS Regular_Execution_Count ,SUM(CASE WHEN rs.execution_type_desc = 'Exception' THEN count_executions ELSE 0 END) AS Exception_Execution_Count ,SUM(count_executions) AS total_executions ,MIN(qt.query_sql_text) AS sampled_query_text FROM sys.query_store_query_text AS qt INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id INNER JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id INNER JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id WHERE rs.execution_type_desc IN ('Regular','Aborted','Exception') AND rsi.start_time >= DATEADD(HOUR, - 2, GETUTCDATE()) GROUP BY q.query_hash ) ,OrderedCPU AS ( SELECT query_hash ,total_cpu_ms ,avg_cpu_ms ,max_cpu_ms ,max_logical_reads ,number_of_distinct_plans ,number_of_distinct_query_ids ,total_executions ,Aborted_Execution_Count ,Regular_Execution_Count ,Exception_Execution_Count ,sampled_query_text ,ROW_NUMBER() OVER ( ORDER BY total_cpu_ms DESC ,query_hash ASC ) AS query_hash_row_number FROM AggregatedCPU ) SELECT OD.query_hash ,OD.total_cpu_ms ,OD.avg_cpu_ms ,OD.max_cpu_ms ,OD.max_logical_reads ,OD.number_of_distinct_plans ,OD.number_of_distinct_query_ids ,OD.total_executions ,OD.Aborted_Execution_Count ,OD.Regular_Execution_Count ,OD.Exception_Execution_Count ,OD.sampled_query_text ,OD.query_hash_row_number FROM OrderedCPU AS OD WHERE OD.query_hash_row_number <= 15 --get top 15 rows by total_cpu_ms ORDER BY total_cpu_ms DESC;問題となるクエリを特定したら、今度はそれらのクエリを調整して、CPU 使用率を抑えます。 クエリを調整する時間がない場合は、問題を回避するためにデータベースの SLO をアップグレードすることもできます。
Azure SQL Database の CPU パフォーマンスに関する問題の処理の詳細については、「Azure SQL Database での高い CPU の診断とトラブルシューティング」を参照してください。
I/O パフォーマンスの問題を特定する
ストレージ入出力 (I/O) パフォーマンスに関する問題を特定する場合、I/O の問題に関連する待機の種類のうち、上位のものは次のとおりです。
PAGEIOLATCH_*データ ファイル I/O に関する問題の場合 (
PAGEIOLATCH_SH、PAGEIOLATCH_EX、PAGEIOLATCH_UPなど)。 待機の種類の名前に IO が含まれている場合は、I/O の問題を示しています。 ページ ラッチ待機の名前に IO が含まれていない場合は、それにより別の種類の問題 (例:tempdbの競合) が示されます。WRITE_LOGトランザクション ログ I/O に関する問題の場合。
I/O に関する問題が現在発生している場合
sys.dm_exec_requests または sys.dm_os_waiting_tasks を使用して、wait_type と wait_time を確認します。
データおよびログの I/O 使用率を確認する
データおよびログの I/O 使用率を確認するには、次のクエリを使用します。 データまたはログの I/O が 80% を超えている場合、ユーザーがその Azure SQL Database サービス レベルで使用可能な I/O を使用したことを意味します。
SELECT
database_name = DB_NAME()
, UTC_time = end_time
, 'CPU Utilization In % of Limit' = rs.avg_cpu_percent
, 'Data IO In % of Limit' = rs.avg_data_io_percent
, 'Log Write Utilization In % of Limit' = rs.avg_log_write_percent
, 'Memory Usage In % of Limit' = rs.avg_memory_usage_percent
, 'In-Memory OLTP Storage in % of Limit' = rs.xtp_storage_percent
, 'Concurrent Worker Threads in % of Limit' = rs.max_worker_percent
, 'Concurrent Sessions in % of Limit' = rs.max_session_percent
FROM sys.dm_db_resource_stats AS rs --past hour only
ORDER BY rs.end_time DESC;
sys.dm_db_resource_stats を使用する他の例については、この記事で後述する「リソース使用量の監視」セクションを参照してください。
I/O の上限に達した場合、次の 2 つのオプションがあります。
- コンピューティング サイズまたはサービス レベルをアップグレードする
- 最も多くの I/O を消費しているクエリを特定して調整する。
クエリ ストアを使用してバッファー関連の I/O を表示する
オプション 2 の場合、クエリ ストアに対して、バッファー関連の I/O に関する次のクエリを使用して、過去 2 時間の追跡されたアクティビティを表示することができます。
-- Top queries that waited on buffer
-- Note these are finished queries
WITH Aggregated AS (SELECT q.query_hash, SUM(total_query_wait_time_ms) total_wait_time_ms, SUM(total_query_wait_time_ms / avg_query_wait_time_ms) AS total_executions, MIN(qt.query_sql_text) AS sampled_query_text, MIN(wait_category_desc) AS wait_category_desc
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
INNER JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
INNER JOIN sys.query_store_wait_stats AS waits ON waits.plan_id=p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id=waits.runtime_stats_interval_id
WHERE wait_category_desc='Buffer IO' AND rsi.start_time>=DATEADD(HOUR, -2, GETUTCDATE())
GROUP BY q.query_hash), Ordered AS (SELECT query_hash, total_executions, total_wait_time_ms, sampled_query_text, wait_category_desc, ROW_NUMBER() OVER (ORDER BY total_wait_time_ms DESC, query_hash ASC) AS query_hash_row_number
FROM Aggregated)
SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.query_hash_row_number
FROM Ordered AS OD
WHERE OD.query_hash_row_number <= 15 -- get top 15 rows by total_wait_time_ms
ORDER BY total_wait_time_ms DESC;
GO
WRITELOG 待機に関する合計ログ I/O を表示する
待機の種類が WRITELOG の場合、次のクエリを使用して、ステートメントごとに合計ログ I/O を表示します。
-- Top transaction log consumers
-- Adjust the time window by changing
-- rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
WITH AggregatedLogUsed
AS (SELECT q.query_hash,
SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_ms,
SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_ms,
SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used,
MAX(rs.max_cpu_time / 1000.00) AS max_cpu_ms,
MAX(max_logical_io_reads) max_logical_reads,
COUNT(DISTINCT p.plan_id) AS number_of_distinct_plans,
COUNT(DISTINCT p.query_id) AS number_of_distinct_query_ids,
SUM( CASE
WHEN rs.execution_type_desc = 'Aborted' THEN
count_executions
ELSE 0
END
) AS Aborted_Execution_Count,
SUM( CASE
WHEN rs.execution_type_desc = 'Regular' THEN
count_executions
ELSE 0
END
) AS Regular_Execution_Count,
SUM( CASE
WHEN rs.execution_type_desc = 'Exception' THEN
count_executions
ELSE 0
END
) AS Exception_Execution_Count,
SUM(count_executions) AS total_executions,
MIN(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rs.execution_type_desc IN ( 'Regular', 'Aborted', 'Exception' )
AND rsi.start_time >= DATEADD(HOUR, -2, GETUTCDATE())
GROUP BY q.query_hash),
OrderedLogUsed
AS (SELECT query_hash,
total_log_bytes_used,
number_of_distinct_plans,
number_of_distinct_query_ids,
total_executions,
Aborted_Execution_Count,
Regular_Execution_Count,
Exception_Execution_Count,
sampled_query_text,
ROW_NUMBER() OVER (ORDER BY total_log_bytes_used DESC, query_hash ASC) AS query_hash_row_number
FROM AggregatedLogUsed)
SELECT OD.total_log_bytes_used,
OD.number_of_distinct_plans,
OD.number_of_distinct_query_ids,
OD.total_executions,
OD.Aborted_Execution_Count,
OD.Regular_Execution_Count,
OD.Exception_Execution_Count,
OD.sampled_query_text,
OD.query_hash_row_number
FROM OrderedLogUsed AS OD
WHERE OD.query_hash_row_number <= 15 -- get top 15 rows by total_log_bytes_used
ORDER BY total_log_bytes_used DESC;
GO
tempdb のパフォーマンスに関する問題を特定する
I/O パフォーマンスに関する問題を特定する場合、tempdb の問題に関連している待機の種類の上位は PAGELATCH_* です (PAGEIOLATCH_* ではありません)。 ただし、PAGELATCH_* 待機は必ずしも tempdb 競合があることを意味しません。 この待機は、同一のデータ ページを対象とする同時要求が原因で、ユーザーオブジェクト データ ページ競合が発生していることを意味する場合もあります。 tempdb 競合の詳細を確認するには、sys.dm_exec_requests を使用して、wait_resource 値が 2:x:y で始まることを確認します。ここで、2 は tempdb (データベース ID)、x はファイル ID、y はページ ID です。
tempdb 競合では、tempdb が使用されるアプリケーション コードを減らすか、書き直すのが一般的な方法です。 tempdb の一般的な使用領域には以下があります。
- 一時テーブル
- テーブル変数
- テーブル値パラメーター
- バージョン ストアの使用 (実行時間の長いトランザクションに関連)
- 並べ替え、ハッシュ結合、スプールが使用されるクエリ プランがあるクエリ
詳細については、「Azure SQL での tempdb」を参照してください。
テーブル変数と一時テーブルが使用される上位のクエリ
テーブル変数と一時テーブルが使用される上位のクエリを特定するには、次のクエリを使用します。
SELECT plan_handle, execution_count, query_plan
INTO #tmpPlan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle);
GO
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT plan_handle, stmt.stmt_details.value('@Database', 'varchar(max)') AS 'Database'
, stmt.stmt_details.value('@Schema', 'varchar(max)') AS 'Schema'
, stmt.stmt_details.value('@Table', 'varchar(max)') AS 'table'
INTO #tmp2
FROM
(SELECT CAST(query_plan AS XML) sqlplan, plan_handle FROM #tmpPlan) AS p
CROSS APPLY sqlplan.nodes('//sp:Object') AS stmt(stmt_details);
GO
SELECT t.plan_handle, [Database], [Schema], [table], execution_count
FROM
(SELECT DISTINCT plan_handle, [Database], [Schema], [table]
FROM #tmp2
WHERE [table] LIKE '%@%' OR [table] LIKE '%#%') AS t
INNER JOIN #tmpPlan AS t2 ON t.plan_handle=t2.plan_handle;
GO
DROP TABLE #tmpPlan
DROP TABLE #tmp2
実行時間の長いトランザクションの特定
実行時間の長いトランザクションを特定するには、次のクエリを使用します。 実行時間の長いトランザクションは、永続的なバージョン ストア (PVS) のクリーンアップを妨げます。 詳しくは、「高速データベース復旧のトラブルシューティング」をご覧ください。
SELECT DB_NAME(dtr.database_id) 'database_name',
sess.session_id,
atr.name AS 'tran_name',
atr.transaction_id,
transaction_type,
transaction_begin_time,
database_transaction_begin_time,
transaction_state,
is_user_transaction,
sess.open_transaction_count,
TRIM(REPLACE(
REPLACE(
SUBSTRING(
SUBSTRING(
txt.text,
(req.statement_start_offset / 2) + 1,
((CASE req.statement_end_offset
WHEN -1 THEN
DATALENGTH(txt.text)
ELSE
req.statement_end_offset
END - req.statement_start_offset
) / 2
) + 1
),
1,
1000
),
CHAR(10),
' '
),
CHAR(13),
' '
)
) Running_stmt_text,
recenttxt.text 'MostRecentSQLText'
FROM sys.dm_tran_active_transactions AS atr
INNER JOIN sys.dm_tran_database_transactions AS dtr
ON dtr.transaction_id = atr.transaction_id
LEFT JOIN sys.dm_tran_session_transactions AS sess
ON sess.transaction_id = atr.transaction_id
LEFT JOIN sys.dm_exec_requests AS req
ON req.session_id = sess.session_id
AND req.transaction_id = sess.transaction_id
LEFT JOIN sys.dm_exec_connections AS conn
ON sess.session_id = conn.session_id
OUTER APPLY sys.dm_exec_sql_text(req.sql_handle) AS txt
OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle) AS recenttxt
WHERE atr.transaction_type != 2
AND sess.session_id != @@spid
ORDER BY start_time ASC;
メモリ許可待機パフォーマンスに関する問題の特定
上位の待機の種類が RESOURCE_SEMAPHORE であり、なおかつ高 CPU 使用率の問題がない場合は、メモリ許可待機に関する問題が発生している可能性があります。
RESOURCE_SEMAPHORE 待機が上位の待機かどうかを確認します
RESOURCE_SEMAPHORE 待機が上位の待機かどうかを確認するには、次のクエリを使用します。 また、最近の履歴で RESOURCE_SEMAPHORE の待機時間ランクが上昇していることも示されます。 メモリ許可待機に関するトラブルシューティングの詳細については、「SQL Server のメモリ許可によって発生するパフォーマンスの低下またはメモリ不足の問題のトラブルシューティング」を参照してください。
SELECT wait_type,
SUM(wait_time) AS total_wait_time_ms
FROM sys.dm_exec_requests AS req
INNER JOIN sys.dm_exec_sessions AS sess
ON req.session_id = sess.session_id
WHERE is_user_process = 1
GROUP BY wait_type
ORDER BY SUM(wait_time) DESC;
大量のメモリを消費するステートメントの特定
Azure SQL Database でメモリ不足エラーが発生した場合は、sys.dm_os_out_of_memory_events をレビューします。 詳細については、「Azure SQL Database でのメモリ不足エラーのトラブルシューティング」を参照してください。
最初に、次のスクリプトを変更して、start_time と end_time の関連する値を更新します。 次に、次のクエリを実行して、大量のメモリを消費するステートメントを特定します。
SELECT IDENTITY(INT, 1, 1) rowId,
CAST(query_plan AS XML) query_plan,
p.query_id
INTO #tmp
FROM sys.query_store_plan AS p
INNER JOIN sys.query_store_runtime_stats AS r
ON p.plan_id = r.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS i
ON r.runtime_stats_interval_id = i.runtime_stats_interval_id
WHERE start_time > '2018-10-11 14:00:00.0000000'
AND end_time < '2018-10-17 20:00:00.0000000';
GO
;WITH cte
AS (SELECT query_id,
query_plan,
m.c.value('@SerialDesiredMemory', 'INT') AS SerialDesiredMemory
FROM #tmp AS t
CROSS APPLY t.query_plan.nodes('//*:MemoryGrantInfo[@SerialDesiredMemory[. > 0]]') AS m(c) )
SELECT TOP 50
cte.query_id,
t.query_sql_text,
cte.query_plan,
CAST(SerialDesiredMemory / 1024. AS DECIMAL(10, 2)) SerialDesiredMemory_MB
FROM cte
INNER JOIN sys.query_store_query AS q
ON cte.query_id = q.query_id
INNER JOIN sys.query_store_query_text AS t
ON q.query_text_id = t.query_text_id
ORDER BY SerialDesiredMemory DESC;
上位 10 のアクティブなメモリ許可の特定
上位 10 のアクティブなメモリ許可を特定するには、次のクエリを実行します。
SELECT TOP 10
CONVERT(VARCHAR(30), GETDATE(), 121) AS runtime,
r.session_id,
r.blocking_session_id,
r.cpu_time,
r.total_elapsed_time,
r.reads,
r.writes,
r.logical_reads,
r.row_count,
wait_time,
wait_type,
r.command,
OBJECT_NAME(txt.objectid, txt.dbid) 'Object_Name',
TRIM(REPLACE(REPLACE(SUBSTRING(SUBSTRING(TEXT, (r.statement_start_offset / 2) + 1,
( (
CASE r.statement_end_offset
WHEN - 1
THEN DATALENGTH(TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1), 1, 1000), CHAR(10), ' '), CHAR(13), ' ')) AS stmt_text,
mg.dop, --Degree of parallelism
mg.request_time, --Date and time when this query requested the memory grant.
mg.grant_time, --NULL means memory has not been granted
mg.requested_memory_kb / 1024.0 requested_memory_mb, --Total requested amount of memory in megabytes
mg.granted_memory_kb / 1024.0 AS granted_memory_mb, --Total amount of memory actually granted in megabytes. NULL if not granted
mg.required_memory_kb / 1024.0 AS required_memory_mb, --Minimum memory required to run this query in megabytes.
max_used_memory_kb / 1024.0 AS max_used_memory_mb,
mg.query_cost, --Estimated query cost.
mg.timeout_sec, --Time-out in seconds before this query gives up the memory grant request.
mg.resource_semaphore_id, --Non-unique ID of the resource semaphore on which this query is waiting.
mg.wait_time_ms, --Wait time in milliseconds. NULL if the memory is already granted.
CASE mg.is_next_candidate --Is this process the next candidate for a memory grant
WHEN 1 THEN
'Yes'
WHEN 0 THEN
'No'
ELSE
'Memory has been granted'
END AS 'Next Candidate for Memory Grant',
qp.query_plan
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_query_memory_grants AS mg
ON r.session_id = mg.session_id
AND r.request_id = mg.request_id
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS txt
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY mg.granted_memory_kb DESC;
接続を監視する
sys.dm_exec_connections ビューを使用して、特定のデータベースまたはエラスティック プールに対して確立されている接続についての情報と、各接続の詳細を取得できます。 また、sys.dm_exec_sessions ビューは、すべてのアクティブなユーザー接続と内部タスクについての情報を取得する場合に役立ちます。
現在のセッションを表示する
次のクエリは、現在の接続に関する情報を取得します。 すべてのセッションを表示するには、WHERE 句を削除します。
sys.dm_exec_requests ビューと sys.dm_exec_sessions ビューを実行するときに、データベースに対する VIEW DATABASE STATE アクセス許可を持っている場合にのみ、データベース上の実行中のすべてのセッションが表示されます。 それ以外の場合は、現在のセッションのみが表示されます。
SELECT
c.session_id, c.net_transport, c.encrypt_option,
c.auth_scheme, s.host_name, s.program_name,
s.client_interface_name, s.login_name, s.nt_domain,
s.nt_user_name, s.original_login_name, c.connect_time,
s.login_time
FROM sys.dm_exec_connections AS c
INNER JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE c.session_id = @@SPID; --Remove to view all sessions, if permissions allow
リソース使用量の監視
Azure portal またはクエリ ストアの SQL Database Query Performance Insight を使用して、Azure SQL Database リソースの使用状況をクエリ レベルで監視できます。
また、次のビューを使用して使用量を監視することもできます。
sys.dm_db_resource_stats
sys.dm_db_resource_stats ビューは、すべてのデータベースで使用できます。 sys.dm_db_resource_stats ビューには、サービス レベルに関連した最近のリソース使用率データが表示されます。 CPU、データ I/O、ログ書き込み、メモリの平均 (%) が 15 秒ごとに記録され、1 時間保持されます。
このビューにはリソース使用率が詳細に表示されるので、現状の分析やトラブルシューティングが目的の場合、最初に sys.dm_db_resource_stats を使用してください。 たとえば次のクエリは、現在のデータベースの過去 1 時間の平均リソース使用率と最大リソース使用率を表示します。
SELECT
Database_Name = DB_NAME(),
tier_limit = COALESCE(rs.dtu_limit, cpu_limit), --DTU or vCore limit
AVG(avg_cpu_percent) AS 'Average CPU use in percent',
MAX(avg_cpu_percent) AS 'Maximum CPU use in percent',
AVG(avg_data_io_percent) AS 'Average data IO in percent',
MAX(avg_data_io_percent) AS 'Maximum data IO in percent',
AVG(avg_log_write_percent) AS 'Average log write use in percent',
MAX(avg_log_write_percent) AS 'Maximum log write use in percent',
AVG(avg_memory_usage_percent) AS 'Average memory use in percent',
MAX(avg_memory_usage_percent) AS 'Maximum memory use in percent'
FROM sys.dm_db_resource_stats AS rs --past hour only
GROUP BY rs.dtu_limit, rs.cpu_limit;
その他のクエリについては、sys.dm_db_resource_stats の例を参照してください。
sys.resource_stats
master データベースの sys.resource_stats ビューには、特定のサービス レベルとコンピューティング サイズでのデータベースのパフォーマンス監視に役立つ追加情報が含まれます。 データは 5 分ごとに集められ、約 14 日間保持されます。 このビューは、過去にデータベースでリソースがどのように使用されたかを長期にわたり分析する場合に役立ちます。
次のグラフは、Premium データベースの CPU リソース使用率を示しています (P2 コンピューティング サイズ、1 週間における毎時間の使用率)。 このグラフは月曜日から始まります。5 営業日が経過した後の週末ではアプリケーションの活動が大幅に減っていることがわかります。
このデータから、このデータベースのピーク CPU 負荷は現在のところ、P2 コンピューティング サイズに対して 50% をわずかに超える CPU 利用率になっていることがわかります (火曜日の昼)。 アプリケーションのリソース プロファイルにおいて CPU が支配的要因である場合、P2 がワークロードに常に対処できる最適なコンピューティング サイズであると決定できます。 時間の経過と共にアプリケーションの規模が大きくなると予測される場合、アプリケーションがパフォーマンス レベルの制限に達しないように、リソース バッファーを余分に確保しておくことをお勧めします。 コンピューティング サイズを上げると、要求を効果的に処理するための十分な能力がないデータベースで発生するおそれのある、ユーザーに見えるエラーを回避できます。これは特に待機時間が重要な環境に当てはまります。 たとえば、データベース呼び出しの結果に基づいて Web ページを描画するアプリケーションをサポートしているデータベースなどです。
アプリケーションの種類が異なれば、同じグラフの解釈が異なる場合があります。 たとえば、あるアプリケーションで給与データを毎日処理し、同じグラフを生成する場合、P1 コンピューティング サイズでこの種の "一括ジョブ" モデルに問題なく対応できる可能性があります。 P2 コンピューティング サイズが 200 DTU であるのに対して、P1 コンピューティング サイズは 100 DTU です。 P1 コンピューティング サイズのパフォーマンスは、P2 コンピューティング サイズの半分です。 このため、P2 における 50% の CPU 使用率は、P1 における 100% の CPU 使用率に相当します。 アプリケーションにタイムアウトがない場合、当日に完了するのであれば、ジョブに 2 時間かかっても 2.5 時間かかっても問題ないものと思われます。 このカテゴリのアプリケーションではおそらく、P1 コンピューティング サイズを利用できます。 1 日の中にはリソース使用率が低くなる時間帯があるという事実を利用できます。このため、"大きなピーク" が 1 日のそのような時間帯のいずれかに波及することがあります。 ジョブを毎日定刻に完了できる限り、P1 コンピューティング サイズがこの種のアプリケーションに適している (コストが削減される) 場合があります。
データベース エンジンでは、各アクティブ データベースの使用済みリソース情報が、各サーバーの master データベースの sys.resource_stats ビューで公開されます。 テーブルのデータは 5 分おきに集計されます。 Basic、Standard、Premium のサービス レベルでは、データをテーブルに表示するのに 5 分を超える時間がかかる可能性があります。このため、このデータはほぼリアルタイムの分析よりも過去の分析に役に立ちます。 sys.resource_stats ビューに照会すると、データベースの最近の履歴が表示され、選択した予約で必要なときに望ましいパフォーマンスが発揮されたかどうかを検証できます。
Note
Azure SQL Database で、次の例の sys.resource_stats にクエリを実行するには、master データベースに接続する必要があります。
次の例は、このビューのデータが表示されているところです。
SELECT TOP 10 *
FROM sys.resource_stats
WHERE database_name = 'userdb1'
ORDER BY start_time DESC;
次の例では、sys.resource_stats カタログ ビューを使用して、データベースでのリソースの使用状況に関する情報を取得するさまざまな方法を示します。
ユーザー データベース
userdb1の過去 1 週間のリソース使用量を確認するには、独自のデータベース名に置き換えて、次のクエリを実行します。SELECT * FROM sys.resource_stats WHERE database_name = 'userdb1' AND start_time > DATEADD(day, -7, GETDATE()) ORDER BY start_time DESC;ワークロードがコンピューティング サイズにどの程度適合しているかを評価するには、リソース メトリックの各側面を分析する必要があります。つまり、CPU、読み取り、書き込み、ワーカー数、セッション数です。
sys.resource_statsを使用して、データベースがプロビジョニングされたサービス レベルごとに、これらのリソース メトリックの平均値と最大値をレポートするように変更されたクエリを次に示します。SELECT rs.database_name , rs.sku , storage_mb = MAX(rs.Storage_in_megabytes) , 'Average CPU Utilization In %' = AVG(rs.avg_cpu_percent) , 'Maximum CPU Utilization In %' = MAX(rs.avg_cpu_percent) , 'Average Data IO In %' = AVG(rs.avg_data_io_percent) , 'Maximum Data IO In %' = MAX(rs.avg_data_io_percent) , 'Average Log Write Utilization In %' = AVG(rs.avg_log_write_percent) , 'Maximum Log Write Utilization In %' = MAX(rs.avg_log_write_percent) , 'Average Requests In %' = AVG(rs.max_worker_percent) , 'Maximum Requests In %' = MAX(rs.max_worker_percent) , 'Average Sessions In %' = AVG(rs.max_session_percent) , 'Maximum Sessions In %' = MAX(rs.max_session_percent) FROM sys.resource_stats AS rs WHERE rs.database_name = 'userdb1' AND rs.start_time > DATEADD(day, -7, GETDATE()) GROUP BY rs.database_name, rs.sku;各リソース メトリックの平均値と最大値に関するこの情報に基づいて、選択したコンピューティング サイズにワークロードが適合しているかどうかを評価できます。 通常、
sys.resource_statsからの平均値が目標サイズに対する有効な基準となります。 これを主要なものさしとしてください。DTU 購入モデルのデータベースの場合:
たとえば、S2 コンピューティング サイズで Standard サービス レベルを使用しているとします。 CPU と I/O の読み取り/書き込みの平均使用率が 40% を下回り、ワーカーの平均数が 50 を下回り、セッションの平均数が 200 を下回っています。 このワークロードには、S1 コンピューティング サイズが適している可能性があります。 データベースがワーカーとセッションの制限内に収まるかどうかは簡単にわかります。 データベースが下位のコンピューティング サイズに適合するかどうかを確認するには、下位コンピューティング サイズの DTU 数を現在のコンピューティング サイズの DTU 数で割り、その計算結果に 100 を掛けます。
S1 DTU / S2 DTU * 100 = 20 / 50 * 100 = 40この結果は、2 つのコンピューティング サイズの間の相対的パフォーマンス差異を百分率で表したものになります。 リソースの使用がこの量を超えていない場合、ワークロードは下位のコンピューティング サイズに適合する可能性があります。 ただし、リソース使用率の値を全範囲で見て、どのくらいの頻度でデータベースのワークロードが下位のコンピューティング サイズに適合するかを割合の観点から判断する必要があります。 次のクエリは、この例で計算した 40% のしきい値に基づき、リソース ディメンション別の適合率を出力します。
SELECT database_name, 100*((COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'CPU Fit Percent', 100*((COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Log Write Fit Percent', 100*((COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Physical Data IO Fit Percent' FROM sys.resource_stats WHERE start_time > DATEADD(day, -7, GETDATE()) AND database_name = 'sample' --remove to see all databases GROUP BY database_name;データベースのサービス レベルに基づき、ワークロードが下位のコンピューティング サイズに適合するかどうかを判断できます。 データベース ワークロード目標が 99.9% で、上記のクエリが 3 つすべてのリソース ディメンションに対して 99.9% を超える値を返す場合、そのワークロードはおそらく下位のコンピューティング サイズに適合します。
適合率を見ると、目標を満たすために上位のコンピューティング サイズに移行する必要があるかどうかもわかります。 たとえば、過去 1 週間のサンプル データベースの CPU 使用率は次のようになります。
平均 CPU 使用率 (%) 最大 CPU 使用率 (%) 24.5 100.00 平均 CPU は、コンピューティング サイズの制限の約 4 分の 1 であり、データベースのコンピューティング サイズにうまく適合するでしょう。
DTU 購入モデルおよび 仮想コア購入モデルのデータベースの場合:
最大値は、データベースがコンピューティング サイズの上限に達することを示します。 次に上位のコンピューティング サイズに移動する必要がありますか。 ワークロードが 100% に到達する回数を確認し、それをデータベースのワークロード目標と比較します。
SELECT database_name, 100*((COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'CPU Fit Percent', 100*((COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Log Write Fit Percent', 100*((COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name)) AS 'Physical Data IO Fit Percent' FROM sys.resource_stats WHERE start_time > DATEADD(day, -7, GETDATE()) AND database_name = 'sample' --remove to see all databases GROUP BY database_name;これらの割合は、ワークロードが現在のコンピューティング サイズ "以下" に収まるサンプルの数です。 このクエリが 3 つのリソース ディメンションのいずれかについて 99.9% 未満の値を返す場合、サンプリングされた平均ワークロードは制限を超えました。 次に高いコンピューティング サイズに移行するか、アプリケーションの調整手法を使用してデータベースの負荷を軽減することを検討してください。
Note
エラスティック プールでは、このセクションで説明した手法を使用して、プール内の個々のデータベースを監視できます。 また、プール全体を監視することもできます。 詳細については、エラスティック プールの監視と管理に関する記事を参照してください。
同時要求の最大数
現在の同時要求数を確認するには、ユーザー データベースで次のクエリを実行します。
SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests AS R;
データベースのワークロードを分析するには、分析したい特定のデータベースでフィルター処理するようにこのクエリを変更します。 最初に、データベースの名前を MyDatabase から目的のデータベースに更新し、次のクエリを実行して、そのデータベース内の同時要求数を見つけます。
SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests AS R
INNER JOIN sys.databases AS D
ON D.database_id = R.database_id
AND D.name = 'MyDatabase';
これはある時点のスナップショットにすぎません。 ワークロードと同時要求の要件をさらに詳しく理解するには、時間をかけて多くのサンプルを収集する必要があります。
同時ログイン イベントの最大数
ユーザーやアプリケーションのパターンを分析すると、ログイン イベントの頻度を把握できます。 さらに、テスト環境で実際の負荷を実行し、この上限やこの記事で説明されている他の制限に達していないことを確認できます。 同時ログイン数または履歴を表示できる単一のクエリや動的管理ビュー (DMV) はありません。
複数のクライアントで同じ接続文字列を使用している場合、サービスによってそれぞれのログインが認証されます。 10 人のユーザーが同じユーザー名とパスワードを使ってデータベースに同時に接続した場合、10 件の同時ログインが発生します。 この制限は、ログインと認証の期間のみに適用されます。 同じ 10 人のユーザーがデータベースに順番に接続した場合、同時ログイン数が 1 より大きくなることはありません。
Note
この制限は現在のところ、エラスティック プールのデータベースには適用されません。
セッションの最大数
現在のアクティブ セッション数を確認するには、データベースで次のクエリを実行します。
SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections;
SQL Server のワークロードを分析する場合は、特定のデータベースが対象になるようにクエリを変更してください。 このクエリは、Azure への移行を検討している場合に、データベースの考えられるセッションのニーズを判断するのに役立ちます。 最初に、データベースの名前を MyDatabase から目的のデータベースに更新し、次のクエリを実行します。
SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections AS C
INNER JOIN sys.dm_exec_sessions AS S
ON (S.session_id = C.session_id)
INNER JOIN sys.databases AS D
ON (D.database_id = S.database_id)
WHERE D.name = 'MyDatabase';
ここでも、これらのクエリはある時点の数を返します。 時間をかけて複数のサンプルを集めると、セッションの使用状況を正確に把握できます。
sys.resource_stats カタログ ビューにクエリを実行し、active_session_count 列を確認して、セッションに関する過去の統計値を取得できます。
データベースとオブジェクトのサイズを計算する
次のクエリは、データベースのサイズ (MB 単位) を返します。
-- Calculates the size of the database.
SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 AS size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';
次のクエリは、データベース内の個々のオブジェクトのサイズ (MB 単位) を返します。
-- Calculates the size of individual database objects.
SELECT o.name, SUM(ps.reserved_page_count) * 8.0 / 1024 AS size_mb
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.objects AS o
ON ps.object_id = o.object_id
GROUP BY o.name
ORDER BY size_mb DESC;
クエリ パフォーマンスを監視する
クエリが低速または実行時間が長いと、大量のシステム リソースが消費される可能性があります。 このセクションでは、動的管理ビュー (sys.dm_exec_query_stats 動的管理ビュー) を使用して、いくつかの一般的なクエリ パフォーマンスの問題を検出する方法について説明します。 このビューには、キャッシュされたプラン内のクエリ ステートメントごとに 1 行が含まれており、その行の有効期間はプラン自体に関連付けられています。 つまり、プランがキャッシュから削除されると、対応する行もこのビューから削除されます。
CPU 時間別に上位のクエリを検出する
次の例では、実行あたりの平均 CPU 時間の上位 15 個のクエリに関する情報を返します。 この例では、論理的に等価なクエリがリソースの累計消費量ごとにグループ化されるように、クエリ ハッシュに応じてクエリを集計します。
SELECT TOP 15 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST
) AS query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
累積 CPU 時間についてクエリ プランを監視する
クエリ プランの効率が悪いと、CPU の消費量が増える可能性があります。 次の例では、最近の履歴で累積 CPU 時間が最も多いクエリを特定します。
SELECT
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
FROM
(SELECT TOP 15
qs.plan_handle,
qs.total_worker_time
FROM
sys.dm_exec_query_stats AS qs
ORDER BY qs.total_worker_time desc
) AS highest_cpu_queries
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC;
クエリのブロックを監視する
クエリが低速または実行時間が長いと、大量のリソースが消費され、結果としてクエリがブロックされる可能性があります。 ブロックの原因には、不適切なアプリケーション設計、不適切なクエリ プラン、有効なインデックスの欠如などがあります。
sys.dm_tran_locks ビューを使用すると、データベース内の現在ロックされているアクティビティに関する情報を取得できます。 サンプル コードについては、「sys.dm_tran_locks」を参照してください。 ブロッキングのトラブルシューティングの詳細については、Azure SQL のブロックの問題の概要と解決策に関するページを参照してください。
デッドロックを監視する
場合によっては、2 つ以上のクエリが相互にブロックされ、デッドロックが発生することがあります。
Azure SQL Database 内のデータベースをトレースする拡張イベントを作成してデッドロック イベントをキャプチャし、クエリ ストアで関連するクエリとその実行プランを探すことができます。 詳細については、ラボ「AdventureWorksLT でデッドロックが発生する」を含め、「Azure SQL Database でデッドロックを分析および防止する」を参照してください。 デッドロックが発生する可能性があるリソースの種類について詳しくは、こちらを参照してください。
関連するコンテンツ
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示