Azure SQL Database의 높은 CPU 진단 및 문제 해결

적용 대상: Azure SQL Database

Azure SQL Database는 높은 CPU 사용량의 원인을 식별하고 워크로드 성능을 최적화하는 기본 제공 도구를 제공합니다. 이러한 도구를 사용하여 높은 CPU 사용량이 발생하는 동안 또는 인시던트가 완료된 후 사후 대응적으로 문제를 해결할 수 있습니다. 또한 자동 조정을 사용하여 시간 경과에 따라 데이터베이스에 대한 CPU 사용량을 사전에 줄일 수 있습니다. 이 문서에서는 Azure SQL Database의 기본 제공 도구를 사용하여 높은 CPU를 진단하고 문제를 해결하는 방법을 설명하고 CPU 리소스를 추가해야 하는 경우를 설명합니다.

vCore 수 이해

CPU 인시던트가 높을 경우 데이터베이스에서 사용할 수 있는 가상 코어(vCore)의 수를 이해하는 것이 유용합니다. vCore는 논리 CPU와 동일합니다. vCore 수는 데이터베이스에 사용할 수 있는 CPU 리소스를 이해하는 데 도움이 됩니다.

Azure Portal에서 vCore 수 식별

프로비전된 컴퓨팅 계층과 함께 vCore 기반 서비스 계층을 사용하는 경우 Azure Portal에서 데이터베이스에 대한 vCore 수를 빠르게 식별할 수 있습니다. 이 경우 개요 페이지의 데이터베이스에 나열된 가격 책정 계층에는 vCore 수가 포함됩니다. 예를 들어 데이터베이스의 가격 책정 계층은 '범용: 표준 시리즈(Gen5), 16개 vCore'일 수 있습니다.

서버리스 컴퓨팅 계층에 있는 데이터베이스의 경우 vCore 수는 항상 데이터베이스의 최대 vCore 설정과 동일합니다. VCore 수는 개요 페이지의 데이터베이스에 나열된 가격 계층에 표시됩니다. 예를 들어 데이터베이스의 가격 책정 계층은 '범용: 서버리스, 표준 시리즈(Gen5), 16개 vCore'일 수 있습니다.

DTU 기반 구매 모델에서 데이터베이스를 사용하는 경우 Transact-SQL을 사용하여 데이터베이스의 vCore 계수를 쿼리해야 합니다.

Transact-SQL을 사용하여 vCore 수 식별

Transact-SQL을 사용하여 모든 데이터베이스의 현재 vCore 수를 식별할 수 있습니다. SSMS(SQL Server Management Studio), Azure Data Studio 또는 Azure Portal의 쿼리 편집기를 사용하여 Azure SQL Database에 대해 Transact-SQL을 실행할 수 있습니다.

데이터베이스에 연결하고 다음 쿼리를 실행합니다.

SELECT 
    COUNT(*) as vCores
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE';
GO

높은 CPU의 원인 식별

Azure Portal, SSMS의 쿼리 저장소 대화형 도구, SSMS 및 Azure Data Studio의 Transact-SQL 쿼리를 사용하여 CPU 사용률을 측정하고 분석할 수 있습니다.

Azure Portal 및 쿼리 저장소는 완료된 쿼리에 대한 CPU 메트릭과 같은 실행 통계를 표시합니다. 하나 이상의 지속적인 장기 실행 쿼리로 인해 현재 높은 CPU 인시던트가 발생하는 경우 Transact-SQL을 사용하여 현재 실행 중인 쿼리를 식별하세요.

새롭고 비정상적으로 높은 CPU 사용률의 일반적인 원인은 다음과 같습니다.

• 많은 양의 CPU를 사용하는 워크로드의 새 쿼리.
• 정기적으로 실행되는 쿼리의 빈도 증가.
• PSP(매개 변수에 민감한 계획) 문제로 인한 회귀를 포함한 쿼리 계획 회귀 때문에 하나 이상의 쿼리가 많은 CPU 용량 사용.
• 쿼리 계획의 컴파일 또는 재컴파일이 대폭 증가.
• 쿼리가 과도한 병렬 처리를 사용하는 데이터베이스.

높은 CPU 인시던트의 원인을 이해하려면 데이터베이스에 대해 높은 CPU 사용률이 발생하고 해당 시점에 CPU를 사용하는 상위 쿼리를 식별합니다.

검사:

• 상당한 CPU를 사용하는 새로운 쿼리가 워크로드에 나타나거나 정기적으로 실행되는 쿼리의 빈도가 증가하고 있습니까? 다음 방법 중 하나를 사용하여 조사합니다. 기록이 제한된 쿼리(새 쿼리)와 기록이 긴 쿼리의 실행 빈도를 찾습니다.
  • Azure Portal에서 CPU 메트릭 및 관련 상위 쿼리 검토
  • Transact-SQL을 사용하여 CPU 사용량별로 최근 상위 15개 쿼리를 쿼리합니다.
  • CPU 시간당 상위 쿼리를 식별하는 SSMS 대화형 쿼리 저장소 도구 사용
• 워크로드의 일부 쿼리가 과거보다 실행당 더 많은 CPU를 사용하고 있습니까? 그렇다면 쿼리 실행 계획이 변경되었습니까? 이 쿼리에 PSP(매개 변수에 민감한 계획) 문제가 있을 수 있습니다. 다음 기법 중 하나를 사용하여 조사합니다. CPU 사용량이 크게 다른 여러 쿼리 실행 계획으로 쿼리를 찾습니다.
  • Transact-SQL을 사용하여 CPU 사용량별로 최근 상위 15개 쿼리를 쿼리합니다.
  • CPU 시간당 상위 쿼리를 식별하는 SSMS 대화형 쿼리 저장소 도구 사용
• 많은 양의 컴파일 또는 재컴파일이 발생한 증거가 있습니까? 쿼리 해시별로 가장 자주 컴파일된 쿼리를 쿼리하고 컴파일 빈도를 검토합니다.
• 쿼리가 과도한 병렬 처리를 사용하고 있나요? MAXDOP 데이터베이스 범위 구성을 쿼리하고 vCore 수를 검토합니다. 과도한 병렬 처리는 MAXDOP가 0(으)로 설정되고 vCore 수가 8보다 큰 데이터베이스에서 자주 발생합니다.

참고 항목

Azure SQL Database는 고가용성 및 재해 복구, 데이터베이스 백업 및 복원, 모니터링, 쿼리 저장소, 자동 조정 등과 같은 핵심 서비스 기능을 구현하기 위해 컴퓨팅 리소스가 필요합니다. 이러한 계산 리소스는 vCore 수가 적은 데이터베이스나 밀도가 높은 탄성 풀의 데이터베이스에서 특히 두드러질 수 있습니다. Azure SQL Database의 리소스 관리에서 자세히 알아보세요.

Azure Portal에서 CPU 사용 현황 메트릭 및 관련 상위 쿼리 검토

Azure Portal을 사용하여 시간 경과에 따라 데이터베이스에서 사용되는 CPU의 백분율을 포함하여 다양한 CPU 메트릭을 추적합니다. Azure Portal은 CPU 메트릭을 데이터베이스 쿼리 저장소의 정보와 결합하여, 주어진 시간에 데이터베이스에서 어떤 쿼리가 CPU를 사용했는지 식별할 수 있도록 합니다.

다음 단계에 따라 CPU 백분율 메트릭을 찾을 수 있습니다.

1. Azure Portal의 데이터베이스로 이동합니다.
2. 왼쪽 메뉴의 지능형 성능에서 Query Performance Insight를 선택합니다.

Query Performance Insight의 기본 보기에는 24시간 동안의 데이터가 표시됩니다. CPU 사용량은 데이터베이스에 사용된 총 사용 가능한 CPU의 백분율로 표시됩니다.

해당 기간에 실행되는 상위 5개 쿼리는 CPU 사용량 그래프 위의 세로 막대에 표시됩니다. 차트에서 시간대를 선택하거나 특정 기간을 탐색하려면 사용자 지정 메뉴를 사용합니다. 표시된 쿼리 수를 늘릴 수도 있습니다.

높은 CPU를 나타내는 각 쿼리 ID를 선택하여 쿼리에 대한 세부 정보를 엽니다. 세부 정보에는 쿼리에 대한 성능 기록과 함께 쿼리 텍스트가 포함됩니다. 최근 쿼리에 대해 CPU가 증가했는지 검사합니다.

다음 섹션에서 쿼리 저장소를 사용하여 쿼리 계획을 자세히 조사하려면 쿼리 ID를 기록해 둡니다.

Azure Portal에서 식별된 상위 쿼리에 대한 쿼리 계획 검토

다음 단계를 따라 SSMS의 대화형 쿼리 저장소 도구에서 쿼리 ID를 사용하여 시간 경과에 따른 쿼리 실행 계획을 검사합니다.

1. SSMS를 엽니다.
2. 개체 탐색기에서 Azure SQL Database에 연결합니다.
3. 개체 탐색기에서 데이터베이스 노드 확장
4. 쿼리 저장소 폴더를 확장합니다.
5. 추적된 쿼리 창을 엽니다.
6. 화면 왼쪽 위에 있는 쿼리 추적 상자에 검색어 ID를 입력하고 Enter를 누릅니다.
7. 필요한 경우 구성을 선택하여 CPU 사용률이 높은 시간과 일치하도록 시간 간격을 조정합니다.

페이지에는 최근 24시간 동안의 쿼리에 대한 실행 계획 및 관련 메트릭이 표시됩니다.

Transact-SQL을 사용하여 현재 실행 중인 쿼리 식별

Transact-SQL을 사용하면 지금까지 사용한 CPU 시간으로 현재 실행 중인 쿼리를 식별할 수 있습니다. 또한 Transact-SQL을 사용하여 데이터베이스의 최근 CPU 사용량, CPU별 상위 쿼리 및 가장 자주 컴파일된 쿼리를 쿼리할 수 있습니다.

SSMS(SQL Server Management Studio), Azure Data Studio 또는 Azure Portal의 쿼리 편집기를 사용하여 CPU 메트릭을 쿼리할 수 있습니다. SSMS 또는 Azure Data Studio를 사용하는 경우 새 쿼리 창을 열고 데이터베이스(master 데이터베이스가 아님)에 연결합니다.

다음 쿼리를 실행하여 CPU 사용량 및 실행 계획으로 현재 실행 중인 쿼리를 찾습니다. CPU 시간은 밀리초 단위로 반환됩니다.

SELECT
    req.session_id,
    req.status,
    req.start_time,
    req.cpu_time AS 'cpu_time_ms',
    req.logical_reads,
    req.dop,
    s.login_name,
    s.host_name,
    s.program_name,
    object_name(st.objectid,st.dbid) 'ObjectName',
    REPLACE (REPLACE (SUBSTRING (st.text,(req.statement_start_offset/2) + 1,
        ((CASE req.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), ' ') AS statement_text,
    qp.query_plan,
    qsx.query_plan as query_plan_with_in_flight_statistics
FROM sys.dm_exec_requests as req  
JOIN sys.dm_exec_sessions as s on req.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as st
OUTER APPLY sys.dm_exec_query_plan(req.plan_handle) as qp
OUTER APPLY sys.dm_exec_query_statistics_xml(req.session_id) as qsx
ORDER BY req.cpu_time desc;
GO

이 쿼리는 실행 계획의 두 복사본을 반환합니다. query_plan 열에는 sys.dm_exec_query_plan의 실행 계획이 포함되어 있습니다. 이 쿼리 계획 버전에는 행 수의 추정만 포함되며 실행 통계는 포함되지 않습니다.

열 query_plan_with_in_flight_statistics이 실행 계획을 반환하는 경우 이 계획은 추가 정보를 제공합니다. query_plan_with_in_flight_statistics 열은 sys.dm_exec_query_statistics_xml에서 현재 실행 중인 쿼리가 지금까지 반환한 실제 행 수와 같은 "진행 중인" 실행 통계가 포함된 데이터를 반환합니다.

지난 1시간 동안의 CPU 사용 현황 메트릭 검토

sys.dm_db_resource_stats에 대한 다음 쿼리는 지난 1시간 동안 15초 간격의 평균 CPU 사용량을 반환합니다.

SELECT
    end_time,
    avg_cpu_percent,
    avg_instance_cpu_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC; 
GO

avg_cpu_percent 열에만 집중하지 않는 것이 중요합니다. avg_instance_cpu_percent 열에는 사용자 및 내부 워크로드 모두에서 사용하는 CPU가 포함됩니다. avg_instance_cpu_percent가 100%에 가까우면 CPU 리소스가 포화된 것입니다. 이 경우 앱 처리량이 충분하지 않거나 쿼리 지연 시간이 긴 경우 높은 CPU 문제를 해결해야 합니다.

Azure SQL Database의 리소스 관리에서 자세히 알아보세요.

더 많은 쿼리를 보려면 sys.dm_db_resource_stats의 예를 검토하세요.

CPU 사용량별로 최근 상위 15개 쿼리를 쿼리

쿼리 저장소는 쿼리에 대한 CPU 사용량을 비롯한 실행 통계를 추적합니다. 다음 쿼리는 지난 2시간 동안 실행된 상위 15개 쿼리를 CPU 사용량별로 정렬하여 반환합니다. CPU 시간은 밀리초 단위로 반환됩니다.

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
    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 *, 
    ROW_NUMBER() OVER (ORDER BY total_cpu_ms DESC, query_hash ASC) AS RN
    FROM AggregatedCPU)
SELECT *
FROM OrderedCPU AS OD
WHERE OD.RN<=15
ORDER BY total_cpu_ms DESC;
GO

이 쿼리는 쿼리의 해시 값별로 그룹화합니다. number_of_distinct_query_ids 열에서 높은 값을 찾으면 자주 실행되는 쿼리가 제대로 매개 변수화되지 않았는지 조사합니다. 매개 변수화되지 않은 쿼리는 실행할 때마다 컴파일될 수 있으며, 이는 상당한 CPU를 사용하고 쿼리 저장소의 성능에 영향을 미칩니다.

개별 쿼리에 대해 자세히 알아보려면 쿼리 해시를 기록하고 이를 사용하여 주어진 쿼리 해시에 대한 CPU 사용량 및 쿼리 계획을 식별하세요.

쿼리 해시로 가장 자주 컴파일된 쿼리

쿼리 계획을 컴파일하는 것은 CPU 집약적인 프로세스입니다. Azure SQL Database 재사용을 위한 메모리의 캐시 계획. 일부 쿼리는 매개 변수화되지 않았거나 RECOMPILE 힌트가 강제로 재컴파일하는 경우 자주 컴파일될 수 있습니다.

쿼리 저장소는 쿼리가 컴파일된 횟수를 추적합니다. 다음 쿼리를 실행하여 분당 평균 컴파일 수와 함께 컴파일 수별로 쿼리 저장소의 상위 20개 쿼리를 식별합니다.

SELECT TOP (20)
    query_hash,
    MIN(initial_compile_start_time) as initial_compile_start_time,
    MAX(last_compile_start_time) as last_compile_start_time,
    CASE WHEN DATEDIFF(mi,MIN(initial_compile_start_time), MAX(last_compile_start_time)) > 0
        THEN 1.* SUM(count_compiles) / DATEDIFF(mi,MIN(initial_compile_start_time), 
            MAX(last_compile_start_time)) 
        ELSE 0 
        END as avg_compiles_minute,
    SUM(count_compiles) as count_compiles
FROM sys.query_store_query AS q
GROUP BY query_hash
ORDER BY count_compiles DESC;
GO

개별 쿼리에 대해 자세히 알아보려면 쿼리 해시를 기록하고 이를 사용하여 주어진 쿼리 해시에 대한 CPU 사용량 및 쿼리 계획을 식별하세요.

주어진 쿼리 해시에 대한 CPU 사용량 및 쿼리 계획 식별

다음 쿼리를 실행하여 지정된 query_hash에 대한 개별 쿼리 ID, 쿼리 텍스트 및 쿼리 실행 계획을 찾습니다. CPU 시간은 밀리초 단위로 반환됩니다.

@query_hash 변수의 값을 워크로드에 유효한 query_hash로 바꿉니다.

declare @query_hash binary(8);

SET @query_hash = 0x6557BE7936AA2E91;

with query_ids as (
    SELECT
        q.query_hash,
        q.query_id,
        p.query_plan_hash,
        SUM(qrs.count_executions) * AVG(qrs.avg_cpu_time)/1000. as total_cpu_time_ms,
        SUM(qrs.count_executions) AS sum_executions,
        AVG(qrs.avg_cpu_time)/1000. AS avg_cpu_time_ms
    FROM sys.query_store_query q
    JOIN sys.query_store_plan p on q.query_id=p.query_id
    JOIN sys.query_store_runtime_stats qrs on p.plan_id = qrs.plan_id
    WHERE q.query_hash = @query_hash
    GROUP BY q.query_id, q.query_hash, p.query_plan_hash)
SELECT qid.*,
    qt.query_sql_text,
    p.count_compiles,
    TRY_CAST(p.query_plan as XML) as query_plan
FROM query_ids as qid
JOIN sys.query_store_query AS q ON qid.query_id=q.query_id
JOIN sys.query_store_query_text AS qt on q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON qid.query_id=p.query_id and qid.query_plan_hash=p.query_plan_hash
ORDER BY total_cpu_time_ms DESC;
GO

이 쿼리는 쿼리 저장소의 전체 기록에서 query_hash에 대한 실행 계획의 각 변형에 대해 하나의 행을 반환합니다. 결과는 총 CPU 시간을 기준으로 정렬됩니다.

대화형 쿼리 저장소 도구를 사용하여 과거 CPU 사용률 추적

그래픽 도구를 사용하려는 경우 다음 단계에 따라 SSMS에서 대화형 쿼리 저장소 도구를 사용합니다.

1. SSMS를 열고 개체 탐색기에서 데이터베이스에 연결합니다.
2. 개체 탐색기에서 데이터베이스 노드 확장
3. 쿼리 저장소 폴더를 확장합니다.
4. 전체 리소스 사용 창을 엽니다.

지난 달에 대한 데이터베이스의 총 CPU 시간(밀리초)이 창의 왼쪽 아래 부분에 표시됩니다. 기본 보기에서 CPU 시간은 일별로 집계됩니다.

창의 오른쪽 위에 있는 구성을 선택하여 다른 기간을 선택합니다. 집계 단위를 변경할 수도 있습니다. 예를 들어 특정 날짜 범위의 데이터를 보고 시간별로 데이터를 집계하도록 선택할 수 있습니다.

대화형 쿼리 저장소 도구를 사용하여 CPU 시간별로 상위 쿼리 식별

차트에서 막대를 선택하여 드릴 인하고 특정 기간에 실행 중인 쿼리를 확인합니다. 리소스를 가장 많이 사용하는 쿼리 창이 열립니다. 또는 개체 탐색기의 데이터베이스 아래에 있는 쿼리 저장소 노드에서 리소스를 가장 많이 사용하는 쿼리를 직접 열 수 있습니다.

기본 보기에서 리소스를 가장 많이 사용하는 쿼리 창에는 기간(ms)별로 쿼리가 표시됩니다. 기간은 때때로 CPU 시간보다 낮을 수 있습니다. 병렬 처리를 사용하는 쿼리는 전체 기간보다 훨씬 더 많은 CPU 시간을 사용할 수 있습니다. 대기 시간이 긴 경우 기간이 CPU 시간보다 높을 수도 있습니다. CPU 시간별로 쿼리를 보려면 창 왼쪽 위에 있는 메트릭 드롭다운을 선택하고 CPU 시간(ms)을 선택합니다.

왼쪽 위의 사분면에 있는 각 막대는 쿼리를 나타내고 있습니다. 막대를 선택하여 해당 쿼리에 대한 세부 정보를 봅니다. 화면의 오른쪽 위 사분면은 해당 쿼리에 대해 쿼리 저장소에 있는 실행 계획의 수를 보여주고 실행된 시기와 선택한 메트릭이 사용된 정도에 따라 매핑합니다. 각 계획 ID를 선택하여 화면 아래에 표시되는 쿼리 실행 계획을 제어합니다.

참고

쿼리 저장소 보기 및 상위 리소스 소비자 보기에 나타나는 모양을 해석하는 것에 대한 가이드는 쿼리 저장소 모범 사례를 참조하세요.

CPU 사용량 줄이기

문제 해결의 일부에는 이전 섹션에서 식별된 쿼리에 대한 자세한 내용이 포함되어야 합니다. 인덱스를 조정하고, 애플리케이션 패턴을 수정하고, 쿼리를 조정하고, 데이터베이스에 대한 CPU 관련 설정을 조정하여 CPU 사용량을 줄일 수 있습니다.

• 워크로드에 나타나는 상당한 CPU를 사용하는 새 쿼리를 찾은 경우 해당 쿼리에 대해 인덱스가 최적화되었는지 검사합니다. 인덱스를 수동으로 조정하거나 자동 인덱스 조정으로 CPU 사용량을 줄일 수 있습니다. 증가된 워크로드에 대해 최대 병렬 처리 수준 설정이 올바른지 평가합니다.
• 쿼리의 전체 실행 횟수가 이전보다 많다는 것을 알게 된 경우 가장 많은 CPU를 사용하는 쿼리에 대한 인덱스를 조정하고 자동 인덱스 조정을 고려합니다. 증가된 워크로드에 대해 최대 병렬 처리 수준 설정이 올바른지 평가합니다.
• 워크로드에서 PSP(매개 변수에 민감한 계획) 문제가 있는 쿼리를 찾은 경우 자동 계획 수정(강제 계획)을 고려하세요. 또한 쿼리 저장소에서 수동으로 계획을 실행하거나 쿼리에 대해 Transact-SQL을 조정하여 일관되게 고성능 쿼리 계획을 생성할 수 있습니다.
• 많은 양의 컴파일 또는 재컴파일이 발생하고 있다는 증거를 찾은 경우 적절하게 매개 변수화되거나 재컴파일 힌트가 필요하지 않도록 쿼리를 조정합니다.
• 쿼리가 과도한 병렬 처리를 사용하고 있는 것으로 확인되면 최대 병렬 처리 수준을 조정합니다.

이 섹션에서는 다음 전략을 고려합니다.

자동 인덱스 조정을 통해 CPU 사용량 감소

효과적인 인덱스 조정은 많은 쿼리에 대한 CPU 사용량을 줄입니다. 최적화된 인덱스는 쿼리에 대한 논리적 및 물리적 읽기를 줄여주므로, 종종 쿼리가 더 적은 작업을 수행해야 합니다.

Azure SQL Database는 기본 복제본의 워크로드에 대해 자동 인덱스 관리를 제공합니다. 자동 인덱스 관리는 기계 학습을 사용하여 워크로드를 모니터하고 데이터베이스에 대한 rowstore 디스크 기반 비클러스터형 인덱스를 최적화합니다.

Azure Portal에서 인덱스 권장 사항을 포함한 성능 권장 사항을 검토합니다. 이러한 권장 사항을 수동으로 적용하거나 CREATE INDEX 자동 조정 옵션을 사용하여 데이터베이스에서 새 인덱스를 만들고 성능을 확인할 수 있습니다.

자동 계획 수정(강제 계획)으로 CPU 사용량 줄이기

높은 CPU 인시던트의 또 다른 일반적인 원인은 실행 계획 선택 회귀입니다. Azure SQL Database는 기본 복제본의 워크로드에서 쿼리 실행 계획의 회귀를 식별하기 위해 강제 계획 자동 조정 옵션을 제공합니다. 이 자동 조정 기능을 사용하도록 설정하면 Azure SQL Database는 쿼리 실행 계획을 강제 실행하면 실행 계획 회귀가 포함된 쿼리의 성능이 안정적으로 향상되는지 테스트합니다.

데이터베이스가 2020년 3월 이후에 만들어진 경우 강제 계획 자동 조정 옵션이 자동으로 사용하도록 설정되었습니다. 이 시간 이전에 데이터베이스가 만들어진 경우 강제 계획 자동 조정 옵션을 사용하도록 설정할 수 있습니다.

인덱스 수동 조정

높은 CPU의 원인 식별에 설명된 방법을 사용하여 CPU를 가장 많이 사용하는 쿼리에 대한 쿼리 계획을 식별합니다. 이러한 실행 계획은 쿼리 속도를 높이기 위해 비클러스터형 인덱스를 식별하고 추가하는 데 도움이 됩니다.

데이터베이스의 각 디스크 기반 비클러스터형 인덱스에는 스토리지 공간이 필요하며 SQL 엔진에서 유지 관리해야 합니다. 가능하면 새 인덱스를 추가하는 대신 기존 인덱스를 수정하고 새 인덱스가 CPU 사용량을 성공적으로 줄이도록 합니다. 비클러스터형 인덱스에 대한 개요는 비클러스터형 인덱스 디자인 가이드라인을 참조하세요.

일부 워크로드의 경우 columnstore 인덱스가 자주 읽는 쿼리의 CPU를 줄이는 가장 좋은 선택일 수 있습니다. columnstore 인덱스가 적절할 수 있는 시나리오에 대한 상위 수준 권장 사항은 columnstore 인덱스 - 디자인 지침을 참조하세요.

애플리케이션, 쿼리 및 데이터베이스 설정 조정

상위 쿼리를 검사할 때 "대화량이 많은" 동작, 분할의 이점을 얻을 수 있는 워크로드, 차선의 데이터베이스 액세스 디자인과 같은 응용 프로그램 안티패턴을 찾을 수 있습니다. 읽기 작업이 많은 워크로드의 경우, 자주 읽는 데이터를 스케일 아웃하기 위한 장기 전략으로서 읽기 전용 쿼리 워크로드를 오프로드할 읽기 전용 복제본과 애플리케이션 계층 캐싱을 고려합니다.

워크로드에서 식별된 쿼리를 사용하여 최상위 CPU를 수동으로 조정하도록 선택할 수도 있습니다. 수동 조정 옵션에는 Transact-SQL 문의 재작성, 쿼리 저장소에서 계획 강제 실행 및 쿼리 힌트 적용이 포함됩니다.

쿼리가 종종 성능에 최적화되지 않은 실행 계획을 사용하는 경우를 식별하는 경우 PSP(매개 변수에 민감한 계획)가 있는 쿼리의 솔루션을 검토하세요.

많은 수의 계획이 있는 매개 변수화되지 않은 쿼리를 식별하는 경우, 이러한 쿼리를 매개 변수화하는 것을 고려하고 길이 및 정밀도를 포함한 매개 변수 데이터 형식을 완전히 선언해야 합니다. 이는 쿼리를 수정하거나 특정 쿼리의 강제 매개 변수화 계획 지침을 만들거나 데이터베이스 수준에서 강제 매개 변수화를 사용하여 수행할 수 있습니다.

컴파일 속도가 높은 쿼리를 식별하는 경우 빈번한 컴파일의 원인을 식별합니다. 빈번한 컴파일의 가장 일반적인 원인은 RECOMPILE 힌트입니다. 가능하면 RECOMPILE 힌트가 추가된 시기와 해결하려는 문제를 식별합니다. RECOMPILE 힌트 없이 자주 실행되는 쿼리에 대해 일관된 성능을 제공하기 위해 대체 성능 조정 솔루션을 구현할 수 있는지 조사합니다.

최대 병렬 처리 수준을 조정하여 CPU 사용량 줄이기

MAXDOP(최대 병렬 처리 수준) 설정은 데이터베이스 엔진의 쿼리 내 병렬 처리를 제어합니다. 일반적으로 MAXDOP 값이 높을수록 쿼리당 병렬 스레드 수가 많아지고 쿼리 실행이 빨라집니다.

경우에 따라 동시에 실행되는 많은 수의 병렬 쿼리로 인해 워크로드가 느려지고 CPU 사용량이 높아질 수 있습니다. 과도한 병렬 처리는 MAXDOP가 높은 수 또는 0으로 설정된 vCore 수가 많은 데이터베이스에서 발생할 가능성이 가장 높습니다. MAXDOP가 0으로 설정되면 데이터베이스 엔진은 병렬 스레드에서 사용할 스케줄러의 수를 총 논리 코어 수와 64 중에 더 작은 값으로 설정합니다.

Transact-SQL을 사용하여 데이터베이스에 대한 최대 병렬 처리 수준 설정을 식별할 수 있습니다. SSMS 또는 Azure Data Studio를 사용하여 데이터베이스에 연결하고 다음 쿼리를 실행합니다.

SELECT 
    name, 
    value, 
    value_for_secondary, 
    is_value_default 
FROM sys.database_scoped_configurations
WHERE name=N'MAXDOP';
GO

데이터베이스 수준에서 MAXDOP 구성의 작은 변경을 실험하거나 쿼리 힌트를 사용하여 기본이 아닌 MAXDOP를 사용하도록 개별 문제 쿼리를 수정하는 것이 좋습니다. 자세한 내용은 최대 병렬 처리 수준 구성의 예를 참조하세요.

CPU 리소스를 추가하는 경우

워크로드의 쿼리 및 인덱스가 적절하게 조정되었거나 성능 조정을 위해 내부 프로세스 또는 기타 이유로 단기간에 수행할 수 없는 변경이 필요하다는 것을 알 수 있습니다. 더 많은 CPU 리소스를 추가하면 이러한 데이터베이스에 도움이 될 수 있습니다. 최소 가동 중지 시간으로 동적으로 데이터베이스 리소스 크기를 조정할 수 있습니다.

vCore 구매 모델을 사용하여 데이터베이스에 대한 vCore 계수 또는 하드웨어 구성을 구성하여 Azure SQL Database에 CPU 리소스를 더 추가할 수 있습니다.

DTU 기반 구매 모델에서는 서비스 계층을 높이고 DTU(데이터베이스 트랜잭션 단위) 수를 늘릴 수 있습니다. DTU는 CPU, 메모리, 읽기 및 쓰기의 혼합 측정을 나타냅니다. vCore 구매 모델의 한 가지 혜택은 사용 중인 하드웨어와 vCore 수를 보다 세부적으로 제어할 수 있다는 것입니다. Azure SQL Database를 DTU 기반 모델에서 vCore 기반 모델로 마이그레이션하여 구매 모델 간에 전환할 수 있습니다.

관련 콘텐츠

다음 문서에서 Azure SQL Database 모니터 및 성능 조정에 대해 자세히 알아보세요.

• 동적 관리 뷰를 사용하여 Azure SQL Database 및 Azure SQL Managed Instance 성능 모니터
• SQL Server 인덱스 아키텍처 및 디자인 가이드
• Azure Portal에서 자동 조정을 사용하여 쿼리 모니터링 및 워크로드 성능 향상
• 쿼리 처리 아키텍처 가이드
• 쿼리 저장소에 대한 모범 사례
• Azure SQL Database에서 검색 가능한 쿼리 성능 병목 상태 유형
• Azure SQL Database에서 교착 상태 분석 및 방지