SQL Server에서 끝나지 않는 것처럼 보이는 쿼리 문제 해결

아티클
01/17/2025

이 문서에서는 완료되지 않는 쿼리가 있거나 완료하는 데 몇 시간 또는 며칠이 걸릴 수 있는 문제에 대한 문제 해결 단계를 설명합니다.

끝 없는 쿼리란?

이 문서에서는 계속 실행하거나 컴파일하는 쿼리, 즉 CPU가 계속 증가하는 쿼리에 중점을 둡니다. 차단되거나 릴리스되지 않은 일부 리소스를 기다리는 쿼리에는 적용되지 않습니다(CPU는 일정하게 유지되거나 거의 변경되지 않음).

Important

쿼리가 실행을 완료하도록 남아 있으면 결국 완료됩니다. 몇 초밖에 걸리지 않거나 며칠이 걸릴 수 있습니다.

끝 없음이라는 용어는 쿼리가 실제로 완료될 때 완료되지 않는 쿼리의 인식을 설명하는 데 사용됩니다.

끝없는 쿼리 식별

쿼리가 병목 현상에서 지속적으로 실행 중인지 또는 중단되는지 확인하려면 다음 단계를 수행합니다.

다음 쿼리를 실행합니다.

DECLARE @cntr int = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    r.cpu_time,
                    r.logical_reads,
                    r.reads,
                    r.writes,
                    r.total_elapsed_time / (1000 * 60) 'Elaps M',
                    SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
                    ((CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(st.TEXT)
                        ELSE r.statement_end_offset
                    END - r.statement_start_offset) / 2) + 1) AS statement_text,
                    COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
                    + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
                    r.command,
                    s.login_name,
                    s.host_name,
                    s.program_name,
                    s.last_request_end_time,
                    s.login_time,
                    r.open_transaction_count,
                    atrn.name as transaction_name,
                    atrn.transaction_id,
                    atrn.transaction_state
        FROM sys.dm_exec_sessions AS s
        JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id 
                CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
        LEFT JOIN (sys.dm_tran_session_transactions AS stran 
             JOIN sys.dm_tran_active_transactions AS atrn
                ON stran.transaction_id = atrn.transaction_id)
        ON stran.session_id =s.session_id
        WHERE r.session_id != @@SPID
        ORDER BY r.cpu_time DESC

    SET @cntr = @cntr + 1
WAITFOR DELAY '00:00:05'
END

샘플 출력을 확인합니다.
- 이 문서의 문제 해결 단계는 중요한 대기 시간 없이 CPU가 경과된 시간에 비례하여 증가하는 다음과 유사한 출력을 확인할 때 특히 적용됩니다. 일부 CPU 바인딩 T-SQL 요청이 논리 읽기를 전혀 수행하지 않을 수 있으므로 변경 내용은 logical_reads 이 경우 관련이 없습니다(예: 계산 또는 WHILE 루프 수행).
  
  session_id status cpu_time logical_reads wait_time wait_type
  
  56 실행 중 7038 101000 0 NULL
  
  56 실행 가능 12040 301000 0 NULL
  
  56 실행 중 17020 523000 0 NULL
- CPU가 약간 변경되지 않거나 약간 변경되지 않고 세션이 리소스에서 대기 중인 다음과 유사한 대기 시나리오를 관찰하는 경우 이 문서는 적용되지 않습니다.
  
  session_id status cpu_time logical_reads wait_time wait_type
  
  56 suspended 0 3 8312 LCK_M_U
  
  56 suspended 0 3 13318 LCK_M_U
  
  56 suspended 0 5 18331 LCK_M_U
자세한 내용은 대기 또는 병목 상태 진단을 참조 하세요.

session_id	status	cpu_time	logical_reads	wait_type
56	실행 중	7038	101000	NULL
56	실행 가능	12040	301000	NULL
56	실행 중	17020	523000	NULL

session_id	status	logical_reads	wait_time	wait_type
56	suspended	3	8312	LCK_M_U
56	suspended	3	13318	LCK_M_U
56	suspended	5	18331	LCK_M_U

긴 컴파일 시간

드문 경우지만 시간이 지남에 따라 CPU가 지속적으로 증가하지만 쿼리 실행에 의해 구동되지 않는 것을 관찰할 수 있습니다. 대신 지나치게 긴 컴파일(쿼리의 구문 분석 및 컴파일)에 의해 구동될 수 있습니다. 이 경우 transaction_name 출력 열을 확인하고 값을 sqlsource_transform찾습니다. 이 트랜잭션 이름은 컴파일을 나타냅니다.

진단 데이터 수집

SSMS(SQL Server Management Studio)를 사용하여 진단 데이터를 수집하려면 다음 단계를 수행합니다.

예상 쿼리 실행 계획 XML을 캡처합니다.
쿼리 계획을 검토하여 속도가 느려질 수 있는 위치에 대한 명백한 징후가 있는지 확인합니다. 일반적인 예는 다음과 같습니다.
- 테이블 또는 인덱스 검색(예상 행 보기).
- 거대한 외부 테이블 데이터 집합에 의해 구동되는 중첩된 루프입니다.
- 루프의 안쪽에 큰 분기가 있는 중첩된 루프입니다.
- 테이블 스풀.
- 각 행을 SELECT 처리하는 데 시간이 오래 걸리는 목록의 함수입니다.
쿼리가 언제든지 빠르게 실행되는 경우 비교할 "빠른" 실행 실제 XML 실행 계획을 캡처할 수 있습니다.

경량 쿼리 프로파일링 인프라 는 이러한 버전의 SQL Server에서 도입되었습니다. 느린 쿼리를 실행하는 동안 실제 통계를 캡처할 수 있습니다. 이 문제 해결 기능을 사용하면 런타임에 쿼리 계획에서 쿼리 연산자를 검사하고 대부분의 시간이 쿼리에 소요되는 위치를 파악할 수 있습니다.

인프라 v1을 프로파일링하는 간단한 쿼리 실행 통계를 사용하여 쿼리의 느린 단계를 식별하려면 다음 단계를 수행합니다.

다음 명령을 실행하여 XEvent를 query_thread_profile 사용하도록 설정합니다.

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = START

애플리케이션에서 영향을 받는 끝 없는 쿼리를 시작합니다.

다음 명령을 분 단위로 여러 번 실행하여 쿼리 계획 연산자의 런타임 실행 통계를 확인합니다.

SELECT CONVERT (varchar(30), getdate(), 126) as runtime,
            qp.session_id,
            convert(nvarchar(48), qp.physical_operator_name) as physical_operator_name,
            qp.row_count,
            qp.estimate_row_count,
            qp.node_id,
            req.cpu_time,
            req.total_elapsed_time,
            substring
            (REPLACE
            (REPLACE
                (SUBSTRING
                (SQLText.text
                , (req.statement_start_offset/2) + 1
                , (
                    (CASE statement_END_offset
                        WHEN -1
                        THEN DATALENGTH(SQLText.text)  
                        ELSE req.statement_END_offset
                        END
                        - req.statement_start_offset)/2) + 1)
            , CHAR(10), ' '), CHAR(13), ' '), 1, 512)  AS active_statement_text
FROM sys.dm_exec_query_profiles qp 
RIGHT OUTER JOIN sys.dm_exec_requests req
    ON qp.session_id = req.session_id
LEFT OUTER JOIN sys.dm_exec_sessions sess
    on req.session_id = sess.session_id
LEFT OUTER JOIN sys.dm_exec_connections conn on conn.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text (ISNULL (req.sql_handle, conn.most_recent_sql_handle)) as SQLText
WHERE req.session_id <> @@SPID 
    AND sess.is_user_process = 1 
ORDER BY qp.session_id asc, row_count desc 
--this is to prevent massive grants
OPTION (max_grant_percent = 3, MAXDOP 1)

분석을 위한 충분한 데이터를 제공하기 위해 1분 간격으로 3~4개의 스냅샷을 캡처합니다. 특히 시간이 지남에 따라 각 연산자의 숫자를 비교 row_count 하고 행 수(백만 개 이상)가 크게 증가한 것을 확인할 수 있습니다.
SSMS의 새 쿼리 창에서 다음 명령을 실행하여 문제 쿼리에 대한 예상 쿼리 계획을 캡처합니다.
```
SET SHOWPLAN_XML ON
GO
<problem query here>
GO
SET SHOWPLAN_XML OFF
```
3단계에서 쿼리로 식별되는 행 수가 가장 높은 노드 ID를 사용하여 예상 쿼리 계획에서 동일한 노드를 찾습니다. 이 단계는 계획에서 긴 실행 시간의 주요 원인인 연산자를 이해하는 데 도움이 됩니다.

다음 명령을 실행하여 XEvent를 중지합니다.

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP

간단한 쿼리 실행 통계 프로파일링 인프라 v2를 사용하여 행 수에 대한 실제 값으로 라이브 쿼리 계획을 캡처할 수 있습니다. 이 프로파일링 인프라를 사용하면 런타임에 쿼리 계획에서 쿼리 연산자를 검사하고 대부분의 시간이 쿼리에 소요되는 위치를 이해할 수 있습니다.

쿼리의 느린 단계를 식별하려면 다음 단계를 수행합니다.

이러한 버전의 SQL Server에서 경량 인프라를 사용하도록 설정하려면 다음 방법 중 하나를 사용합니다.

다음 명령을 실행하여 추적 플래그 7412를 사용하도록 설정합니다.
```
DBCC TRACEON (7412, -1)
```

또는 다음 명령을 실행하여 XEvent를 사용하도록 설정합니다 query_thread_profile .

CREATE EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
 ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
   sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
   sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
   sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
 EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY=30 SECONDS,
 MAX_EVENT_SIZE=0 KB,
 MEMORY_PARTITION_MODE=NONE,
 TRACK_CAUSALITY=OFF,
 STARTUP_STATE=OFF);

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = START

애플리케이션에서 영향을 받는 끝 없는 쿼리를 시작합니다.

다음 명령과 유사한 명령을 사용하여 실행 중인 끝 없는 쿼리를 Session_id 식별합니다.

SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t

다음 명령을 1분 간격으로 서너 번 실행하여 계획의 쿼리 계획 및 실제 통계를 검사합니다. 매번 쿼리 계획을 저장해야 하므로 쿼리 계획을 비교하고 대부분의 CPU 시간을 소비하는 쿼리 연산자를 설정할 수 있습니다. 특히 시간에 따른 각 연산자의 행 개수(실제 행 수)를 비교하고 행 개수(백만 개 이상)가 크게 증가한 연산자를 확인할 수 있습니다. 이전 3단계에서 찾은 정수 값으로 바꿉 <session_id> 다.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
XEvent를 시작한 경우 중지하거나 추적 플래그를 사용하지 않도록 설정합니다.
```
ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = STOP
-- or
DBCC TRACEOFF (7412, -1)
```

간단한 쿼리 실행 통계 프로파일링 인프라 v3을 사용하여 행 수에 대한 실제 값으로 라이브 쿼리 계획을 캡처할 수 있습니다. 이 프로파일링 인프라를 사용하면 런타임에 쿼리 계획에서 쿼리 연산자를 검사하고 대부분의 시간이 쿼리에 소요되는 위치를 이해할 수 있습니다. 경량 프로파일링은 SQL Server 2019에서 기본적으로 사용하도록 설정됩니다.

쿼리의 느린 단계를 식별하려면 다음 단계를 수행합니다.

애플리케이션에서 영향을 받는 끝 없는 쿼리를 시작합니다.

다음 명령과 유사한 명령을 사용하여 실행 중인 끝 없는 쿼리를 Session_id 식별합니다.

SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t

다음 명령을 서너 번 실행하여 계획의 쿼리 계획 및 실제 통계를 검사합니다. 매번 쿼리 계획을 저장해야 하므로 쿼리 계획을 비교하고 대부분의 CPU 시간을 소비하는 쿼리 연산자를 설정할 수 있습니다. 이전 3단계에서 찾은 정수 값으로 바꿉 <session_id> 다.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
특히 query_plan 열 아래에서 XML 링크를 선택합니다. 그래픽 쿼리 계획이 새 창에서 열리면 마우스 오른쪽 단추로 클릭하고 실행 계획을 다른 이름으로 저장...을 선택합니다. 분석을 위한 충분한 데이터를 제공하기 위해 1분 간격으로 3~4개의 스냅샷을 캡처하는 단계를 반복합니다. 특히 시간에 따른 각 연산자의 행 개수(실제 행 수)를 비교하고 행 개수(백만 개 이상)가 크게 증가한 연산자를 확인할 수 있습니다.
참고 항목

출력 sys.dm_exec_query_statistics_xml을 가져오지 않는 경우 다음 명령을 실행하여 데이터베이스 옵션이 LAST_QUERY_PLAN_STATS 비활성화되었는지 확인할 수 있습니다.
```
SELECT name, value, value_for_secondary, is_value_default 
FROM sys.database_scoped_configurations
WHERE name = 'LAST_QUERY_PLAN_STATS'
```
를 실행 ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON하여 데이터베이스 수준에서 마지막 쿼리 계획 통계를 사용하도록 설정할 수 있습니다.

수집된 계획을 검토하는 방법

이 섹션에서는 수집된 데이터를 검토하는 방법을 보여 줍니다. SQL Server 2016 SP1 이상 빌드 및 버전에서 수집된 여러 XML 쿼리 계획(확장 *.sqlplan 사용)을 사용합니다.

실행 계획을 비교하려면 다음 단계를 수행합니다.

이전에 저장된 쿼리 실행 계획 파일(.sqlplan)을 엽니다.
실행 계획의 빈 영역을 마우스 오른쪽 단추로 클릭하고 실행 계획 비교를 선택합니다.
비교할 두 번째 쿼리 계획 파일을 선택합니다.
연산자 간에 많은 수의 행이 흐르고 있음을 나타내는 두꺼운 화살표를 찾습니다. 그런 다음 화살표 앞이나 뒤에 있는 연산자를 선택하고 두 계획의 실제 행 수를 비교합니다.
두 번째 계획과 세 번째 계획을 비교하여 동일한 연산자에서 행의 가장 큰 흐름이 발생하는지 확인합니다.

예를 들어 다음과 같습니다.

해결

쿼리에 사용되는 테이블에 대한 통계가 업데이트되었는지 확인합니다.
쿼리 계획에서 누락된 인덱스 권장 사항을 찾아 적용합니다.
쿼리를 단순화하기 위해 쿼리를 다시 작성합니다.
- 더 많은 선택적 WHERE 조건자를 사용하여 앞에서 처리된 데이터를 줄입니다.
- 그것을 분해.
- 일부 파트를 임시 테이블로 선택하고 나중에 조인합니다.
- EXISTSFAST 최적화 프로그램 행 목표로 인해 매우 오랜 시간 동안 실행되는 쿼리에서 (T-SQL) 및 (T-SQL)을 제거TOP합니다. 또는 힌트를 DISABLE_OPTIMIZER_ROWGOAL 사용할 수 있습니다. 자세한 내용은 행 목표 사라 도적을 참조 하세요.
- 이러한 경우에는 문을 단일 큰 쿼리로 결합하기 때문에 CTE(Common Table Expressions)를 사용하지 마세요.
쿼리 힌트를 사용하여 더 나은 계획을 만들어 보세요.
- HASH JOIN 또는 MERGE JOIN 힌트
- FORCE ORDER 힌트
- FORCESEEK 힌트
- RECOMPILE
- 강제할 수 있는 빠른 쿼리 계획이 있는 경우 USE PLAN N'<xml_plan>'
이러한 계획이 있고 SQL Server 버전이 쿼리 저장소 지원하는 경우 QDS(쿼리 저장소)를 사용하여 잘 알려진 계획을 강제로 적용합니다.

대기 또는 병목 상태 진단

이 섹션은 문제가 장기 실행 CPU 구동 쿼리가 아닌 경우 참조로 여기에 포함되어 있습니다. 대기로 인해 긴 쿼리 문제를 해결하는 데 사용할 수 있습니다.

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

대략적인 대기 시간을 계산하려면 쿼리의 경과된 시간에서 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를 사용하여 성능 데이터를 분석할 수 있습니다.

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

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

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

다음을 통해 공유