실행중인 저장 프로 시저를 결정하는 한 가지 방법은 다음과 같이 “동적 관리”방법을 사용하는 것입니다.
SELECT
sqlText.Text, req.*
FROM
sys.dm_exec_requests req
OUTER APPLY
sys.dm_exec_sql_text(req.sql_handle) AS sqltext그러나 이것은 저장 프로 시저의 create 문 텍스트 만 표시합니다. 예 :
CREATE PROCEDURE IMaProcedure @id int AS SELECT * FROM AllTheThings Where id = @id이상적으로는 특정 위반 매개 변수 집합에 대해 너무 오래 실행되는 실행중인 절차에 대한 매개 변수가 무엇인지보고 싶습니다.
그렇게 할 방법이 있습니까? ( 이 질문에서 Aaron Bertrand 는 DBCC InputBuffer에 대해 언급 했지만이 문제에 적합하다고 생각하지 않습니다.)
답변
저장 프로 시저 (예 : RPC 호출)에 전달 된 런타임 매개 변수 값 또는 매개 변수화 된 쿼리와 같은이 정보는 SQL 추적을 통해서만 사용할 수 있습니다. : 당신은 (그것을 SQL Server와 함께 제공) SQL Server 프로파일 러를 실행하고 다음과 같은 다양한 ‘완료’이벤트를 선택하여 볼 수 있습니다 RPC:Completed, SP:Completed그리고 SQL:BatchCompleted. 값이있을 때 “TextData”필드도 선택해야합니다.
이 질문에 대한 내 대답과 @Kin의 대답 의 차이점은 @Kin의 대답 (실수하지 않으면이를 제거하지 않는 한)은 다음 중 하나를 얻는 데 중점을 둡니다.
- 자체 쿼리 계획 (이 경우 런타임 매개 변수 정보를 포함 할 수 있지만 다른 세션 / SPID는 포함 할 수 없음) 또는
- DMV에에서 계획 (이 경우 그들이 해야 에만 런타임 값이 아닌 컴파일 된 매개 변수 값을 가지고).
내 대답 은 현재 실행중인 다른 세션 의 매개 변수 값을 얻는 데 중점을 둡니다 . DMV에 의존 할 때 런타임 매개 변수 값이 컴파일 된 매개 변수 값과 같은지 알 수있는 방법이 없습니다. 이 질문의 맥락은 다른 세션 / SPID를 통해 제출 된 쿼리의 런타임 값을 추적하는 것입니다 (SQL Server 2005에서는 확장 이벤트가 SQL Server 2008에 도입 됨).
답변
실제 실행 계획을 켠 다음 실행 계획 XML을 볼 수 있습니다 .
또는 당신이 사용할 수있는 SQL 보초의 계획 탐색기 도구를 하고 볼 parameters을 나열합니다 탭 compiled value과 run time value실제 실행 계획을.
실제 계획을 켤 수없는 경우 아래 설명 된대로 계획 캐시를 살펴볼 수 있습니다.
-- borrowed from Erland Sommarskog
-- Link : http://www.sommarskog.se/query-plan-mysteries.html#dmvgettingplans
-- Remember that you are looking at the estimated plan so the actual no. of rows and actual executions wont be there ! <-- Important why a particular plan is bad.
DECLARE @dbname nvarchar(256),
@procname nvarchar(256)
SELECT @dbname = 'Northwind', -- Your DB name
@procname = 'dbo.List_orders_11' -- The SP that you want to get parameters for !
; WITH basedata AS (
SELECT qs.statement_start_offset/2 AS stmt_start,
qs.statement_end_offset/2 AS stmt_end,
est.encrypted AS isencrypted, est.text AS sqltext,
epa.value AS set_options, qp.query_plan,
charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>')
AS paramstart,
charindex('</ParameterList>', qp.query_plan) AS paramend
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle,
qs.statement_start_offset,
qs.statement_end_offset) qp
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa
WHERE est.objectid = object_id (@procname)
AND est.dbid = db_id(@dbname)
AND epa.attribute = 'set_options'
), next_level AS (
SELECT stmt_start, set_options, query_plan,
CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'
WHEN stmt_start >= 0
THEN substring(sqltext, stmt_start + 1,
CASE stmt_end
WHEN 0 THEN datalength(sqltext)
ELSE stmt_end - stmt_start + 1
END)
END AS Statement,
CASE WHEN paramend > paramstart
THEN CAST (substring(query_plan, paramstart,
paramend - paramstart) AS xml)
END AS params
FROM basedata
)
SELECT set_options AS [SET], n.stmt_start AS Pos, n.Statement,
CR.c.value('@Column', 'nvarchar(128)') AS Parameter,
CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value],
CAST (query_plan AS xml) AS [Query plan]
FROM next_level n
CROSS APPLY n.params.nodes('ColumnReference') AS CR(c)
ORDER BY n.set_options, n.stmt_start, Parameter답변
@SolomonRutzky가 맞습니다.
SQL 프로파일 러 추적은 Sproc 편집없이 유일한 방법 입니다.
Sproc을 편집하십시오.
그러나 다음으로 가장 좋은 방법은 해당 Sproc을 약간 편집하는 것입니다.
현재 시간으로 시작할 때 DateTime 변수를 선언하십시오.
Sproc의 끝에서 Sproc_StartTime, Sproc_EndTime 및 매개 변수 값을 테이블에 기록하십시오.
Sproc 처리에 오랜 시간이 사용 된 경우에만 로깅에 DateDiff ()를 사용하도록 조건부 논리를 추가 할 수도 있습니다.
이로 인해 Sproc 속도가 향상되고 Sproc이 최고 속도로 실행 중일 때 로그 테이블의 공간 소비가 줄어들 수 있습니다.
그런 다음 몇 달에 걸쳐 Prod에서 추적을 실행하지 않고 쿼리 및 분석 할 수있는 로그 파일이 있습니다.
Sproc 조정이 끝나면 추가 한 몇 줄의 타이머 및 로거 로직을 삭제하십시오.
캐시 된 계획 매개 변수 값 :
로그 테이블에 Current Cached-Plan 매개 변수 값을 포함 시키면 성능 문제 가 복합적 인지 판단하는 데 도움이 될 수 있습니다 .
내가 사용하는 OPTIMIZE FOR나는 그것이 슬라이싱과 다이 싱 데이터에 사용됩니다 알고 내 SPROC의 매개 변수를 처리하는 방법을 설정합니다.
내가 사용하는 것을 발견 OPTIMIZE FOR일관된 수익률과 빠른 결과이 같은 매개 변수와 같은 SPROC를 사용할 때 옵션 필터 .
처리 방법을 지정하면 고려할 변수가 하나 줄어 듭니다.
아래는 Select-Statement의 맨 아래에 추가 할 수있는 예입니다.
OPTION(OPTIMIZE FOR (@SiteID = 'ABC',
@LocationID = NULL, @DepartmentID = NULL,
@EmployeeID = NULL, @CustomerID = NULL,
@ProductID = NULL, @OrderID = NULL, @OrderStatusID = NULL,
@IncludedCancelledOrders = 1,
@StartDate UNKNOWN, @EndDate UNKNOWN))답변
Erland Sommarskog의 쿼리를 사용하여 계획 XML을 파쇄 하고 CHARINDEX (내장 함수)가 첫 번째 표현식 일치 문자열을 찾을 때 첫 번째 “basedata”CTE가 WARNINGS (예 : 암시 적 변환)가있는 계획을 설명하지 않는 ParameterCompiledValue를 가져 오는 경우 입력 (예 🙂 및 이러한 경고는 동일한 문구 / 노드를 사용합니다.
따라서이 섹션을 아래의 수정 된 섹션으로 교체 할 것을 제안합니다.
CHARINDEX('<ParameterList>', qp.query_plan) + LEN('<ParameterList>') AS paramstart,
CHARINDEX('</ParameterList>', qp.query_plan) AS paramend수정 된 섹션 :
CHARINDEX('<ParameterList><ColumnReference', qp.query_plan) + LEN('<ParameterList>') AS paramstart,
CHARINDEX('</ParameterList></QueryPlan>', qp.query_plan) AS paramend답변
SELECT DB_NAME(req.database_id),
sqltext.TEXT,
req.session_id,
req.status,
req.start_time,
req.command,
req.cpu_time,
req.total_elapsed_time , REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT(VARCHAR(MAX), CONVERT(XML, REPLACE( query_plan, 'xmlns="','xmlns1="')).query('// ParameterList/ColumnReference')),
'<ColumnReference Column="','declare '),
'" ParameterDataType="',' '),
'" ParameterCompiledValue="(',' = '),
')"/>', CONCAT(';', CHAR(10) , CHAR(13))) ParameterList
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, statement_start_offset, statement_end_offset) qp
order by req.total_elapsed_time desc