how see parameter value on xml_deadlock_report

Question

i have some xml_deadlock_report i got from this step

management > sessions > system_health > package().even_file

but when i see xml_deadlock_report i got sql query but i can't see what parameter use in where condition

<deadlock>
 <victim-list>
  <victimProcess id="process30e9e810c8" />
 </victim-list>
 <process-list>
  <process id="process30e9e810c8" taskpriority="0" logused="5608" waitresource="PAGE: 31:1:8080697 " waittime="982" ownerId="3428828901" transactionname="implicit_transaction" lasttranstarted="2020-06-02T16:57:47.850" XDES="0x2bea7c6d28" lockMode="U" schedulerid="1" kpid="23404" status="suspended" spid="150" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-06-02T16:58:06.750" lastbatchcompleted="2020-06-02T16:58:06.747" lastattention="1900-01-01T00:00:00.747" hostname="172.16.12.40" hostpid="0" loginname="user_xxxx_admin" isolationlevel="read committed (2)" xactid="3428828901" currentdb="31" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x02000000adf830280fe6a3ada9d7a7b50732f1aa6cf6359e0000000000000000000000000000000000000000">
DELETE FROM TTADATTSTATUSDETAIL
            WHERE Attend_ID = @P1    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P1 nvarchar(4000))DELETE FROM TTADATTSTATUSDETAIL
            WHERE Attend_ID = @P1    </inputbuf>
  </process>
  <process id="process30e9db1c38" taskpriority="0" logused="7004" waitresource="PAGE: 31:1:8635778 " waittime="1040" ownerId="3428815086" transactionname="implicit_transaction" lasttranstarted="2020-06-02T16:57:35.037" XDES="0x29609d83a8" lockMode="U" schedulerid="5" kpid="12076" status="suspended" spid="130" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-06-02T16:58:06.030" lastbatchcompleted="2020-06-02T16:58:06.023" lastattention="1900-01-01T00:00:00.023" hostname="172.16.12.40" hostpid="0" loginname="user_xxxx_admin" isolationlevel="read committed (2)" xactid="3428815086" currentdb="31" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x02000000adf830280fe6a3ada9d7a7b50732f1aa6cf6359e0000000000000000000000000000000000000000">
DELETE FROM TTADATTSTATUSDETAIL
            WHERE Attend_ID = @P1    </frame>
   </executionStack>
   <inputbuf>
(@P1 nvarchar(4000))DELETE FROM TTADATTSTATUSDETAIL
            WHERE Attend_ID = @P1    </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="8080697" dbid="31" subresource="FULL" objectname="dbSF_BizNet_KPKRI.dbo.TTADATTSTATUSDETAIL" id="lock29105cff80" mode="U" associatedObjectId="72057594477346816">
   <owner-list>
    <owner id="process30e9db1c38" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process30e9e810c8" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="8635778" dbid="31" subresource="FULL" objectname="dbSF_BizNet_KPKRI.dbo.TTADATTSTATUSDETAIL" id="lock29808f0600" mode="IX" associatedObjectId="72057594477346816">
   <owner-list>
    <owner id="process30e9e810c8" mode="IX" />
   </owner-list>
   <waiter-list>
    <waiter id="process30e9db1c38" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>

how i see what value at parameter @P1 ??

Niels Broertjes · Accepted Answer · 2020-06-04T13:17:23.767

You cannot see that information in the deadlock report, because it is a parameterized query. However, maybe it helps you to use Brent Ozar's sp_blitzLock to troubleshoot your deadlocks. https://www.brentozar.com/archive/2017/12/introducing-sp_blitzlock-troubleshooting-sql-server-deadlocks/

Edit:

You can only see what parameters are used to compile the query plan. However, that probably is not what you are looking for, because the query that caused the deadlock has most likely different parameters then the one used to compile the plan. If you want to, refer to this article:

https://www.mssqltips.com/sqlservertip/4992/how-to-find-compiled-parameter-values-for-sql-server-cached-plans/

In order to see the compiled values you probably can use Extended Events. I looked it up and found this article that explains how to do that:

https://www.scarydba.com/2018/09/24/extended-events-and-stored-procedure-parameter-values/

Be aware that running Extended Events can have an impact on your server in terms of performance.

The issue with performance you are running into is probably parameter sniffing. Suddenly your Stored Procedure runs slow. There is already tons of stuff written about it. Refer to this article:

Why is my query suddenly slower than it was yesterday?

how see parameter value on xml_deadlock_report

1 Answers1