, PATINDEX(‘%Database Id =%’, blocked_process_report. THEN blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)’,’varchar(max)’)ĮLSE cast(SUBSTRING(blocked_process_report.value(‘(blocked-process-report/blocked-process/process/inputbuf)’,’varchar(max)’) (SELECT CASE WHEN ‘nvarchar(max)’) IS NULL ,case when PATINDEX(‘%PROC Database Id =%’. , COALESCE(SUSER_NAME(), ‘ - N/A - ‘) AS , COALESCE(OBJECT_NAME(, ), ‘ - N/A - ‘) AS , COALESCE(OBJECT_SCHEMA_NAME(, ), ‘ - N/A - ‘) AS (‘C:\temp\XEventSessions\blocked_process*.xel’, ‘C:\temp\XEventSessions\blocked_process*.xem’, NULL, NULL) , DATEADD(, DATEDIFF(, GETUTCDATE(), CURRENT_TIMESTAMP) It is not perfect, but provides more info quickly, which I use in a Power Pivot workbook to drill into problems and look for patterns. For these I added outer apply ‘functions’ to break out the ID values so that they could be returned as meaningful text using the Object_Name() functions. In the case of the deadlock_graph, the blocker can be incorrect if there are multiple victims, and the selection is relying on pulling data from the second ‘process’ in the ‘process-list’.įor the Blocked Processes, there are a lot of records that have inputbuf values like ‘Proc ’. The primary change is an addition of two columns, and, that are derived from the XML fields. This post and one by Kendra were most helpful! In order to drill deeper into an ongoing deadlock problem, I took your query from this post and reworked it to provide more detail from the deadlock_graph and blocked_process_report XML fields. Sp_configure ‘show advanced options’, 1 ĮXEC sp_configure ‘blocked process threshold’, ‘5’ īTW, it works for me with this literal instead of ‘blocked_process’, in spite of what Elijah pointed out. The second question is that I think that you should add this ‘ while creating the file ‘c:\myFoñder\XEventSessions\blocked_process_0_130391967440060000.xel’. The Exploit Database - Exploits, Shellcode, 0days, Remote Exploits, Local Exploits, Web Apps, Vulnerability Reports, Security Articles, Tutorials and more. The operating system returned error 3: ‘The system cannot find the path specified. Object cannot be added to the event session. The target, “_file”, encountered a configuration error during initialization. The “problem” is that you don’t see any error when creating the event session, but as I said, when starting it: SQL throws an error when starting the event session if the path selected for the event files does not exist. I am testing it right now and will use it in a customer, who’s complaining about performance problems. You can, however, create a table that will let you use the blocked process report viewer: These Extended Events files are different enough that you can’t use them outright. It’d be awesome if you could use it, but Michael’s blocked process report viewer uses the output of a server side trace to read blocking information. Swart has created tools to view the blocked process report. Viewing the Extended Events Blocked Process Reportīut what about the blocked process report? After all, your users are complaining about blocking, right? For folks using SQL Server 2012 and earlier, you can either parse the XML by hand or use SQL Sentry Plan Explorer. You should see an error along the lines of “There is an error in XML document”. Viewing the Extended Events Deadlock GraphsĮxtended Events deadlock graphs use a slightly different XML schema than what SSMS expects. It isn’t a pretty query, but it does the job very well. In this query, you read from an Extended Events session that’s being saved to disk and perform XML shredding to get client information. 'C:\temp\XEventSessions\blocked_process*.xem',ĬROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xeventsĬASE WHEN '' THEN ' - N/A - 'ĬOALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' - N/A - ') AS ,ĬOALESCE(OBJECT_NAME(object_id, database_id), ' - N/A - ') AS ,ĬOALESCE(SUSER_NAME(login_sid), ' - N/A - ') AS username, Since you want to read from the systemhealth extended event on SQL Server 2008 R2, all you need to do is un-comment the section to 'read from the systemhealth XE ring buffer' ( line 100). The code will work for all versions of SQL Server. ('C:\temp\XEventSessions\blocked_process*.xel', He has written a very elegant script to shred all the details from an XML deadlock graph. SELECT object_Name as event_name,event_data=convert(xml,event_data) FROM sys.fn_xe_file_target_read_file('', null, null, null)Įvent_data.DATEDIFF(mi, GETUTCDATE(), 'datetime2')) AS 'nvarchar(128)') Select value, * from B WITH (updlock) where value = 1 or id = 1 I setup the extended event with database_xml_deadlock_report, but it seems only show the report like in ERROR log. Are there any way I can get the deadlock graph just like the previous sql profiler trace?
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |