[SQL Server] Afficher la quantité de mémoire allouée aux verrous
Vérifier la valeur de la ligne OBJECTSTORE_LOCK_MANAGER
--SQL Server 2012 ou version ultérieure :
SELECT SUM(pages_kb)/1024 AS lock_memory_megabytes, type FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY 1 DESC
--SQL Server 2008 :
SELECT (SUM(single_pages_kb) + SUM(multi_pages_kb))/1024 AS lock_memory_megabytes, type FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY 1 DESC
Nous devrons voir quelles sessions contiennent le plus de verrous. Utilisez les trois requêtes suivantes pour trouver les sessions qui détiennent le plus de verrous. Cet ensemble de requêtes renvoie 3 ensembles de résultats : les informations sur la session, les sessions en cours d'exécution détenant des verrous et les sessions inactives détenant des verrous. Exécutez tous les ensembles d'instructions suivants ensemble dans SSMS :
SELECT TOP 10 count(1) AS lock_count, request_session_id INTO #holding_locks FROM sys.dm_tran_locks GROUP BY request_session_id ORDER BY 1 DESC
SELECT hl.lock_count, hl.request_session_id, s.login_name, s.program_name, s.host_name FROM #holding_locks hl INNER JOIN sys.dm_exec_sessions s ON hl.request_session_id = s.session_id
SELECT hl.request_session_id, hl.lock_count, SUBSTRING(st.text, (er.statement_start_offset/2)+1, ((CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE er.statement_end_offset END - er.statement_start_offset)/2) + 1) AS statement_text , CONVERT(XML, qp.query_plan) FROM #holding_locks hl INNER JOIN sys.dm_exec_requests er ON er.session_id = hl.request_session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st CROSS APPLY sys.dm_exec_text_query_plan(er.plan_handle, er.statement_start_offset, er.statement_end_offset) qp ORDER BY lock_count desc
SELECT hl.request_session_id, hl.lock_count, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text, CONVERT(XML, qp.query_plan) FROM #holding_locks hl INNER JOIN sys.dm_exec_connections c ON hl.request_session_id = c.session_id INNER JOIN sys.dm_exec_query_stats qs ON qs.sql_handle = c.most_recent_sql_handle CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) qp WHERE NOT EXISTS(SELECT 1 FROM sys.dm_exec_requests er WHERE er.session_id = hl.request_session_id) ORDER BY lock_count desc, qs.statement_start_offset
La sortie de cette requête montre quelles sessions/requêtes détiennent le plus de verrous.