Verificare locks in TempDB

Configurare noua (How To)

Situatie

Pentru a fi de ajutor in cadrul procesului de optimizare a proceselor dintr-un server de baze de date sql, am facut o procedura ce verifica daca sunt blocaje in baza TempDB si, daca da, intoarce date despre aceste blocaje, cum ar fi: id-ul sesiunii care este blocata, id-ul sesiunii care blocheaza, tipul de lock\wait type.

Solutie

create procedure BD_TempDB_blocking_prc

as

SELECT
session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
ResourceType = CASE
WHEN PageID = 1 OR PageID % 8088 = 0 THEN ‘Is PFS Page’
WHEN PageID = 2 OR PageID % 511232 = 0 THEN ‘Is GAM Page’
WHEN PageID = 3 OR (PageID – 1) % 511232 = 0 THEN ‘Is SGAM Page’
ELSE ‘Is Not PFS, GAM, or SGAM page’
END
FROM ( SELECT
session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
CAST(RIGHT(resource_description, LEN(resource_description)
– CHARINDEX(‘:’, resource_description, 3)) AS INT) AS PageID
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE ‘PAGE%LATCH_%’
AND resource_description LIKE ‘2:%’
) AS tab;

Tip solutie

Permanent
Etichetare:

Voteaza

(9 din 20 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?