교착 상태 및 잠금 상태의 해독 키 및 Page WaitResource

차단된 프로세스 보고서를 사용하거나 SQL Server에서 제공하는 교착 상태 그래프를 주기적으로 수집하면 다음과 같은 상황이 발생합니다.

waitresource="페이지: 6:3:70133"

waitresource=“KEY: 6:72057594041991168 (ce52f92a058c)“

때로는 연구하는 거대한 XML에 더 많은 정보가 있지만(교착 상태 그래프에는 개체 및 인덱스의 이름을 찾는 데 도움이 되는 리소스 목록이 포함되어 있음) 항상 그런 것은 아닙니다.

이 텍스트는 당신이 그것들을 해독하는 데 도움이 될 것입니다.

여기에 있는 모든 정보는 다양한 장소의 인터넷에 있으며 단순히 매우 분산되어 있습니다! DBCC PAGE에서 hobt_id, 문서화되지 않은 %%physloc%% 및 %%lockres%% 함수에 이르기까지 모든 것을 하나로 묶고 싶습니다.

먼저 PAGE 잠금 대기에 대해 이야기한 다음 KEY 잠금으로 이동하겠습니다.

1) waitresource="PAGE: 6:3:70133" = Database_Id: 파일 ID: 페이지 번호

요청이 PAGE 잠금에서 대기 중인 경우 SQL Server는 해당 페이지의 주소를 제공합니다.

"PAGE: 6:3:70133"을 분해하면 다음과 같은 결과가 나타납니다.

• 데이터베이스 ID = 6
• data_file_id = 3
• 페이지 번호 = 70133

1.1) database_id 해독

다음 쿼리를 사용하여 데이터베이스 이름을 찾습니다.

SELECT 
    name 
FROM sys.databases 
WHERE database_id=6;
GO

공개입니다 DB WideWorldImporters 내 SQL 서버에서.

1.2) 데이터 파일 이름 찾기 - 관심이 있는 경우

다음 단계에서 data_file_id를 사용하여 테이블 이름을 찾을 것입니다. 다음 단계로 건너뛸 수 있지만 파일 이름에 관심이 있는 경우 찾은 데이터베이스의 컨텍스트에서 쿼리를 실행하고 data_file_id를 이 쿼리로 대체하여 찾을 수 있습니다.

USE WideWorldImporters;
GO
SELECT 
    name, 
    physical_name
FROM sys.database_files
WHERE file_id = 3;
GO

WideWorldImporters 데이터베이스에서 이것은 WWI_UserData라는 파일이며 C:MSSQLDATAWideWorldImporters_UserData.ndf로 복원했습니다. (앗, 제가 시스템 드라이브에 파일을 넣는 걸 잡았어요! 안돼! 부끄러워요).

1.3) DBCC PAGE에서 개체 이름 가져오기

이제 데이터 파일 70133의 페이지 #3이 WorldWideImporters 데이터베이스에 속한다는 것을 알고 있습니다. 문서화되지 않은 DBCC PAGE 및 추적 플래그 3604를 사용하여 이 페이지의 내용을 볼 수 있습니다.
참고: 문서화되지 않은 항목이기 때문에 다른 서버의 복원된 복사본에서 DBCC PAGE를 사용하는 것을 선호합니다. 경우에 따라 그녀는 덤프가 발생할 수 있습니다 (약. 번역기-불행히도 링크는 아무데도 연결되지 않지만 URL로 판단하면 필터링 된 인덱스에 대해 이야기하고 있습니다.).

/* This trace flag makes DBCC PAGE output go to our Messages tab
instead of the SQL Server Error Log file */
DBCC TRACEON (3604);
GO
/* DBCC PAGE (DatabaseName, FileNumber, PageNumber, DumpStyle)*/
DBCC PAGE ('WideWorldImporters',3,70133,2);
GO

결과로 스크롤하면 object_id 및 index_id를 찾을 수 있습니다.

거의 완료되었습니다! 이제 쿼리를 사용하여 테이블 및 인덱스 이름을 찾을 수 있습니다.

USE WideWorldImporters;
GO
SELECT 
    sc.name as schema_name, 
    so.name as object_name, 
    si.name as index_name
FROM sys.objects as so 
JOIN sys.indexes as si on 
    so.object_id=si.object_id
JOIN sys.schemas AS sc on 
    so.schema_id=sc.schema_id
WHERE 
    so.object_id = 94623380
    and si.index_id = 1;
GO

여기에서 잠금 대기가 Sales.OrderLines 테이블의 PK_Sales_OrderLines 인덱스에 있음을 알 수 있습니다.

참고: SQL Server 2014 이상에서는 문서화되지 않은 DMO sys.dm_db_database_page_allocations를 사용하여 개체 이름을 찾을 수도 있습니다. 하지만 데이터베이스의 모든 페이지를 쿼리해야 하는데 이는 대규모 데이터베이스에 적합하지 않아 DBCC PAGE를 사용했습니다.

1.4) 차단된 페이지의 데이터를 볼 수 있나요?

누우, 네. 하지만... 정말 필요한가요?
작은 테이블에서도 느립니다. 하지만 좀 멋집니다. 여기까지 읽으셨으니... %%physloc%%에 대해 이야기해 봅시다!

%%physloc%%는 각 항목에 대한 물리적 ID를 반환하는 문서화되지 않은 마법입니다. 당신이 사용할 수있는 SQL Server 2008 이상에서 sys.fn_PhysLocFormatter와 함께 %%physloc%%.

이제 우리는 Sales.OrderLines에서 페이지를 차단하려고 했음을 알았으므로 다음 쿼리를 사용하여 #3 페이지의 데이터 파일 #70133에 저장된 이 테이블의 모든 데이터를 볼 수 있습니다.

Use WideWorldImporters;
GO
SELECT 
    sys.fn_PhysLocFormatter (%%physloc%%),
    *
FROM Sales.OrderLines (NOLOCK)
WHERE sys.fn_PhysLocFormatter (%%physloc%%) like '(3:70133%'
GO

내가 말했듯이 작은 테이블에서도 느립니다. 우리가 보고자 하는 데이터가 잠금이 발견된 시점과 정확히 동일하다는 보장이 아직 없기 때문에 요청에 NOLOCK을 추가했습니다. 따라서 안전하게 더티 읽기를 수행할 수 있습니다.
그러나 만세, 쿼리는 우리 쿼리가 싸웠던 바로 그 25개의 행을 반환합니다.

PAGE 잠금에 대해 충분합니다. KEY 잠금을 기다리고 있다면?

2) waitresource="KEY: 6:72057594041991168 (ce52f92a058c)" = Database_Id, HOBT_Id(원하는 경우 %%lockres%%로 해독할 수 있는 매직 해시)

쿼리가 인덱스 항목을 잠그려고 시도하고 자체적으로 잠기면 완전히 다른 유형의 주소를 얻게 됩니다.
"6:72057594041991168 (ce52f92a058c)"를 여러 부분으로 나누면 다음과 같은 결과가 나타납니다.

• 데이터베이스 ID = 6
• hobt_id = 72057594041991168
• 매직 해시 = (ce52f92a058c)

2.1) database_id 해독

위의 예와 정확히 동일하게 작동합니다! 다음 쿼리를 사용하여 데이터베이스 이름을 찾습니다.

SELECT 
    name 
FROM sys.databases 
WHERE database_id=6;
GO

내 경우는 똑같다. DB WideWorldImporters.

2.2) hobt_id 복호화

찾은 데이터베이스의 컨텍스트에서 테이블 및 인덱스의 이름을 결정하는 데 도움이 되는 몇 가지 조인을 사용하여 sys.partitions를 쿼리해야 합니다.

USE WideWorldImporters;
GO
SELECT 
    sc.name as schema_name, 
    so.name as object_name, 
    si.name as index_name
FROM sys.partitions AS p
JOIN sys.objects as so on 
    p.object_id=so.object_id
JOIN sys.indexes as si on 
    p.index_id=si.index_id and 
    p.object_id=si.object_id
JOIN sys.schemas AS sc on 
    so.schema_id=sc.schema_id
WHERE hobt_id = 72057594041991168;
GO

요청이 PK_Application_Countries 색인을 사용하여 Application.Countries 잠금에서 대기 중임을 알려줍니다.

2.3) 이제 일부 %%lockres%% 마법에 대해 - 어떤 항목이 잠겼는지 확인하려는 경우

잠금이 필요한 행을 정말로 알고 싶다면 테이블 자체를 쿼리하여 찾을 수 있습니다. 문서화되지 않은 %%lockres%% 함수를 사용하여 매직 해시와 일치하는 항목을 찾을 수 있습니다.
이 쿼리는 전체 테이블을 스캔하며 큰 테이블에서는 전혀 재미가 없을 수 있습니다.

SELECT
    *
FROM Application.Countries (NOLOCK)
WHERE %%lockres%% = '(ce52f92a058c)';
GO

NOLOCK을 추가했습니다(트위터에서 Klaus Aschenbrenner의 조언에 따라) 잠금이 문제가 될 수 있기 때문입니다. 우리는 거래가 시작되었을 때 무엇이 ​​있었는지가 아니라 현재 무엇이 있는지 확인하기를 원합니다. 데이터 일관성은 우리에게 중요하지 않다고 생각합니다.
Voila, 우리가 싸운 기록!

감사의 글 및 추가 정보

누가 이런 것들을 처음으로 설명했는지는 기억나지 않지만, 다음은 여러분이 좋아할 만한 최소한의 문서화에 대한 두 개의 게시물입니다.

• Paul Randal의 게시물 %%physloc%% 및 sys.fn_PhysLocFormatter (첫 번째 예에 데이터가 있으므로)
• StackOverflow에 대한 질문 %%lockres%% 사용 (두 번째 예에서 데이터를 찾은 것처럼). 답변 중 하나는 게시물로 연결됩니다. 2010년에 작성된 %%lockres%%에 대한 Grant Fritchey.

출처 : habr.com