'sys.dm_os_wait_stats' 태그의 글 목록

azure sql, QPI, Query Store, SQL Server Managed Instance, sys.dm_os_wait_stats, sys.query_store_wait_stats

Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석

· Version : Azure SQL, SQL Server 2016 Later

대기 통계(Wait Statistics)는데이터베이스 엔진에서 무언가를 기다리는 쿼리를 식별하는데 도움이 되며 쿼리 지속시간이 긴 이유를 분석할 수 있는 정보를 나타낸다. 이번 포스트에서는 워크로드가 대기하는 이유와 일부 리소스에서 대기중인 쿼리를 식별하는 방법에 대해서 살펴본다.

Azure SQL Managed Instance를 사용하면 아래 DMV를 사용하여 쿼리가 리소스를 대기하는 이유를 찾을 수 있다.

· sys.dm_os_wait_stats : 인스턴스 레벨에서 대기 정보 반환

· sys.query_store_wait_stats : 데이터베이스 레벨에서 대기중인 쿼리의 실행계획 반환

이러한 정보는 DMO/Query Store를 사용하여 찾을 수 있다. 그러나 분석을 쉽게 하기 위해 무료 오픈소스인 QPI 라이브러리를 사용할 수 있다.

· QPI : https://github.com/JocaPC/qpi

QPI 라이브러리를 설치하려면 아래 링크에서 SQL Server 버전에 대한 SQL 스크립트를 다운로드 할 수 있다. Query Store에 의존하기 때문에 SQL Server 2016 이상, Azure SQL 에서 가능하다.

· QPI Installation : https://github.com/JocaPC/qpi#installation

sys.dm_os_wait_stats 는 인스턴스 시작 이후 또는 통계를 마지막으로 재설정 한 이후에 대기 통계를 수집하므로 대기 통계의 스냅샷을 작성하거나 최소한 재설정을 해야한다. 아래 스크립트를 실행하여 QPI에서대기 통계를 재설정 할 수 있다.

exec qpi.snapshot_wait_stats

이 절차는 인스턴스의 대기 통계를 재설정하고 관리형 인스턴스는 새 대기 통계를 수집을 시작한다. 워크로드가 실행되는 동안 qpi.wait_stats보기에서 대기 통계 정보를 읽을 수 있다. 결과를 보면 인스턴스의 기본 대기 통계는 INSTANCE_LOG_RATE_GOVERNON이며 Log Rate Governor로 분류 된다. 대기 유형의 영향을 받는 쿼리를 찾으려면 실제 대기 유형 이름이 아닌 쿼리 저정소에서 Category를 사용해야 하므로 Category가 중요하다. 쿼리 저장소의 대기 통계는 대기 유형별로 기록되지 않는다.

대기가 발생한 Category의 영향을 받는 상위 쿼리를 보려면 qpi.db_query_wait_stats 보기를 사용하여 Category에 해당하는 대기 통계를 필터링 할 수 있다.

예제의 경우 관리형 인스턴스에서 로그 속도 제한에 도달하고 있으며, 원인은 인덱스 재구축으로 인한것일 수 있다. 데이터베이스가 여러개인 경우 데이터베이스마다 쿼리 저장소가 구성되므로 각 데이터베이스에서 이 쿼리를 실행해야한다.

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/05/analyzing-wait-statistics-on-managed-instance/

2019-09-23/ Sungwook Kang / http://sungwookkang.com

Azure SQL, SQL Server Managed Instance, Query Store, QPI, sys.dm_os_wait_stats, sys.query_store_wait_stats

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 (0)	2020.02.28
SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 (0)	2019.09.24
SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인 (0)	2019.09.21
SQL Server 2019 Log Writer Workers (0)	2019.09.19
SQL Server Login Timeout 디버깅 (0)	2019.09.19

SQL Server2019, SQL Statistics, sys.dm_exec_requests, sys.dm_os_wait_stats, WAIT_ON_SYNC_STATISTICS_REFRESH

SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인

· Version : SQL Server 2019

SQL Server에서 통계정보는 옵티마이저가 실행 계획을 생성할 때 참고하는 중요한 지표이다. 통계 자동 업데이트가 true 로 설정된 경우, 데이터의 변경이 특정 임계치 이상되면 자동으로 통계 정보를 업데이트 한다.

· SQL Server Statistics : http://sqlmvp.kr/140165557766

이때 통계 정보를 업데이트하면서 블럭킹이 발생하는데 이전까지는 블럭킹이 발생한것에 대해서 확인할 방법이 없었다. SQL Server 2019 부터는 이러한 문제를 해결하기 위해 새로운 진단 데이터가 도입되었다. 통계 업데이트시 블럭킹을 발생하는 것을 재현하기 위해 아래와 같은 시나리오를 만들었다.

· 자동 통계 업데이트를 트리거하는 SELECT 쿼리를 실행한다.

· 동기 통계 업데이트가 실행을 시작하고 새 통계가 생성될때 까지 쿼리가 대기한다. (기본적으로 차단됨)

· 동기 통계 업데이트 조작이 완료 될 때 까지 쿼리 컴파일 및 실행이 재개되지 않는다.

이 시간 동안 쿼리는 동기화 통계 업데이트 작업이 완료될 때 까지 대기하고 있으며, 이 문제를 확인하기 어려웠다. 대용량 테이블또는 사용량이 많은 시스템등 통계 업데이트에 시간이 오래 걸리는 경우 원인을 쉽게 확인할 수 있는 방법이 없다.

SQL Server 2019에서는 동기화 통계 업데이트로 인해 쿼리가 차단되면 sys.dm_exec_requests에서‘command’컬럼에 (STATMAN)가 표시된다. 그리고 통계 업데이트 작업이 완료되면 초기 명령이름으로 돌아간다.

또한 새로운 WAIT_ON_SYNC_STATISTICS_REFRESH 대기 유형은 동기 통계 업데이트에서 집계된 대기 시간(블럭)을 측정한다. 이 대기시간 누적은 sys.dm_os_wait_stats 동적 관리뷰에서 확인할 수 있다.

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/13/diagnostic-data-for-synchronous-statistics-update-blocking/

2019-09-20/ Sungwook Kang / http://sungwookkang.com

SQL Server2019, SQL Statistics, WAIT_ON_SYNC_STATISTICS_REFRESH, sys.dm_os_wait_stats, sys.dm_exec_requests

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 (0)	2019.09.24
Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석 (0)	2019.09.24
SQL Server 2019 Log Writer Workers (0)	2019.09.19
SQL Server Login Timeout 디버깅 (0)	2019.09.19
SQL Server Worker Thread 기본 계산 (0)	2019.09.18

DB튜닝, MSSQL, SQL Server, SQL Wait, SQL 대기, sys.dm_os_ring_buffers, sys.dm_os_wait_stats, 쿼리성능, 쿼리튜닝

RESOURCE_GOVERNOR_IDLE과 쿼리 성능

Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

이 글은 CSS SQL Server Engineers에 기재된 내용으로 원문을 읽고 해석한 것으로 필자의 이해력을 기반으로 기술하였습니다. 기술적 오류 또는 번역의 오류가 포함될 수 있으니 반드시 원문을 참고 바랍니다.

쿼리의 실행이 느릴 때 SQL Nexus(http://sqlnexus.codeplex.com/) 에서 다음과 같은 대기 유형을 캡처 했다. 대기 유형에서 RESOURCE_GOVERNOR_IDLE가 매우 높게 나타는것을 확인 하였다.

이 대기 유형은 CPU CAP 실행에 관련한 것이었다(CAP_CPU_PERCENT). CAP_CPU_PERCENT를 사용하면 SQL Server는 CPU pool에서 CPU CAP을 초과하지 않는 것을 보장한다. 만약 CPU_CAP_PERCENT를 10%로 설정한 경우 SQL Server는 CPU pool의 10%를 사용하는 것을 보장한다. SQL Server는 풀에게 부여되지 않는 퀀텀(quantum)을 차지하기 위해 실행 가능한 큐에 유휴 소비자(Idle Consumer)를 삽입한다. 유휴소비자가 기다리는 동안 RESOURCE_GOVERNOR_IDLE이 유휴소비자 퀀텀이 있음을 나타내기 시작했다. 여기에 특정 리소스 풀에 대한 실행 가능한 큐와 CAP_CPU_PERCENT 구성 없이 어떻게 보이는지에 대한 것이다.

대기 유형은 Sys.dm_os_ring_buffers에서 볼 수 있을 뿐만 아니라 sys.dm_os_ring_buffers 항목에서도 볼 수 있다.

select * from sys.dm_os_ring_buffers

where ring_buffer_type ='RING_BUFFER_SCHEDULER' and record like '%SCHEDULER_IDLE_ENQUEUE%'

<Record id = "139903" type ="RING_BUFFER_SCHEDULER" time ="78584090"><Scheduler address="0x00000002F0580040"><Action>SCHEDULER_IDLE_ENQUEUE</Action><TickCount>78584090</TickCount><SourceWorker>0x00000002E301C160</SourceWorker><TargetWorker>0x0000000000000000</TargetWorker><WorkerSignalTime>0</WorkerSignalTime><DiskIOCompleted>0</DiskIOCompleted><TimersExpired>0</TimersExpired><NextTimeout>6080</NextTimeout></Scheduler></Record>

이처럼 RESOURCE_GOVERNOR_IDLE 대기 유형 타입을 무시해서는 안된다. 사용자가 CPU CAP을 설정하는 경우 정확히 평가해야 한다. 너무 낮은 설정은 쿼리에 영향을 받을 수 있다.

다음 스크립트는 CPU CAP을 설정하고 실행 시간을 관찰한다.

--first measure how long this takes

select count_big (*) from sys.messages m1 cross join sys.messages m2 -- cross join sys.messages m3

go

--alter to 5 (make sure you revert it back later)

ALTER RESOURCE POOL [default]

WITH ( CAP_CPU_PERCENT = 5 );

go

ALTER RESOURCE GOVERNOR RECONFIGURE;

go

--see the configuration

select * from sys.dm_resource_governor_resource_pools

go

--now see how long it takes

select count_big (*) from sys.messages m1 cross join sys.messages m2 -- cross join sys.messages m3

go

--While the above query is running, open a different connection and run the following query

--you will see that it keeps going up. note that if you don't configure CAP_CPU_PERCENT, this value will be zero

select * from sys.dm_os_wait_stats where wait_type ='RESOURCE_GOVERNOR_IDLE'

--revert it back

ALTER RESOURCE POOL [default]

WITH ( CAP_CPU_PERCENT = 100 );

go

ALTER RESOURCE GOVERNOR RECONFIGURE;

go

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2015/04/10/what-is-resource-governor-idle-and-why-you-should-not-ignore-it-completely.aspx

SQL Server, mssql, 쿼리성능, 쿼리튜닝, DB튜닝, sys.dm_os_wait_stats, sys.dm_os_ring_buffers, SQL 대기, SQL Wait

2015-04-20 / 강성욱 / http://sqlmvp.kr

'SQL Server > SQL Server Tip' 카테고리의 다른 글

트랜잭션로그 파일이 손상된 데이터베이스 복원 하기 (0)	2015.07.23
트랜잭션 로그 백업을 읽고 트랜잭션 발생 시간 및 사용자 찾기 (0)	2015.07.23
TDE 암호화된 데이터베이스 복원 (0)	2015.07.23
재해복구를 위한 SQL Server 역할 가져오기 (0)	2015.07.23
비관리자 계정에 Profiler 실행 권한 부여하기 (0)	2015.07.23

Data Science Lab