동적 관리 뷰를 사용하여 Microsoft Azure SQL Managed Instance 성능 모니터링
적용 대상: 
Azure SQL Managed Instance
Microsoft Azure SQL Managed Instance를 사용하여 차단되거나 오래 실행된 쿼리, 리소스 병목 현상, 잘못된 쿼리 계획 등에 의해 야기될 수 있는 DMV(동적 관리 뷰)의 하위 집합에서 성능 문제를 진단할 수 있습니다. 이 문서에서는 동적 관리 뷰를 사용하여 일반적인 성능 문제를 검색하는 방법에 대한 정보를 제공합니다.
이 문서에서는 Azure SQL Managed Instance를 다룹니다. 동적 관리 뷰를 사용한 Microsoft Azure SQL Database 성능 모니터링도 참조하세요.
사용 권한
Azure SQL Managed Instance에서 동적 관리 뷰를 쿼리하려면 VIEW SERVER STATE 권한이 필요합니다.
GRANT VIEW SERVER STATE TO database_user;
SQL Server 및 Azure SQL Managed Instance의 인스턴스에서 동적 관리 뷰는 서버 상태 정보를 반환합니다.
CPU 성능 문제 식별
CPU 사용량이 장시간 80% 이상인 경우 다음 문제 해결 단계를 고려합니다.
지금 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 id if not parameterized or not parameterized properly
-- it grabs a sample query text by min
WITH AggregatedCPU AS (SELECT q.query_hash, SUM(count_executions * avg_cpu_time / 1000.0) AS total_cpu_millisec, SUM(count_executions * avg_cpu_time / 1000.0)/ SUM(count_executions) AS avg_cpu_millisec, MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec, 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
JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id=p.plan_id
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_millisec, avg_cpu_millisec, max_cpu_millisec, 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_millisec DESC, query_hash ASC) AS RN
FROM AggregatedCPU)
SELECT OD.query_hash, OD.total_cpu_millisec, OD.avg_cpu_millisec, OD.max_cpu_millisec, 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.RN
FROM OrderedCPU AS OD
WHERE OD.RN<=15
ORDER BY total_cpu_millisec DESC;
문제가 있는 쿼리를 파악한 후에는 CPU 사용률을 줄이기 위해 해당 쿼리를 튜닝해야 합니다. 쿼리를 튜닝할 시간이 없는 경우 관리되는 인스턴스의 SLO를 업그레이드하여 문제를 해결할 수도 있습니다.
IO 성능 문제 식별
IO 성능 문제를 식별할 때 IO 문제와 관련된 상위 대기 유형은 다음과 같습니다.
PAGEIOLATCH_*데이터 파일 IO 문제인 경우(
PAGEIOLATCH_SH,PAGEIOLATCH_EX,PAGEIOLATCH_UP포함) 대기 유형 이름에 IO가 있으면 IO 문제를 가리키는 것입니다. 페이지 래치 대기 이름에 IO가 없으면 다른 유형의 문제(예:tempdb경합)를 가리키는 것입니다.WRITE_LOG트랜잭션 로그 IO 문제인 경우
지금 IO 문제가 발생하고 있는 경우
sys.dm_exec_requests 또는 sys.dm_os_waiting_tasks를 사용하여 wait_type 및 wait_time을 확인합니다.
쿼리 저장소를 사용하여 버퍼 관련 IO 확인
옵션 2의 경우 버퍼 관련 IO에 대한 쿼리 저장소에 다음 쿼리를 사용하여 마지막 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
JOIN sys.query_store_query AS q ON qt.query_text_id=q.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id=p.query_id
JOIN sys.query_store_wait_stats AS waits ON waits.plan_id=p.plan_id
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 RN
FROM Aggregated)
SELECT OD.query_hash, OD.total_executions, OD.total_wait_time_ms, OD.sampled_query_text, OD.wait_category_desc, OD.RN
FROM Ordered AS OD
WHERE OD.RN<=15
ORDER BY total_wait_time_ms DESC;
GO
WRITELOG 대기에 대한 총 로그 IO 보기
대기 유형이 WRITELOG면 다음 쿼리를 사용하여 총 로그 IO를 문별로 봅니다.
-- 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_millisec,
SUM(count_executions * avg_cpu_time / 1000.0) / SUM(count_executions) AS avg_cpu_millisec,
SUM(count_executions * avg_log_bytes_used) AS total_log_bytes_used,
MAX(rs.max_cpu_time / 1000.00) AS max_cpu_millisec,
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
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
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 RN
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.RN
FROM OrderedLogUsed AS OD
WHERE OD.RN <= 15
ORDER BY total_log_bytes_used DESC;
GO
tempdb 성능 문제 식별
IO 성능 문제를 식별할 때 tempdb 문제와 관련된 상위 대기 유형은 PAGELATCH_*입니다(PAGEIOLATCH_* 아님). 그러나 PAGELATCH_* 대기는 항상 tempdb 경합이 있다는 의미가 아닙니다. 이 대기는 동일한 데이터 페이지를 대상으로 하는 동시 요청으로 인해 사용자 개체 데이터 페이지 경합이 있다는 의미일 수도 있습니다. tempdb 경합을 추가로 확인하려면 sys.dm_exec_requests를 사용하여 2:x:y로 시작하는 wait_resource 값을 확인합니다. 여기서 2는 tempdb 데이터베이스 ID, x는 파일 ID, y는 페이지 ID입니다.
tempdb 경합의 경우 tempdb를 사용하는 애플리케이션 코드를 줄이거나 다시 쓰는 것이 일반적인 방법입니다. 일반적인 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)') 'Database', stmt.stmt_details.value('@Schema', 'varchar(max)') 'Schema', stmt.stmt_details.value('@Table', 'varchar(max)') '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
JOIN #tmpPlan AS t2 ON t.plan_handle=t2.plan_handle;
장기 실행 트랜잭션 식별
다음 쿼리를 사용하여 장기 실행 트랜잭션을 식별합니다. 장기 실행 트랜잭션은 버전 저장소 정리를 방지합니다.
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_SEMAHPORE이고 높은 CPU 사용량 문제가 없는 경우 메모리 부여 대기 문제일 가능성이 있습니다.
RESOURCE_SEMAHPORE 대기가 최상위 대기인지 확인
다음 쿼리를 사용하여 RESOURCE_SEMAHPORE 대기가 최상위 대기인지 확인
SELECT wait_type,
SUM(wait_time) AS total_wait_time_ms
FROM sys.dm_exec_requests AS req
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;
메모리를 많이 사용하는 문 식별
메모리 부족 오류가 발생하면 sys.dm_os_out_of_memory_events를 검토합니다.
다음 쿼리를 사용하여 높은 메모리 사용 문을 식별합니다.
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
JOIN sys.query_store_runtime_stats AS r
ON p.plan_id = r.plan_id
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
JOIN sys.query_store_query AS q
ON cte.query_id = q.query_id
JOIN sys.query_store_query_text AS t
ON q.query_text_id = t.query_text_id
ORDER BY SerialDesiredMemory DESC;
메모리 부여 식별
다음 쿼리를 사용하여 상위 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),
' '
)
) 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
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;
데이터베이스 및 개체 크기 계산
다음 쿼리는 데이터베이스 크기(MB)를 반환합니다.
-- Calculates the size of the database.
SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 AS DatabaseSizeInMB
FROM sys.database_files
WHERE type_desc = 'ROWS';
GO
다음 쿼리는 데이터베이스의 개별 개체 크기(MB)를 반환합니다.
-- Calculates the size of individual database objects.
SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name;
GO
연결 모니터링
sys.dm_exec_connections 뷰를 사용하여 특정 관리되는 인스턴스에 설정된 연결에 관한 정보 및 각 연결의 세부 정보를 검색할 수 있습니다. 또한 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
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
WHERE c.session_id = @@SPID;
리소스 사용 모니터링
SQL Server에서와 마찬가지로 쿼리 저장소를 사용하여 리소스 사용량을 모니터링할 수 있습니다.
sys.dm_db_resource_stats 및 sys.server_resource_stats를 사용하여 사용량을 모니터링할 수도 있습니다.
sys.dm_db_resource_stats
모든 데이터베이스에 sys.dm_db_resource_stats 뷰를 사용할 수 있습니다. sys.dm_db_resource_stats 뷰는 서비스 계층을 기준으로 최근 리소스 사용 데이터를 보여 줍니다. CPU, 데이터 IO, 로그 쓰기, 메모리의 평균 백분율은 15초마다 기록되며 1시간 동안 유지됩니다.
이 뷰는 리소스 사용에 대한 더욱 세부적인 내용을 제공하므로 현재 상태 분석 또는 문제 해결을 위해서는 먼저 sys.dm_db_resource_stats를 사용합니다. 예를 들어 이 쿼리는 지난 1시간 동안의 현재 데이터베이스에 대한 평균 및 최대 리소스 사용량을 보여줍니다.
SELECT
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;
다른 쿼리는 sys.dm_db_resource_stats의 예를 검토하세요.
sys.server_resource_stats
sys.server_resource_stats를 사용하여 Azure SQL Managed Instance에 대한 CPU 사용량, IO 및 스토리지 데이터를 반환합니다. 데이터는 5분 간격 이내로 수집 및 집계됩니다. 15초 간격 보고에 대한 행 하나가 있습니다. 반환되는 데이터에는 CPU 사용량, 스토리지 크기, IO 사용률 및 관리되는 인스턴스 SKU가 포함됩니다. 기록 데이터는 약 14일 동안 보존됩니다.
예제에서는 sys.server_resource_stats 카탈로그 보기를 사용하여 인스턴스가 리소스를 사용하는 방법에 대한 정보를 가져올 수 있는 다양한 방법을 보여 줍니다.
다음 예제에서는 지난 7일 동안의 평균 CPU 사용량을 반환합니다.
DECLARE @s datetime; DECLARE @e datetime; SET @s= DateAdd(d,-7,GetUTCDate()); SET @e= GETUTCDATE(); SELECT AVG(avg_cpu_percent) AS Average_Compute_Utilization FROM sys.server_resource_stats WHERE start_time BETWEEN @s AND @e; GO다음 예제에서는 증가 추세 분석을 허용하기 위해 인스턴스에서 매일 사용하는 평균 스토리지 공간을 반환합니다.
DECLARE @s datetime; DECLARE @e datetime; SET @s= DateAdd(d,-7,GetUTCDate()); SET @e= GETUTCDATE(); SELECT Day = convert(date, start_time), AVG(storage_space_used_mb) AS Average_Space_Used_mb FROM sys.server_resource_stats WHERE start_time BETWEEN @s AND @e GROUP BY convert(date, start_time) ORDER BY convert(date, start_time); GO
최대 동시 요청
현재 동시 요청 수를 보려면 데이터베이스에서 이 Transact-SQL 쿼리를 실행하세요.
SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests R;
개별 데이터베이스의 워크로드를 분석하려면 이 쿼리를 수정하여 분석할 특정 데이터베이스에서 필터링해야 합니다. 예를 들어 MyDatabase라는 데이터베이스가 있다면 이 Transact-SQL 쿼리는 해당 데이터베이스의 동시 요청 수를 반환합니다.
SELECT COUNT(*) AS [Concurrent_Requests]
FROM sys.dm_exec_requests R
INNER JOIN sys.databases D ON D.database_id = R.database_id
AND D.name = 'MyDatabase';
단일 시점의 스냅샷입니다. 워크로드 및 동시 요청 요구 사항을 더 깊이 이해하려면 시간 변화에 따라 여러 샘플을 수집해야 합니다.
최대 동시 로그인
사용자 및 애플리케이션 패턴을 분석하여 로그인 빈도를 파악할 수 있습니다. 또한 테스트 환경에서 실제 부하를 실행하여 이 문서에서 설명하는 한도나 기타 한도에 도달하지 않는지 확인할 수 있습니다. 동시 로그인 횟수나 기록을 보여주는 단일 쿼리 또는 동적 관리 뷰(DMV)는 없습니다.
여러 클라이언트가 동일한 연결 문자열을 사용하는 경우 서비스에서 각 로그인을 인증합니다. 10명의 사용자가 동일한 사용자 이름 및 암호를 사용하여 데이터베이스에 동시에 연결하는 경우 동시 로그인 수는 10개가 됩니다. 이 제한은 로그인 및 인증 기간에만 적용됩니다. 동일한 10명의 사용자가 데이터베이스에 순차적으로 연결하는 경우 동시 로그인 수는 1을 초과할 수 없습니다.
최대 세션 수
현재 활성 세션 수를 보려면 데이터베이스에서 이 Transact-SQL 쿼리를 실행합니다.
SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections;
SQL Server 워크로드를 분석하려는 경우 특정 데이터베이스에 집중하도록 쿼리를 수정합니다. 이 쿼리는 데이터베이스를 Azure로 이동을 고려할 때 가능한 세션 요구 사항을 확인하는 데 도움이 됩니다.
SELECT COUNT(*) AS [Sessions]
FROM sys.dm_exec_connections C
INNER JOIN sys.dm_exec_sessions S ON (S.session_id = C.session_id)
INNER JOIN sys.databases D ON (D.database_id = S.database_id)
WHERE D.name = 'MyDatabase';
다시 말하지만, 이러한 쿼리는 특정 시점의 횟수를 반환합니다. 시간이 지남에 따라 여러 샘플을 수집하는 경우 세션 사용을 가장 잘 이해할 수 있습니다.
쿼리 성능 모니터링
느리거나 오래 실행되는 쿼리는 상당한 시스템 리소스를 소모할 수 있습니다. 이 섹션에서는 동적 관리 뷰를 사용하여 몇 가지 일반적인 쿼리 성능 문제를 감지하는 방법을 보여 줍니다.
상위 N개 쿼리 찾기
다음 예제는 평균 CPU 시간을 기준으로 상위 5개 쿼리에 대한 정보를 반환합니다. 이 예제에서는 쿼리 해시에 따라 쿼리를 집계하므로 논리적으로 동등한 쿼리가 누적 리소스 사용량별로 그룹화됩니다.
SELECT TOP 5 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;
차단된 쿼리 모니터링
느린 속도로 또는 장시간 실행하는 쿼리는 과도한 리소스 소비에 기여하고 차단된 쿼리에 따른 결과일 수 있습니다. 차단의 원인으로 부실한 애플리케이션 디자인, 잘못된 쿼리 계획, 유용한 인덱스 부족 등이 있습니다. sys.dm_tran_locks 뷰를 사용하여 데이터베이스의 현재 잠금 활동에 대한 정보를 가져올 수 있습니다. 예를 보려면 sys.dm_tran_locks를 참조하세요. 차단 문제 해결에 대한 자세한 내용은 Azure SQL 차단 문제 이해 및 해결을 참조하세요.
교착 상태 모니터링
경우에 따라 둘 이상의 쿼리가 서로 차단되어 교착 상태가 발생할 수 있습니다.
확장 이벤트 추적 데이터베이스를 만들어 교착 상태 이벤트를 캡처한 다음 쿼리 저장소에서 관련 쿼리 및 해당 실행 계획을 찾을 수 있습니다.
Azure SQL Managed Instance의 경우 교착 상태 가이드의 교착 상태 도구를 참조하세요.
쿼리 계획 모니터링
또한 비효율적인 쿼리 계획 때문에 CPU 사용량이 증가할 수 있습니다. 다음 예제에서는 sys.dm_exec_query_stats 뷰를 사용하여 가장 많은 누적 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 50
qs.plan_handle,
qs.total_worker_time
FROM
sys.dm_exec_query_stats 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;
기타 모니터링 옵션
데이터베이스 Watcher를 사용하여 모니터링(미리 보기)
데이터베이스 Watcher는 심층 워크로드 모니터링 데이터를 수집하여 데이터베이스 성능, 구성 및 상태에 대한 자세한 보기를 제공합니다. Azure Portal의 대시보드는 Azure SQL 자산의 단일 창 보기와 모니터링되는 각 리소스에 대한 자세한 보기를 제공합니다. 데이터는 Azure 구독의 중앙 데이터 저장소로 수집됩니다. 수집된 데이터를 쿼리, 분석, 내보내기, 시각화하고 다운스트림 시스템과 통합할 수 있습니다.
데이터베이스 Watcher에 대한 자세한 내용은 다음 문서를 참조하세요.
- 데이터베이스 Watcher로 Azure SQL 워크로드 모니터링(프리뷰)
- 빠른 시작: Azure SQL을 모니터링하는 데이터베이스 Watcher 만들기(프리뷰)
- 데이터베이스 Watcher 만들기 및 구성(프리뷰)
- 데이터베이스 Watcher 데이터 수집 및 데이터 세트(프리뷰)
- 데이터베이스 Watcher 모니터링 데이터 분석(프리뷰)
- 데이터베이스 Watcher FAQ
Azure Monitor를 사용하여 모니터
Azure Monitor는 Azure SQL Managed Instance를 모니터링하기 위한 다양한 진단 데이터 수집 그룹, 메트릭, 엔드포인트를 제공합니다. 자세한 내용은 Azure Monitor를 사용하여 Azure SQL Managed Instance 모니터링을 참조하세요. Azure SQL 분석(미리 보기)은 많은 모니터링 솔루션이 더 이상 개발되지 않는 Azure Monitor와의 통합입니다. 추가 모니터링 옵션은 Azure SQL Managed Instance 및 Azure SQL Database의 모니터링 및 성능 튜닝을 참조하세요.