Created by
David Hogue
| WITH locks AS (
SELECT
CAST(event_data AS XML).query('/event/data[@name=''resource_type'']/text').value('.', 'nvarchar(100)') AS resource_type,
CAST(event_data AS XML).query('/event/data[@name=''mode'']/text').value('.', 'nvarchar(100)') AS mode,
CAST(event_data AS XML).query('/event/data[@name=''owner_type'']/text').value('.', 'nvarchar(100)') AS owner_type,
CAST(event_data AS XML).query('/event/data[@name=''object_id'']/value').value('.', 'nvarchar(100)') AS [object_id],
CAST(event_data AS XML).query('/event/data[@name=''database_name'']/value').value('.', 'nvarchar(100)') AS [database_name]
FROM sys.fn_xe_file_target_read_file('/tmp/locks_test_0_131999895040450000.xel', NULL, NULL, NULL))
SELECT COUNT(*), l.resource_type, l.mode, l.owner_type, s.[name] AS [Schema], o.[name] AS [Object]
FROM locks AS l
LEFT JOIN LearningPlatform.sys.objects AS o ON l.[object_id] = o.[object_id]
LEFT JOIN LearningPlatform.sys.schemas AS s ON o.[schema_id] = s.[schema_id]
GROUP BY l.resource_type, l.mode, l.owner_type, l.[object_id], s.[name], o.[name]
ORDER BY COUNT(*) DESC;
|
| -- Start logging
ALTER EVENT SESSION [locks_test]
ON SERVER
STATE = START;
/********************************
* Insert migration script here *
********************************/
-- Stop logging
ALTER EVENT SESSION [locks_test]
ON SERVER
STATE = STOP;
|
| -- Setup event
CREATE EVENT SESSION [locks_test] ON SERVER
ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_released
GO
-- Log to file
ALTER EVENT SESSION [locks_test] ON SERVER
ADD TARGET package0.event_file(SET filename='/tmp/locks_test.xel')
GO
|