« לכל הכתבות

ניטור נעילות

נעילות בבסיסי נתונים מתרחשות לעיתים קרובות ומטרתן להגן על משאבי בסיסי הנתונים ולשמור על אמינות ועקביות הנתונים שבו. עם זאת הנעילות, במידה והן ממושכות פוגעות בביצועי המערכת מכיוון שהן מעכבות טראנזאקציות הממתינות למשאבים הנעולים מלהתבצע, על כן חשוב לנטר את הנעילות הן כדי לוודא שהן מתבצעות בטראנזאקציות המשתמשות במידע שבמשאבים ו/או משנות אותו כדי למנוע פגיעה בנתונים, הן כדי למצוא דרכים לקצר את משכן ככל האפשר כדי למנוע פגיעה בביצועים והן כדי לוודא שאינן מתבצעות היכן שאין בהן צורך.

נעילות בבסיסי נתונים נועדו להגן על משאבים או אובייקטים משותפים, משאבים אלה עשויים להיות:

  • טבלאות
  • שורות נתונים
  • בלוקי נתונים
  • פריטים מוטמנים
  • חיבורים (IP)
  • מערכות שלמות

קיימים גם סוגים שונים של מנעולים היכולים לגרום לנעילות משותפות, נעילות בלעדיות, נעילות טראנזאקציונאליות, נעילות DML ונעילות גיבוי-שחזור. מכל מקום, מסמך זה יתמקד בסוג אחד של נעילות, נעילות טראנזאקציונאליות.
הנה שאילתא שתסייע לך לגלות את השאילתות הנועלות בבסיס הנתונים שלך:

-- Perform cleanup.
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='FindBlockers')
DROP EVENT SESSION FindBlockers ON SERVER
GO
DECLARE @dbid int

SELECT @dbid = db_id(DB_NAME())

DECLARE @sql nvarchar(1024)
SET @sql = '
CREATE EVENT SESSION FindBlockers ON SERVER
ADD EVENT sqlserver.lock_acquired
(action
( sqlserver.sql_text, sqlserver.database_id, sqlserver.tsql_stack,
sqlserver.plan_handle, sqlserver.session_id)
WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND resource_0!=0)
),
ADD EVENT sqlserver.lock_released
(WHERE ( database_id=' + cast(@dbid as nvarchar) + ' AND resource_0!=0 ))
ADD TARGET package0.pair_matching
( SET begin_event=''sqlserver.lock_acquired'',
begin_matching_columns=''database_id, resource_0, resource_1, resource_2, transaction_id, mode'',
end_event=''sqlserver.lock_released'',
end_matching_columns=''database_id, resource_0, resource_1, resource_2, transaction_id, mode'',
respond_to_memory_pressure=1)
WITH (max_dispatch_latency = 1 seconds)'

EXEC (@sql)
--
-- Create the metadata for the event session
-- Start the event session
--
ALTER EVENT SESSION FindBlockers ON SERVER
STATE = START
--
-- The pair matching targets report current unpaired events using
-- the sys.dm_xe_session_targets dynamic management view (DMV)
-- in XML format.
-- The following query retrieves the data from the DMV and stores
-- key data in a temporary table to speed subsequent access and
-- retrieval.
--
SELECT
objlocks.value('(action[@name="session_id"]/value)[1]', 'int')
AS session_id,
objlocks.value('(data[@name="database_id"]/value)[1]', 'int')
AS database_id,
objlocks.value('(data[@name="resource_type"]/text)[1]', 'nvarchar(50)' )
AS resource_type,
objlocks.value('(data[@name="resource_0"]/value)[1]', 'bigint')
AS resource_0,
objlocks.value('(data[@name="resource_1"]/value)[1]', 'bigint')
AS resource_1,
objlocks.value('(data[@name="resource_2"]/value)[1]', 'bigint')
AS resource_2,
objlocks.value('(data[@name="mode"]/text)[1]', 'nvarchar(50)')
AS mode,
objlocks.value('(action[@name="sql_text"]/value)[1]', 'varchar(MAX)')
AS sql_text,
CAST(objlocks.value('(action[@name="plan_handle"]/value)[1]', 'varchar(MAX)') AS xml)
AS plan_handle,
CAST(objlocks.value('(action[@name="tsql_stack"]/value)[1]', 'varchar(MAX)') AS xml)
AS tsql_stack
INTO #unmatched_locks
FROM (
SELECT CAST(xest.target_data as xml)
lockinfo
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
WHERE xest.target_name = 'pair_matching' AND xes.name = 'FindBlockers'
) heldlocks
CROSS APPLY lockinfo.nodes('//event[@name="lock_acquired"]') AS T(objlocks)

--
-- Join the data acquired from the pairing target with other
-- DMVs to return provide additional information about blockers
--
SELECT ul.*
FROM #unmatched_locks ul
INNER JOIN sys.dm_tran_locks tl ON ul.database_id = tl.resource_database_id AND ul.resource_type = tl.resource_type
WHERE resource_0 IS NOT NULL
AND session_id IN
(SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id != 0)
AND tl.request_status='wait'
AND REPLACE(ul.mode, 'LCK_M_', '' ) = tl.request_mode

אחרי שתסיימו לנתח את התוצאות אל תשכחו להריץ את הפקודות הבאות:
DROP TABLE #unmatched_locks
DROP EVENT SESSION FindBlockers ON SERVER

כך תוכל לזהות את השאילתות הנועלות בבסיס הנתונים שלך, לנתח אותן ולפעול כדי לייעל אותן.

Leave a Reply

Your email address will not be published.