동적 관리 뷰를 사용하여 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;
Azure의 논리 서버에 대한 로그인에 ##MS_ServerStateReader## 서버 역할에 대한 멤버 자격을 부여하려면 master 데이터베이스에 연결한 다음, 다음 쿼리(예제)를 실행합니다.
ALTER SERVER ROLE [##MS_ServerStateReader##] ADD MEMBER [login];
SQL Server 및 Azure SQL Managed Instance의 인스턴스에서 동적 관리 뷰는 서버 상태 정보를 반환합니다. Azure SQL Database에서는 현재 논리 데이터베이스에 대한 정보만 반환합니다.
CPU 성능 문제 식별
CPU 사용량이 장시간 80% 이상인 경우 CPU 문제가 현재 발생 중인지 또는 과거에 발생했는지에 관계없이 다음 문제 해결 단계를 고려하세요.
CPU 문제가 현재 발생 중인 경우
지금 문제가 발생하는 경우 다음과 같은 두 가지 시나리오가 있을 수 있습니다.
점증적으로 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 제한에 도달하는 경우 두 가지 옵션이 있습니다.
- 컴퓨팅 크기 또는 서비스 계층 업그레이드
- 가장 많은 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 절을 제거합니다.
데이터베이스에 대한 VIEW DATABASE STATE 권한이 있는 경우에만 sys.dm_exec_requests 및 sys.dm_exec_sessions 보기를 실행할 때 데이터베이스에서 실행 중인 모든 세션을 볼 수 있습니다. 그렇지 않으면 현재 세션만 볼 수 있습니다.
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 데이터베이스 리소스 사용량을 모니터링할 수 있습니다.
다음 뷰를 사용하여 사용량을 모니터링할 수도 있습니다.
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일 동안 유지됩니다. 이 뷰는 데이터베이스가 리소스를 사용하는 방식에 대한 장기적인 기록 분석에 유용합니다.
다음 그래프는 P2 컴퓨팅 크기를 가진 프리미엄 데이터베이스의 CPU 리소스 사용량을 일주일 동안 시간대별로 보여줍니다. 이 그래프는 월요일부터 5일의 근무일과 애플리케이션 사용량이 훨씬 적은 주말까지 표시되어 있습니다.
데이터에 따르면 이 데이터베이스는 현재 P2 컴퓨팅 크기에 비해 CPU 사용량이 50%를 약간 넘는 최대 CPU 부하가 화요일 정오에 있습니다. 애플리케이션의 리소스 프로필에서 CPU가 가장 지배적인 요인인 경우 항상 워크로드를 충족하는 데 적합한 컴퓨팅 크기는 P2임을 판단할 수 있습니다. 애플리케이션이 시간이 지남에 따라 증가할 것으로 예상되는 경우 애플리케이션이 성능 수준 제한에 도달하지 않도록 추가 리소스 버퍼를 사용하는 것이 좋습니다. 컴퓨팅 크기를 늘리면 특히 대기 시간이 중요한 환경에서 데이터베이스가 요청을 효과적으로 처리하므로 충분한 능력을 갖고 있지 않은 경우 발생할 수 있는 고객에게 보이는 오류를 방지하는 데 도움이 될 수 있습니다. 데이터베이스 호출 결과에 따라 웹 페이지를 그리는 애플리케이션을 지원하는 데이터베이스를 예로 들 수 있습니다.
다른 애플리케이션 유형은 동일한 그래프를 다르게 해석할 수 있습니다. 예를 들어 애플리케이션이 매일 급여 데이터를 처리하려고 하고 차트가 동일하다면, 이러한 종류의 "일괄 작업" 모델은 P1 컴퓨팅 크기에서 잘 작동할 수 있습니다. P1 컴퓨팅 크기는 100 DTU이고, 이에 반해 P2 컴퓨팅 크기는 200 DTU입니다. P1 컴퓨팅 크기는 P2 컴퓨팅 크기의 절반 성능을 제공합니다. 따라서 P2에서의 CPU 사용량 50%는 P1에서의 CPU 사용량 100%와 같습니다. 애플리케이션에 시간 제한이 없는 경우, 작업이 오늘 안에 완료된다면 완료되는 데 2시간이 걸리든 2.5시간이 걸리든 중요하지 않을 수 있습니다. 이 범주에 속하는 애플리케이션은 아마도 P1 컴퓨팅 크기를 사용할 수 있을 것입니다. 하루 중 리소스 사용량이 낮은 시간대가 있다는 사실을 활용할 수 있습니다. 즉, "최고" 시간대의 작업을 하루 중 사용량이 낮은 시간대 중 하나로 나눌 수 있습니다. 작업이 매일 제 시간에 완료될 수 있다면 P1 컴퓨팅 크기는 이러한 종류의 애플리케이션에 적합할 수 있으며 비용을 절감할 수 있습니다.
데이터베이스 엔진은 각 서버의 master 데이터베이스의 sys.resource_stats 뷰에서 각 활성 데이터베이스에 대해 사용된 리소스 정보를 노출합니다. 테이블의 데이터는 5분 간격으로 집계됩니다. 기본, 표준, 프리미엄 서비스 계층의 경우 데이터가 테이블에 표시되는 데 5분 이상 걸릴 수 있으므로 이 데이터는 실시간에 가까운 분석보다는 기록 분석에 더 유용합니다. sys.resource_stats 뷰를 쿼리하여 데이터베이스의 최근 기록을 확인하고 필요할 때 선택한 예약이 원하는 성능을 제공했는지 여부를 검증합니다.
참고 항목
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의 지난 주 리소스 사용량을 확인하고자 할 때 이 쿼리를 실행하여 자신의 데이터베이스 이름으로 대체할 수 있습니다.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 컴퓨팅 크기와 함께 표준 서비스 계층을 사용할 수 있습니다. CPU와 I/O 읽기 및 쓰기의 평균 사용 백분율은 40% 미만, 평균 작업자 수는 50명 미만, 평균 세션 수는 200개 미만입니다. 워크로드가 S1 컴퓨팅 크기에 적합할 수 있습니다. 데이터베이스가 작업자 및 세션 제한에 적합한지 여부는 쉽게 확인할 수 있습니다. 데이터베이스가 더 낮은 컴퓨팅 크기에 적합한지 확인하려면 더 낮은 컴퓨팅 크기의 DTU 수를 현재 컴퓨팅 크기의 DTU 수로 나눈 다음 그 결과에 100을 곱합니다.
S1 DTU / S2 DTU * 100 = 20 / 50 * 100 = 40결과는 두 컴퓨팅 크기 간의 상대적 성능 차이를 백분율로 나타낸 값입니다. 리소스 사용량이 이 양을 초과하지 않으면 워크로드가 더 낮은 컴퓨팅 크기에 적합할 수 있습니다. 그러나 리소스 사용량 값의 모든 범위를 살펴보고 데이터베이스 워크로드가 더 낮은 컴퓨팅 크기에 얼마나 자주 적합한지 백분율로 결정해야 합니다. 다음 쿼리는 이 예에서 계산된 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%이고 앞의 쿼리가 세 가지 리소스 차원에 대해 99.9%보다 큰 값을 반환할 경우 워크로드는 더 낮은 단계의 컴퓨팅 크기에 적합할 가능성이 높습니다.
적합률을 살펴보면 목표를 충족하기 위해 더 높은 단계의 컴퓨팅 크기로 이동해야 하는지 여부를 알 수 있습니다. 예를 들어 지난 한 주 동안의 샘플 데이터베이스에 대한 CPU 사용량은 다음과 같습니다.
평균 CPU 비율 최대 CPU 비율 24.5 100.00 평균 CPU는 컴퓨팅 크기 한도의 약 4분의 1이며 데이터베이스의 컴퓨팅 크기에 적합합니다.
DTU 구매 모델 및 vCore 구매 모델 데이터베이스의 경우:
최댓값은 데이터베이스가 컴퓨팅 크기 한도에 도달했음을 나타냅니다. 더 높은 단계의 컴퓨팅 크기로 전환해야 하나요? 워크로드가 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;이러한 백분율은 워크로드가 현재 컴퓨팅 크기에서 맞는 샘플의 수입니다. 이 쿼리가 세 가지 리소스 차원 중 하나라도 99.9% 미만의 값을 반환하는 경우 샘플링된 평균 워크로드가 한도를 초과한 것입니다. 한 단계 높은 컴퓨팅 크기로 전환하거나 애플리케이션 튜닝 기술을 사용하여 데이터베이스의 부하를 줄이는 것을 고려하세요.
참고 항목
탄력적 풀의 경우 이 섹션에 설명된 기술을 사용하여 풀의 개별 데이터베이스를 모니터링할 수 있습니다. 풀 전체를 모니터링할 수도 있습니다. 자세한 내용은 탄력적 풀 모니터링 및 관리를 참조하세요.
최대 동시 요청 수
현재 동시 요청 수를 보려면 사용자 데이터베이스에서 이 쿼리를 실행하세요.
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을 초과할 수 없습니다.
참고 항목
현재 이 제한은 탄력적 풀의 데이터베이스에는 적용되지 않습니다.
최대 세션 수
현재 활성 세션 수를 보려면 데이터베이스에서 이 쿼리를 실행하세요.
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 동적 관리 뷰를 사용하여 동적 관리 뷰를 통해 몇 가지 일반적인 쿼리 성능 문제를 감지하는 방법을 보여 줍니다. 이 뷰에는 캐시된 계획 내에 쿼리 문당 하나의 행이 포함되며 행의 수명은 계획 자체에 연결되어 있습니다. 캐시에서 계획이 제거되면 뷰에서도 해당 행이 제거됩니다.
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 차단 문제 이해 및 해결을 참조하세요.
교착 상태 모니터링
경우에 따라 둘 이상의 쿼리가 서로 차단되어 교착 상태가 발생할 수 있습니다.
Azure SQL Database에서 확장 이벤트 추적 데이터베이스를 만들어 교착 상태 이벤트를 캡처한 다음, 쿼리 저장소에서 관련 쿼리 및 해당 실행 계획을 찾을 수 있습니다. 자세한 내용은 AdventureWorksLT에서 교착 상태 발생 랩을 포함하여 Azure SQL Database의 교착 상태 분석 및 방지를 참조하세요. 교착 상태에 빠질 수 있는 리소스에서 해당 유형에 대해 자세히 알아보세요.
관련 콘텐츠
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기