<inputbuf>
교착 상태 확장 이벤트 세션에서 캡처 한 교착 상태 XML 의 요소 크기를 늘리는 방법이 있습니까?
응용 프로그램 코드에서 문제를 정확히 찾아내는 데 도움이되는 전체 쿼리를 확인하려고합니다.
1024 자로 제한되어있는 것 같습니다 +/-. 증가시킬 수 있습니까?
샘플 XML은 아래를 참조하십시오. <inputbuf>
선택 목록의 중간에서 요소 의 쿼리 텍스트 가 잘리는 것을 볼 수 있습니다 .
<deadlock>
<victim-list>
<victimProcess id="processc9c0829848" />
</victim-list>
<process-list>
<process id="processc9c0829848" taskpriority="0" logused="0" waitresource="PAGE: 5:1:40600276 " waittime="696" ownerId="255115931225" transactionname="SELECT" lasttranstarted="2019-04-24T09:29:25.950" XDES="0xc8dfa8da40" lockMode="S" schedulerid="13" kpid="8480" status="suspended" spid="245" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2019-04-24T09:29:25.950" lastbatchcompleted="2019-04-24T09:29:25.950" lastattention="1900-01-01T00:00:00.950" clientapp="EntityFramework" hostname="MSR-PRD-BDB02" hostpid="43440" loginname="IUSR_BuildDB" isolationlevel="read committed (2)" xactid="255115931225" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="66" stmtend="7378" sqlhandle="0x02000000638e8b1acc45f82c476cd42914e32866e87c4fd60000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 int,@p__linq__1 int)SELECT
[Project1].[CachedBuildStateId] AS [CachedBuildStateId],
[Project1].[BuildVersionId1] AS [BuildVersionID],
[Project1].[ProjectID] AS [ProjectID],
[Project1].[VersionName] AS [VersionName],
[Project1].[PlatformID] AS [PlatformID],
[Project1].[VersionPath] AS [VersionPath],
[Project1].[RegionID] AS [RegionID],
[Project1].[TestStatusID] AS [TestStatusID],
[Project1].[TestResult] AS [TestResult],
[Project1].[TestReportPath] AS [TestReportPath],
[Project1].[TypeID] AS [TypeID],
[Project1].[Size] AS [Size],
[Project1].[VersionDate] AS [VersionDate],
[Project1].[Approved] AS [Approved],
[Project1].[Archived] AS [Archived],
[Project1].[CLData] AS [CLData],
[Project1].[CLCode] AS [CLCode],
[Project1].[CLSound] AS [CLSound],
[Project1].[XDKSDKVersion] AS [XDKSDKVersion],
[Project1].[Comments] AS [Comments],
[Project1].[DateAdded] AS [DateAdded],
[Project1].[DateModified] AS [DateModified],
[Project1].[CacheLocally] AS [CacheLocally], </inputbuf>
</process>
<process id="processc9bf5cf468" taskpriority="0" logused="436" waitresource="PAGE: 5:1:3752363 " waittime="724" ownerId="255115931408" transactionname="user_transaction" lasttranstarted="2019-04-24T09:29:25.973" XDES="0xcf6b186408" lockMode="IX" schedulerid="25" kpid="11228" status="suspended" spid="209" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-24T09:29:25.973" lastbatchcompleted="2019-04-24T09:29:25.970" lastattention="1900-01-01T00:00:00.970" clientapp="EntityFramework" hostname="MSR-PRD-BDB04" hostpid="6248" loginname="IUSR_BuildDB" isolationlevel="read committed (2)" xactid="255115931408" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="78" stmtend="408" sqlhandle="0x020000009438fe362ae846385f4e36448d4eac52a5f052e10000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@0 int,@1 datetime,@2 datetime,@3 int)update [dbo].[CachedBuildState]
set [CachingStatusId] = @0, [StartTimeUtc] = @1, [LastUpdateTimeUtc] = @2, [MessageDetails] = null
where ([CachedBuildStateId] = @3)
</inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="40600276" dbid="5" subresource="FULL" objectname="BuildDB.dbo.CachedBuildState" id="lockcda4d62280" mode="IX" associatedObjectId="72057594075283456">
<owner-list>
<owner id="processc9bf5cf468" mode="IX" />
</owner-list>
<waiter-list>
<waiter id="processc9c0829848" mode="S" requestType="wait" />
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="3752363" dbid="5" subresource="FULL" objectname="BuildDB.dbo.CachedBuildState" id="lockd70db08700" mode="S" associatedObjectId="72057594075283456">
<owner-list>
<owner id="processc9c0829848" mode="S" />
</owner-list>
<waiter-list>
<waiter id="processc9bf5cf468" mode="IX" requestType="wait" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
답변
당신은을 sqlhandle
사용 전체 쿼리를 얻을 수 있도록 각 프레임 sys.dm_exec_sql_text
기능 :
SELECT text
FROM sys.dm_exec_sql_text(sql_handle)
예를 들어, 일부 명령문의 텍스트 가져 오기 sys.dm_exec_query_stats
(XML 보고서의 statement_start_offset, statement_end_offset, sql_handle 대신 stmtstart, stmtend, sqlhandle)
SELECT TOP (100)
CASE
WHEN s.[statement_start_offset] > 0 THEN
--The start of the active command is not at the beginning of the full command text
CASE s.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
SUBSTRING(t.TEXT, (s.[statement_start_offset]/2) + 1, 2147483647)
ELSE
--The end of the active statement is not at the end of the full command
SUBSTRING(t.TEXT, (s.[statement_start_offset]/2) + 1, (s.[statement_end_offset] - s.[statement_start_offset])/2+1)
END
ELSE
--1st part of full command is running
CASE s.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
RTRIM(LTRIM(t.[text]))
ELSE
--The end of the active statement is not at the end of the full command
LEFT(t.TEXT, (s.[statement_end_offset]/2) +1)
END
END AS [executing statement]
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(s.[sql_handle]) t
UPD
@JohnEisbrener의 합리적인 의견 덕분에 DMV의 정보는 쿼리 캐시가 정리 될 때까지 실제임을 알 수 있습니다. 따라서 캐시 정리 후 (예 : 서버를 다시 시작한 후) 해당 정보를 사용하려면 이벤트가 발생한 직후 해당 데이터를 일부 영구 테이블에 저장해야합니다.