두 서버 간에 현저한 성능 차이를 나타내는 쿼리 문제 해결

적용 대상: SQL Server

이 문서에서는 쿼리가 다른 서버보다 한 서버에서 느리게 실행되는 성능 문제에 대한 문제 해결 단계를 제공합니다.

증상

SQL Server가 설치된 두 개의 서버가 있다고 가정합니다. SQL Server 인스턴스 중 하나에는 다른 SQL Server 인스턴스에 있는 데이터베이스의 복사본이 포함되어 있습니다. 두 서버의 데이터베이스에 대해 쿼리를 실행하면 쿼리가 한 서버에서 다른 서버보다 느리게 실행됩니다.

다음 단계는 이 문제를 해결하는 데 도움이 될 수 있습니다.

1단계: 여러 쿼리의 일반적인 문제인지 확인

다음 두 가지 방법 중 하나를 사용하여 두 서버에서 두 개 이상의 쿼리에 대한 성능을 비교합니다.

• 두 서버에서 쿼리를 수동으로 테스트합니다.

  1. 다음과 같은 쿼리에 우선 순위가 지정된 테스트를 위해 몇 가지 쿼리를 선택합니다.
     • 한 서버에서 다른 서버보다 훨씬 빠릅니다.
     • 사용자/애플리케이션에 중요합니다.
     • 요청 시 문제를 재현하기 위해 자주 실행되거나 설계되었습니다.
     • 데이터를 캡처하는 데 충분한 시간(예: 5밀리초 쿼리 대신 10초 쿼리 선택).
  2. 두 서버에서 쿼리를 실행합니다.
  3. 각 쿼리에 대해 두 서버의 경과된 시간(기간)을 비교합니다.

• SQL Nexus를 사용하여 성능 데이터를 분석합니다.

  1. 두 서버의 쿼리에 대한 PSSDiag/SQLdiag 또는 SQL LogScout 데이터를 수집합니다.
  2. SQL Nexus를 사용하여 수집된 데이터 파일을 가져오고 두 서버의 쿼리를 비교합니다. 자세한 내용은 두 로그 컬렉션 간의 성능 비교(예: 느리고 빠름)를 참조하세요.

시나리오 1: 두 서버에서 단일 쿼리만 다르게 수행

하나의 쿼리만 다르게 수행하면 환경보다는 개별 쿼리에만 문제가 발생할 수 있습니다. 이 경우 2단계: 데이터 수집 및 성능 문제 유형을 확인합니다.

시나리오 2: 두 서버에서 여러 쿼리가 다르게 수행됩니다.

한 서버에서 여러 쿼리가 다른 쿼리보다 느리게 실행되는 경우 가장 가능성이 큰 원인은 서버 또는 데이터 환경의 차이입니다. 환경 차이 진단으로 이동하여 두 서버 간의 비교가 유효한지 확인합니다 .

2단계: 데이터 수집 및 성능 문제 유형 확인

경과된 시간, CPU 시간 및 논리 읽기 수집

두 서버에서 쿼리의 경과된 시간과 CPU 시간을 수집하려면 상황에 가장 적합한 다음 방법 중 하나를 사용합니다.

• 현재 실행 중인 문의 경우 sys.dm_exec_requests total_elapsed_time 및 cpu_time 열을 확인합니다. 다음 쿼리를 실행하여 데이터를 가져옵니다.

  SELECT 
      req.session_id
      , req.total_elapsed_time AS duration_ms
      , req.cpu_time AS cpu_time_ms
      , req.total_elapsed_time - req.cpu_time AS wait_time
      , req.logical_reads
      , 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 total_elapsed_time DESC;
  

• 쿼리의 과거 실행의 경우 sys.dm_exec_query_stats last_elapsed_time 및 last_worker_time 열을 확인합니다. 다음 쿼리를 실행하여 데이터를 가져옵니다.

  SELECT t.text,
       (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
       (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
       ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
       qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
       qs.total_logical_writes / qs.execution_count AS avg_writes,
       (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
  FROM sys.dm_exec_query_stats qs
       CROSS apply sys.Dm_exec_sql_text (sql_handle) t
  WHERE t.text like '<Your Query>%'
  -- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
  ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC
  

  참고 항목

  음수 값을 표시하면 avg_wait_time 병렬 쿼리입니다.

• SSMS(SQL Server Management Studio) 또는 Azure Data Studio에서 요청 시 쿼리를 실행할 수 있는 경우 SET STATISTICS TIME ON 및 SET STATISTICS IOON를 사용하여 쿼리를 실행합니다.

  SET STATISTICS TIME ON
  SET STATISTICS IO ON
  <YourQuery>
  SET STATISTICS IO OFF
  SET STATISTICS TIME OFF
  

  그런 다음 메시지에서 다음과 같은 CPU 시간, 경과된 시간 및 논리적 읽기가 표시됩니다.

    Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
  
    SQL Server Execution Times:
      CPU time = 460 ms,  elapsed time = 470 ms.
  

• 쿼리 계획을 수집할 수 있는 경우 실행 계획 속성에서 데이터를 확인합니다.

  1. 실제 실행 계획 포함을 사용하여 쿼리를 실행합니다.

  2. 실행 계획에서 가장 왼쪽에 있는 연산자를 선택합니다.

  3. 속성에서 QueryTimeStats 속성을 확장합니다.

  4. ElapsedTime 및 CpuTime을 확인합니다.

     

쿼리의 경과된 시간과 CPU 시간을 비교하여 두 서버의 문제 유형을 확인합니다.

유형 1: CPU 바인딩(실행기)

CPU 시간이 경과된 시간과 같거나 더 높은 경우 CPU 바인딩된 쿼리로 처리할 수 있습니다. 예를 들어 경과된 시간이 3,000밀리초(밀리초)이고 CPU 시간이 2900ms이면 대부분의 경과된 시간이 CPU에 소요됩니다. 그런 다음 CPU 바인딩된 쿼리라고 말할 수 있습니다.

실행(CPU 바인딩된) 쿼리의 예:

경과된 시간(밀리초)	CPU 시간(밀리초)	읽기(논리적)
3200	3000	300000
1080	1000	20

캐시에서 데이터/인덱스 페이지를 읽는 논리적 읽기는 SQL Server에서 CPU 사용률의 동인인 경우가 가장 많습니다. CPU 사용은 T-SQL 또는 XProcs 또는 SQL CRL 개체와 같은 다른 코드의 while 루프와 같은 다른 원본에서 제공될 수 있습니다. 표의 두 번째 예제에서는 대부분의 CPU가 읽기에서 사용되지 않는 이러한 시나리오를 보여 줍니다.

참고 항목

CPU 시간이 기간보다 크면 병렬 쿼리가 실행됨을 나타냅니다. 여러 스레드가 동시에 CPU를 사용하고 있습니다. 자세한 내용은 병렬 쿼리(실행기 또는 웨이터)를 참조 하세요.

유형 2: 병목 상태 대기 중(웨이터)

경과된 시간이 CPU 시간보다 훨씬 큰 경우 쿼리가 병목 상태를 기다리고 있습니다. 경과된 시간에는 CPU에서 쿼리를 실행하는 시간(CPU 시간) 및 리소스가 해제될 때까지 기다리는 시간(대기 시간)이 포함됩니다. 예를 들어 경과된 시간이 2000ms이고 CPU 시간이 300ms인 경우 대기 시간은 1700ms(2000 - 300 = 1700)입니다. 자세한 내용은 대기 유형을 참조 하세요.

대기 중인 쿼리의 예:

경과된 시간(밀리초)	CPU 시간(밀리초)	읽기(논리적)
2000	300	28000
10080	700	80000

병렬 쿼리 - 실행기 또는 웨이터

병렬 쿼리는 전체 기간보다 더 많은 CPU 시간을 사용할 수 있습니다. 병렬 처리의 목표는 여러 스레드가 쿼리의 일부를 동시에 실행할 수 있도록 하는 것입니다. 1초의 클록 시간에서 쿼리는 8개의 병렬 스레드를 실행하여 8초의 CPU 시간을 사용할 수 있습니다. 따라서 경과된 시간 및 CPU 시간 차이에 따라 CPU 바인딩 또는 대기 쿼리를 결정하는 것이 어려워집니다. 그러나 일반적으로 위의 두 섹션에 나열된 원칙을 따릅니다. 요약은 다음과 같습니다.

• 경과된 시간이 CPU 시간보다 훨씬 큰 경우 이를 웨이터로 간주합니다.
• CPU 시간이 경과된 시간보다 훨씬 큰 경우 실행기를 고려합니다.

병렬 쿼리의 예:

경과된 시간(밀리초)	CPU 시간(밀리초)	읽기(논리적)
1200	8100	850000
3080	12300	1500000

3단계: 두 서버의 데이터 비교, 시나리오 파악 및 문제 해결

Server1과 Server2라는 두 대의 컴퓨터가 있다고 가정해 보겠습니다. 또한 Server1에서 Server2보다 쿼리가 느리게 실행됩니다. 두 서버의 시간을 비교한 다음 다음 섹션에서 가장 일치하는 시나리오의 작업을 따릅니다.

시나리오 1: Server1의 쿼리는 CPU 시간을 더 많이 사용하며 Server1에서는 Server2보다 논리적 읽기가 더 높습니다.

Server1의 CPU 시간이 Server2보다 훨씬 크고 경과된 시간이 두 서버의 CPU 시간과 밀접하게 일치하는 경우 주요 대기 또는 병목 현상이 발생하지 않습니다. Server1의 CPU 시간 증가는 논리적 읽기의 증가로 인해 발생할 가능성이 높습니다. 논리적 읽기의 중요한 변경은 일반적으로 쿼리 계획의 차이를 나타냅니다. 예시:

서버	경과된 시간(밀리초)	CPU 시간(밀리초)	읽기(논리적)
Server1	3100	3000	300000
Server2	1100	1000	90200

작업: 실행 계획 및 환경 확인

1. 두 서버에서 쿼리의 실행 계획을 비교합니다. 이렇게 하려면 다음 두 가지 방법 중 하나를 사용합니다.
   • 실행 계획을 시각적으로 비교합니다. 자세한 내용은 실제 실행 계획 표시를 참조하세요.
   • 실행 계획을 저장하고 SQL Server Management Studio 계획 비교 기능을 사용하여 비교합니다.
2. 환경을 비교합니다. 환경이 다르면 쿼리 계획 차이 또는 CPU 사용량의 직접적인 차이가 발생할 수 있습니다. 환경에는 서버 버전, 데이터베이스 또는 서버 구성 설정, 추적 플래그, CPU 수 또는 클록 속도, Virtual Machine과 물리적 컴퓨터가 포함됩니다. 자세한 내용은 쿼리 계획 차이점 진단을 참조 하세요 .

시나리오 2: 쿼리는 Server1의 웨이터이지만 Server2에는 없습니다.

두 서버의 쿼리에 대한 CPU 시간이 비슷하지만 Server1의 경과 시간이 Server2보다 훨씬 큰 경우 Server1의 쿼리는 병목 상태를 기다리는 데 훨씬 더 많은 시간을 소비합니다. 예시:

서버	경과된 시간(밀리초)	CPU 시간(밀리초)	읽기(논리적)
Server1	4500	1000	90200
Server2	1100	1000	90200

• Server1 대기 시간: 4500 - 1000 = 3500ms
• Server2 대기 시간: 1100 - 1000 = 100ms

작업: Server1에서 대기 유형 확인

Server1에서 병목 상태를 식별하고 제거합니다. 대기의 예로는 차단(잠금 대기), 래치 대기, 디스크 I/O 대기, 네트워크 대기 및 메모리 대기가 있습니다. 일반적인 병목 상태 문제를 해결하려면 대기 또는 병목 상태 진단으로 진행합니다.

시나리오 3: 두 서버의 쿼리는 웨이터이지만 대기 유형 또는 시간은 다릅니다.

예시:

서버	경과된 시간(밀리초)	CPU 시간(밀리초)	읽기(논리적)
Server1	8000	1000	90200
Server2	3000	1000	90200

• Server1 대기 시간: 8000 - 1000 = 7000ms
• Server2 대기 시간: 3000 - 1000 = 2000ms

이 경우 CPU 시간은 두 서버에서 비슷하며 쿼리 계획이 동일할 가능성이 있음을 나타냅니다. 병목 현상이 발생할 때까지 기다리지 않으면 두 서버 모두에서 쿼리가 동일하게 수행됩니다. 따라서 기간 차이는 서로 다른 대기 시간에서 비롯됩니다. 예를 들어 쿼리는 Server1에서 7000ms의 잠금을 기다리는 동안 Server2의 I/O에서 2000ms를 기다립니다.

작업: 두 서버에서 대기 유형 확인

각 서버에서 개별적으로 대기하는 각 병목 상태를 해결하고 두 서버에서 실행 속도를 향상합니다. 두 서버에서 병목 상태를 제거하고 성능을 비교할 수 있도록 해야 하기 때문에 이 문제를 해결하는 것은 노동 집약적입니다. 일반적인 병목 상태 문제를 해결하려면 대기 또는 병목 상태 진단으로 진행합니다.

시나리오 4: Server1의 쿼리는 Server2보다 더 많은 CPU 시간을 사용하지만 논리적 읽기가 닫힙니다.

예시:

서버	경과된 시간(밀리초)	CPU 시간(밀리초)	읽기(논리적)
Server1	3000	3000	90200
Server2	1000	1000	90200

데이터가 다음 조건과 일치하는 경우:

• Server1의 CPU 시간은 Server2보다 훨씬 큽 수 있습니다.
• 경과된 시간은 각 서버의 CPU 시간과 밀접하게 일치하며 대기하지 않음을 나타냅니다.
• 일반적으로 CPU 시간의 가장 높은 드라이버인 논리적 읽기는 두 서버 모두에서 유사합니다.

그런 다음 추가 CPU 시간은 다른 CPU 바인딩된 작업에서 가져옵니다. 이 시나리오는 모든 시나리오에서 가장 드문 시나리오입니다.

원인: 추적, UDF 및 CLR 통합

이 문제는 다음으로 인해 발생할 수 있습니다.

• XEvents/SQL Server 추적, 특히 텍스트 열(데이터베이스 이름, 로그인 이름, 쿼리 텍스트 등)에 대한 필터링을 사용합니다. 한 서버에서 추적을 사용하도록 설정했지만 다른 서버에서는 사용하도록 설정하지 않은 경우 이것이 그 차이의 원인일 수 있습니다.
• CPU 바인딩된 작업을 수행하는 UDF(사용자 정의 함수) 또는 기타 T-SQL 코드입니다. 이는 일반적으로 데이터 크기, CPU 클록 속도 또는 전원 계획과 같은 Server1 및 Server2에서 다른 조건이 다른 경우의 원인입니다.
• CPU를 구동할 수 있지만 논리적 읽기를 수행하지 않는 SQL Server CLR 통합 또는 XP(확장 저장 프로시저) . DLL의 차이로 인해 CPU 시간이 다를 수 있습니다.
• CPU 바인딩된 SQL Server 기능의 차이(예: 문자열 조작 코드).

작업: 추적 및 쿼리 확인

1. 두 서버에서 추적을 확인하여 다음을 수행합니다.

   1. Server1에서 사용하도록 설정된 추적이 있지만 Server2에서는 사용하도록 설정되지 않은 경우
   2. 추적을 사용하는 경우 추적을 사용하지 않도록 설정하고 Server1에서 쿼리를 다시 실행합니다.
   3. 이번에는 쿼리가 더 빠르게 실행되는 경우 추적을 다시 사용하도록 설정하지만 텍스트 필터가 있는 경우 제거합니다.

2. 쿼리에서 문자열 조작을 수행하는 UDF를 사용하거나 목록의 데이터 열에 대해 광범위한 처리를 수행하는지 확인합니다 SELECT .

3. 쿼리에 루프, 함수 재귀 또는 중첩이 포함되어 있는지 확인합니다.

환경 차이 진단

다음 질문을 확인하고 두 서버 간의 비교가 유효한지 확인합니다.

• 두 SQL Server 인스턴스가 동일한 버전 또는 빌드인가요?

  그렇지 않은 경우 차이를 발생시킨 몇 가지 수정 사항이 있을 수 있습니다. 다음 쿼리를 실행하여 두 서버에서 버전 정보를 가져옵니다.

  SELECT @@VERSION
  

• 두 서버에서 실제 메모리의 양이 유사합니까?

  한 서버에 64GB의 메모리가 있는 반면 다른 서버에는 256GB의 메모리가 있는 경우 이는 상당한 차이가 있을 것입니다. 데이터/인덱스 페이지 및 쿼리 계획을 캐시하는 데 사용할 수 있는 메모리가 늘어나면 하드웨어 리소스 가용성에 따라 쿼리를 다르게 최적화할 수 있습니다.

• 두 서버에서 CPU 관련 하드웨어 구성이 유사합니까? 예시:

  • CPU 수는 컴퓨터마다 다릅니다(한 컴퓨터의 CPU 24개와 다른 컴퓨터의 CPU 96개).

  • 전원 요금제- 균형 잡힌 성능과 고성능

  • VM(Virtual Machine) 및 물리적(운영 체제 미설치) 머신

  • Hyper-V와 VMware의 차이점- 구성의 차이입니다.

  • 클록 속도 차이(낮은 클록 속도와 더 높은 클록 속도). 예를 들어 2GHz와 3.5GHz는 차이를 만들 수 있습니다. 서버에서 클록 속도를 얻으려면 다음 PowerShell 명령을 실행합니다.

    Get-CimInstance Win32_Processor | Select-Object -Expand MaxClockSpeed
    

  다음 두 가지 방법 중 하나를 사용하여 서버의 CPU 속도를 테스트합니다. 비교 가능한 결과를 생성하지 않는 경우 문제는 SQL Server 외부에 있습니다. 전원 계획 차이, CPU 감소, VM 소프트웨어 문제 또는 클록 속도 차이일 수 있습니다.

  • 두 서버에서 다음 PowerShell 스크립트를 실행하고 출력을 비교합니다.

    $bf = [System.DateTime]::Now
    for ($i = 0; $i -le 20000000; $i++) {}
    $af = [System.DateTime]::Now
    Write-Host ($af - $bf).Milliseconds " milliseconds"
    Write-Host ($af - $bf).Seconds " Seconds"
    

  • 두 서버에서 다음 Transact-SQL 코드를 실행하고 출력을 비교합니다.

    SET NOCOUNT ON 
    DECLARE @spins INT = 0
    DECLARE @start_time DATETIME = GETDATE(), @time_millisecond INT
    
    WHILE (@spins < 20000000)
    BEGIN
       SET @spins = @spins +1
    END
    
    SELECT @time_millisecond = DATEDIFF(millisecond, @start_time, getdate())
    
    SELECT @spins Spins, @time_millisecond Time_ms,  @spins / @time_millisecond Spins_Per_ms
    

대기 또는 병목 상태 진단

병목 상태를 기다리는 쿼리를 최적화하려면 대기 시간 및 병목 현상이 있는 위치(대기 유형)를 식별합니다. 대기 유형이 확인되면 대기 시간을 줄이거나 대기를 완전히 제거합니다.

대략적인 대기 시간을 계산하려면 쿼리의 경과된 시간에서 CPU 시간(작업자 시간)을 뺍니다. 일반적으로 CPU 시간은 실제 실행 시간이며 쿼리 수명 중 나머지 부분은 대기 중입니다.

대략적 대기 기간을 계산하는 방법의 예:

경과된 시간(밀리초)	CPU 시간(밀리초)	대기 시간(밀리초)
3200	3000	200
7080	1000	6080

병목 상태 식별 또는 대기

• 기록 대기 대기 쿼리를 식별하려면(예 >: 전체 경과 시간의 20%가 대기 시간임) 다음 쿼리를 실행합니다. 이 쿼리는 SQL Server가 시작된 이후 캐시된 쿼리 계획에 대한 성능 통계를 사용합니다.

  SELECT t.text,
           qs.total_elapsed_time / qs.execution_count
           AS avg_elapsed_time,
           qs.total_worker_time / qs.execution_count
           AS avg_cpu_time,
           (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count
           AS avg_wait_time,
           qs.total_logical_reads / qs.execution_count
           AS avg_logical_reads,
           qs.total_logical_writes / qs.execution_count
           AS avg_writes,
           qs.total_elapsed_time
           AS cumulative_elapsed_time
  FROM sys.dm_exec_query_stats qs
           CROSS apply sys.Dm_exec_sql_text (sql_handle) t
  WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time
           > 0.2
  ORDER BY qs.total_elapsed_time / qs.execution_count DESC
  

• 대기 시간이 500ms보다 긴 현재 실행 중인 쿼리를 식별하려면 다음 쿼리를 실행합니다.

  SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms
  FROM sys.dm_exec_requests r 
     JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id 
  WHERE wait_time > 500
  AND is_user_process = 1
  

• 쿼리 계획을 수집할 수 있는 경우 SSMS의 실행 계획 속성에서 WaitStats를 확인합니다.

  1. 실제 실행 계획 포함을 사용하여 쿼리를 실행합니다.
  2. 실행 계획 탭에서 가장 왼쪽에 있는 연산자를 마우스 오른쪽 단추로 클릭합니다.
  3. 속성을 선택한 다음 WaitStats 속성을 선택합니다.
  4. WaitTimeMs 및 WaitType을 확인합니다.

• PSSDiag/SQLdiag 또는 SQL LogScout LightPerf/GeneralPerf 시나리오에 익숙한 경우 둘 중 하나를 사용하여 성능 통계를 수집하고 SQL Server 인스턴스에서 대기 중인 쿼리를 식별하는 것이 좋습니다. 수집된 데이터 파일을 가져오고 SQL Nexus를 사용하여 성능 데이터를 분석할 수 있습니다.

대기를 제거하거나 줄이는 데 도움이 되는 참조

각 대기 유형에 대한 원인과 해결 방법은 다양합니다. 모든 대기 유형을 확인하는 일반적인 방법은 없습니다. 일반적인 대기 유형 문제를 해결하고 해결하기 위한 문서는 다음과 같습니다.

• 차단 문제 이해 및 해결(LCK_M_*)
• Azure SQL 데이터베이스 차단 문제의 이해 및 해결
• I/O 문제로 인한 느린 SQL Server 성능 문제 해결(PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
• SQL Server에서 마지막 페이지 삽입 PAGELATCH_EX 경합 해결
• 메모리에서 설명 및 솔루션 부여(RESOURCE_SEMAPHORE)
• ASYNC_NETWORK_IO 대기 유형으로 인해 발생하는 느린 쿼리 문제 해결
• Always On 가용성 그룹을 사용하여 높은 HADR_SYNC_COMMIT 대기 유형 문제 해결
• 작동 방식: CMEMTHREAD 및 디버깅
• 병렬 처리 대기 실행 가능(CXPACKET 및 CXCONSUMER) 만들기
• THREADPOOL 대기

많은 대기 유형 및 해당 형식이 나타내는 내용에 대한 설명은 대기 유형에서 표를 참조하세요.

쿼리 계획 차이점 진단

다음은 쿼리 계획의 차이점에 대한 몇 가지 일반적인 원인입니다.

• 데이터 크기 또는 데이터 값 차이

  동일한 데이터베이스 백업을 사용하여 두 서버에서 동일한 데이터베이스를 사용하고 있나요? 한 서버에서 다른 서버에 비해 데이터가 수정되었나요? 데이터 차이로 인해 쿼리 계획이 달라질 수 있습니다. 예를 들어 T1 테이블(1000개 행)을 테이블 T2(2,000,000개 행)와 조인하는 것은 T1 테이블(100개 행)을 테이블 T2(2,000,000개 행)와 조인하는 경우와 다릅니다. 작업의 형식과 속도는 JOIN 크게 다를 수 있습니다.

• 통계 차이

  다른 데이터베이스가 아닌 한 데이터베이스에서 통계가 업데이트되었나요? 통계가 다른 샘플 속도(예: 30% 대 100% 전체 검사)로 업데이트되었나요? 동일한 샘플 속도로 양쪽에서 통계를 업데이트해야 합니다.

• 데이터베이스 호환성 수준 차이점

  데이터베이스의 호환성 수준이 두 서버 간에 다른지 확인합니다. 데이터베이스 호환성 수준을 얻으려면 다음 쿼리를 실행합니다.

  SELECT name, compatibility_level
  FROM sys.databases
  WHERE name = '<YourDatabase>'
  

• 서버 버전/빌드 차이점

  SQL Server의 버전 또는 빌드가 두 서버 간에 다른가요? 예를 들어 하나의 서버 SQL Server 버전 2014 및 다른 SQL Server 버전 2016인가요? 쿼리 계획을 선택하는 방법이 변경될 수 있는 제품 변경이 있을 수 있습니다. SQL Server의 동일한 버전과 빌드를 비교해야 합니다.

  SELECT ServerProperty('ProductVersion')
  

• CE(카디널리티 추정기) 버전 차이

  레거시 카디널리티 추정기가 데이터베이스 수준에서 활성화되었는지 확인합니다. CE에 대한 자세한 내용은 카디널리티 예측(SQL Server)을 참조하세요.

  SELECT name, value, is_value_default
  FROM sys.database_scoped_configurations
  WHERE name = 'LEGACY_CARDINALITY_ESTIMATION'
  

• 최적화 프로그램 핫픽스 사용/사용 안 함

  쿼리 최적화 프로그램 핫픽스가 한 서버에서 사용하도록 설정되어 있지만 다른 서버에서는 사용하지 않도록 설정된 경우 다른 쿼리 계획을 생성할 수 있습니다. 자세한 내용은 SQL Server 쿼리 최적화 프로그램 핫픽스 추적 플래그 4199 서비스 모델을 참조하세요.

  쿼리 최적화 프로그램 핫픽스의 상태를 가져오려면 다음 쿼리를 실행합니다.

  -- Check at server level for TF 4199
  DBCC TRACESTATUS (-1)
  -- Check at database level
  USE <YourDatabase>
  SELECT name, value, is_value_default 
  FROM sys.database_scoped_configurations
  WHERE name = 'QUERY_OPTIMIZER_HOTFIXES'
  

• 추적 플래그 차이

  일부 추적 플래그는 쿼리 계획 선택에 영향을 미칩니다. 한 서버에서 다른 서버에서 사용하도록 설정되지 않은 추적 플래그가 활성화되어 있는지 확인합니다. 두 서버에서 다음 쿼리를 실행하고 결과를 비교합니다.

  -- Check at server level for trace flags
  DBCC TRACESTATUS (-1)
  

• 하드웨어 차이점(CPU 수, 메모리 크기)

  하드웨어 정보를 얻으려면 다음 쿼리를 실행합니다.

  SELECT cpu_count, physical_memory_kb/1024/1024 PhysicalMemory_GB 
  FROM sys.dm_os_sys_info
  

• 쿼리 최적화 프로그램별 하드웨어 차이점

  OptimizerHardwareDependentProperties 쿼리 계획을 확인하고 하드웨어 차이점이 다른 계획에 중요한 것으로 간주되는지 확인합니다.

  WITH xmlnamespaces(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
  SELECT
    txt.text,
    t.OptHardw.value('@EstimatedAvailableMemoryGrant', 'INT') AS EstimatedAvailableMemoryGrant , 
    t.OptHardw.value('@EstimatedPagesCached', 'INT') AS EstimatedPagesCached, 
    t.OptHardw.value('@EstimatedAvailableDegreeOfParallelism', 'INT') AS EstimatedAvailDegreeOfParallelism,
    t.OptHardw.value('@MaxCompileMemory', 'INT') AS MaxCompileMemory
  FROM sys.dm_exec_cached_plans AS cp
  CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
  CROSS APPLY qp.query_plan.nodes('//OptimizerHardwareDependentProperties') AS t(OptHardw)
  CROSS APPLY sys.dm_exec_sql_text (CP.plan_handle) txt
  WHERE text Like '%<Part of Your Query>%'
  

• 최적화 프로그램 시간 제한

  최적화 프로그램 시간 제한 문제가 있나요? 실행 중인 쿼리가 너무 복잡하면 쿼리 최적화 프로그램에서 계획 옵션 평가를 중지할 수 있습니다. 중지되면 당시 사용 가능한 비용이 가장 낮은 계획을 선택합니다. 이로 인해 한 서버와 다른 서버에서 임의 계획 선택처럼 보일 수 있습니다.

• SET 옵션

  SET ARITHABORT와 같은 일부 SET 옵션은 계획에 영향을 미칩니다. 자세한 내용은 SET 옵션을 참조 하세요.

• 쿼리 힌트 차이점

  한 쿼리는 쿼리 힌트를 사용하고 다른 쿼리는 사용하지 않나요? 쿼리 텍스트를 수동으로 확인하여 쿼리 힌트가 있는지 확인합니다.

• 매개 변수 구분 계획(매개 변수 스니핑 문제)

  정확히 동일한 매개 변수 값으로 쿼리를 테스트하고 있나요? 그렇지 않은 경우 거기에서 시작할 수 있습니다. 계획이 다른 매개 변수 값을 기반으로 한 서버에서 이전에 컴파일되었나요? RECOMPILE 쿼리 힌트를 사용하여 두 쿼리를 테스트하여 계획 재사용이 수행되지 않았는지 확인합니다. 자세한 내용은 매개 변수에 민감한 문제 조사 및 해결을 참조하세요.

• 다른 데이터베이스 옵션/범위 구성 설정

  동일한 데이터베이스 옵션 또는 범위가 지정된 구성 설정이 두 서버에서 모두 사용합니까? 일부 데이터베이스 옵션은 계획 선택에 영향을 줄 수 있습니다. 예를 들어 데이터베이스 호환성, 레거시 CE 및 기본 CE 및 매개 변수 스니핑이 있습니다. 한 서버에서 다음 쿼리를 실행하여 두 서버에 사용되는 데이터베이스 옵션을 비교합니다.

  -- On Server1 add a linked server to Server2 
  EXEC master.dbo.sp_addlinkedserver @server = N'Server2', @srvproduct=N'SQL Server'
  
  -- Run a join between the two servers to compare settings side by side
  SELECT 
     s1.name AS srv1_config_name, 
     s2.name AS srv2_config_name,
     s1.value_in_use AS srv1_value_in_use, 
     s2.value_in_use AS srv2_value_in_use, 
     Variance = CASE WHEN ISNULL(s1.value_in_use, '##') != ISNULL(s2.value_in_use,'##') THEN 'Different' ELSE '' END
  FROM sys.configurations s1 
  FULL OUTER JOIN [server2].master.sys.configurations s2 ON s1.name = s2.name
  
  
  SELECT 
     s1.name AS srv1_config_name,
     s2.name AS srv2_config_name,
     s1.value srv1_value_in_use,
     s2.value srv2_value_in_use,
     s1.is_value_default,
     s2.is_value_default,
     Variance = CASE WHEN ISNULL(s1.value, '##') != ISNULL(s2.value, '##') THEN 'Different' ELSE '' END
  FROM sys.database_scoped_configurations s1
  FULL OUTER JOIN [server2].master.sys.database_scoped_configurations s2 ON s1.name = s2.name
  

• 계획 지침

  계획 지침이 한 서버의 쿼리에 사용되지만 다른 서버에는 사용되지 않나요? 다음 쿼리를 실행하여 차이점을 설정합니다.

  SELECT * FROM sys.plan_guides