March 11, 2014 at 10:04 am
Hi all,
Ive ran some Event Notifications collecting some blocking data, im now trying to extract the sqlhandle from the xml that its created and put it into sys.dm_exec_sql_text.
what ever i try is coming up short though.
To get the SQLHandle you can do the following:
SELECT FullLog.value('(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process[1]/process/executionStack/frame/@sqlhandle)[1]',
'nvarchar(max)') AS 'sqlhandle'
FROM [dbo].[EventNotification]
WHERE [EventType] = 'BLOCKED_PROCESS_REPORT'
If I cut and paste that into
select *
from sys.dm_exec_sql_text (0x030010004b525b7ba8648e00d0a100000100000000000000)
i get results! happy days.
If i try the following
WITH cte ( sqlhandle )
AS ( SELECT FullLog.value('(/EVENT_INSTANCE/TextData/blocked-process-report/blocking-process[1]/process/executionStack/frame/@sqlhandle)[1]',
'nvarchar(max)') AS 'sqlhandle'
FROM [DBA].[dbo].[EventNotification2]
WHERE [EventType] = 'BLOCKED_PROCESS_REPORT'
)
SELECT sql_text.*
FROM cte
CROSS APPLY sys.dm_exec_sql_text(CONVERT(VARBINARY(MAX), sqlhandle))
AS sql_text
I get the error
Msg 569, Level 16, State 4, Line 1
The handle that was passed to dm_exec_sql_text was invalid.
If i take out the CONVERT from the cross apply i get the error
Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type nvarchar(max) to varbinary is not allowed. Use the CONVERT function to run this query.
Any ideas what im missing? ive tried adding the convert into the cte and its still not getting the same error sayings its not valid..
EventNotifcation Table design:
CREATE TABLE EventNotification
(
LoggingID INT IDENTITY(1, 1)
PRIMARY KEY CLUSTERED ,
SQLInstance VARCHAR(100) ,
DatabaseName VARCHAR(100) ,
EventTime DATETIME ,
EventType VARCHAR(100) ,
LoginName VARCHAR(100) ,
DatabaseUser VARCHAR(100) ,
ClientHostName VARCHAR(100) ,
NTUserName VARCHAR(100) ,
NTDomainName VARCHAR(100) ,
SchemaName VARCHAR(100) ,
ObjectName VARCHAR(100) ,
ObjectType VARCHAR(100) ,
Success INT ,
FullSQL VARCHAR(MAX) ,
FullLog XML ,
Archived BIT NOT NULL
)
Full XML example from FullLog :
<EVENT_INSTANCE>
<EventType>BLOCKED_PROCESS_REPORT</EventType>
<PostTime>2014-03-06T11:51:15.583</PostTime>
<SPID>5</SPID>
<TextData>
<blocked-process-report>
<blocked-process>
<process id="process37e03d498" taskpriority="0" logused="0" waitresource="PAGE: 15:1:1737 " waittime="895387" ownerId="856691" transactionname="SELECT" lasttranstarted="2014-03-06T11:36:20.163" XDES="0x36aa12d00" lockMode="S" schedulerid="6" kpid="9500" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2014-03-06T11:36:19.960" lastbatchcompleted="2014-03-06T11:36:12.230" lastattention="1900-01-01T00:00:00.230" clientapp="Microsoft SQL Server Management Studio - Query" hostname="BLQGV4J" hostpid="9844" loginname="staggerlee" isolationlevel="read committed (2)" xactid="856691" currentdb="15" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack>
<frame line="2" stmtstart="86" sqlhandle="0x020000001328ea139dbaf2a4d9d57b31247285837357cded0000000000000000000000000000000000000000" />
</executionStack>
<inputbuf>
-- run this query in a different window!
SELECT FirstName ,
LastName ,
SUM(soh.TotalDue) AS TotalDue ,
MAX(OrderDate) AS LastOrder
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.Customer AS c ON soh.CustomerID = c.CustomerID
INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID
WHERE soh.OrderDate > = '2011/ 01/ 01'
GROUP BY c.CustomerID ,
FirstName ,
LastName </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="sleeping" spid="52" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-03-06T11:35:54.087" lastbatchcompleted="2014-03-06T11:35:58.820" lastattention="1900-01-01T00:00:00.820" clientapp="Microsoft SQL Server Management Studio - Query" hostname="BLQGV4J" hostpid="9844" loginname="staggerlee" isolationlevel="read committed (2)" xactid="856039" currentdb="15" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
<executionStack />
<inputbuf>
DECLARE @SalesOrderHeaderID INT
BEGIN TRANSACTION
INSERT INTO Sales.SalesOrderHeader
( RevisionNumber ,
OrderDate ,
DueDate ,
ShipDate ,
Status ,
OnlineOrderFlag ,
PurchaseOrderNumber ,
AccountNumber ,
CustomerID ,
SalesPersonID ,
TerritoryID ,
BillToAddressID ,
ShipToAddressID ,
ShipMethodID ,
CreditCardID ,
CreditCardApprovalCode ,
CurrencyRateID ,
Comment ,
rowguid ,
ModifiedDate
)
VALUES ( 5 ,
'2011/ 06/ 20' ,
'2011/ 06/ 25' ,
'2011/ 06/ 30' ,
5 ,
0 ,
NULL ,
'10-4030-018749' ,
18749 ,
NULL ,
6 ,
28374 ,
28374 ,
1 ,
8925 ,
'929849Vi46003' ,
NULL ,
NULL ,
NEWID() ,
GETD </inputbuf>
</process>
</blocking-process>
</blocked-process-report>
</TextData>
<DatabaseID>15</DatabaseID>
<TransactionID>856691</TransactionID>
<Duration>895381000</Duration>
<StartTime>2014-03-06T11:36:20.203</StartTime>
<EndTime>2014-03-06T11:51:15.583</EndTime>
<ObjectID>0</ObjectID>
<IndexID>0</IndexID>
<ServerName>BLQGV4J</ServerName>
<Mode>3</Mode>
<LoginSid>AQ==</LoginSid>
<EventSequence>502</EventSequence>
<IsSystem>1</IsSystem>
<SessionLoginName />
</EVENT_INSTANCE>
March 11, 2014 at 11:26 am
While I don't have a workaround for your solution, the second error appears to be the most telling. It looks like SQL Server doesn't like the NVARCHAR to VARBINARY conversion.
In the example below I try to create a NVARCHAR(MAX) variable and convert it to VARBINARY(MAX). When I print it as an NVARCHAR, it looks fine. When trying to convert it to VARBINARY, however, it gives us something entirely different which is probably what is getting passed into your example.
DECLARE @testvar NVARCHAR(MAX)
SET @testvar = '0x01000400EB08F51F40C012F00200000000000000'
PRINT @testvar
PRINT CONVERT(VARBINARY(MAX), @testvar)
Results:
0x01000400EB08F51F40C012F00200000000000000
0x300078003000310030003000300034003000300045004200300038004600350031004600340030004300300031003200460030003000320030003000300030003000300030003000300030003000300030003000
I'm sure there are others that can provide a more detailed explanation though.
March 11, 2014 at 11:37 am
You can try a convert to varbinary(64) (sqlhandle as expected by sys.dm_exec_query_text is looking for the varbinary(64)).
In looking at your deadlock graph, you have a sqlhandle that is too large. The sqlhandle should have ended at the cded.
Since you are trying to get the text of the queries involved in the deadlock, you could just trap that from the inputbuf node.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2014 at 2:43 pm
hey,
thanks the replies..
Yes its definitely messing with the string when its converts into varbinary, i've tried converting to varbinary max and 64, always the same results of its not a valid..
SQLRNNR its not a deadlock graph, its a blocking info from capturing BLOCKING_PROCRESS_THRESHOLD with service broker and event notifications, so no alternatives that i can see at present to this..
cheers
s
March 11, 2014 at 2:49 pm
Yup correct, I misspoke on the deadlock graph. It is obviously just a blocked process report. Look at your XML, you have the <inputbuf> node there with the query that is participating in the blocking. You can just extract the query from the XML that you have already trapped.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 12, 2014 at 6:45 am
Hi all,
sorry for the delay in replying, I have it working now,
it was down to the convert.
using
CONVERT ( varbinary(64), sql_handle, 1)
works.
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Thanks for the help
S
October 13, 2014 at 1:11 pm
😛 Great Help, just what I was looking for!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply