W SQL Server istnieje sposób na określenie wartości parametrów przekazywanych do wykonanej procedury składowanej

Jednym ze sposobów określenia wykonanej procedury składowanej jest użycie metod „dynamicznego zarządzania”, takich jak:

SELECT 
    sqlText.Text, req.* 
FROM 
    sys.dm_exec_requests req
OUTER APPLY 
    sys.dm_exec_sql_text(req.sql_handle) AS sqltext

Jednak wyświetla to tylko tekst instrukcji create procedury składowanej. na przykład:

CREATE PROCEDURE IMaProcedure @id int AS SELECT * FROM AllTheThings Where id = @id

Idealnie chciałbym zobaczyć, jakie parametry były dla uruchomionej procedury, które powodują, że trwa ona tak długo dla określonego zestawu parametrów naruszających.

Czy jest na to sposób? (W tym pytaniu Aaron Bertrand wspomina DBCC InputBuffer , ale nie sądzę, żeby to było odpowiednie dla tego problemu).

Informacje te - wartości parametrów w czasie wykonywania przekazywane do procedury składowanej (tj. Wywołanie RPC) lub sparametryzowane zapytanie - są dostępne tylko za pośrednictwem śledzenia SQL (i zakładam, że równoważne zdarzenie rozszerzone w nowszych wersjach programu SQL Server). Można to zobaczyć uruchamiając SQL Server Profiler (chodzi o SQL Server) i wybierając różne „Completed” wydarzeń, takich jak: RPC:Completed, SP:Completed, i SQL:BatchCompleted. Musisz także wybrać pole „TextData”, ponieważ będą tam wartości.

Różnica między moją odpowiedź i @ Kin za odpowiedź na to pytanie jest to, że @ Kin za odpowiedź (o ile się nie mylę, w którym to przypadku usunę ten) skupia się na coraz albo:

• własny plan zapytań (w którym to przypadku może zawierać informacje o parametrach środowiska wykonawczego, ale nie dotyczy innych sesji / identyfikatorów SPID), lub
• plany z DMV (w takim przypadku powinny mieć tylko skompilowane wartości parametrów, które nie są wartościami wykonawczymi).

Moja odpowiedź koncentruje się na uzyskaniu wartości parametrów dla innych aktualnie uruchomionych sesji. Korzystając z DMV, nie ma możliwości sprawdzenia, czy wartość parametru środowiska wykonawczego jest taka sama, jak wartość parametru skompilowanego. A kontekstem tego pytania jest śledzenie wartości środowiska wykonawczego zapytań przesyłanych za pośrednictwem innych sesji / identyfikatorów SPID (oraz w SQL Server 2005, podczas gdy w SQL Server 2008 wprowadzono zdarzenia rozszerzone).

Państwo może włączyć rzeczywistego planu wykonania, a następnie spojrzeć na XML plan wykonania.

Można też użyć planu explorer narzędzie SQL wartownika i zobaczyć parameterszakładkę, która będzie notować compiled valuei run time valuedla rzeczywistego planu wykonania.

Jeśli nie możesz włączyć aktualnego planu, możesz przejrzeć pamięć podręczną planu, jak opisano poniżej.

-- 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 ma rację.
SQL Profiler Trace to jedyny sposób ( bez edycji Sproc ).

Edytuj swój proces:

Jednak kolejną najlepszą rzeczą jest nieznaczna edycja danego Sproc.
Zadeklaruj zmienną DateTime na początku bieżącym czasem.
Na końcu Sproc, zarejestruj Sproc_StartTime, Sproc_EndTime i wartości parametrów w tabeli.

Możesz nawet dodać pewną logikę warunkową, aby użyć funkcji DateDiff () do rejestrowania tylko wtedy, gdy dłuższy czas był używany do przetwarzania Sproc.
Może to przyspieszyć twój Sproc i zmniejszyć zużycie miejsca przez twoją tabelę logów, gdy Sproc jest na szczycie.

Następnie masz plik dziennika, który możesz przeszukiwać i analizować przez miesiące (bez śledzenia uruchomionego w Prod).
Po zakończeniu dostrajania Sproc, po prostu usuń kilka dodanych linii logiki Timer i Logger.

Wartości parametrów planu buforowanego:

Powinienem wspomnieć, że uwzględnienie bieżących wartości parametrów planu buforowanego w tabeli dziennika może pomóc w ustaleniu, czy zwiększają one problem z wydajnością .
Używam, OPTIMIZE FORaby ustawić sposób obsługi parametrów w moim Sproc, gdy wiem, że będzie on używany do krojenia i krojenia danych.
Uważam, że użycie OPTIMIZE FORdaje spójne i szybkie wyniki przy użyciu tego samego Sproc z parametrami jak filtry opcjonalne .
To zdecydowanie jedna zmienna mniejsza do rozważenia, jeśli określisz, jak sobie z nimi poradzić.

Poniżej znajduje się przykład tego, co możesz dodać na dole swojej instrukcji Select:

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))

Zauważyłem, używając zapytania Erlanda Sommarskoga do zniszczenia planu XML i wyciągnięcia parametru ParameterCompiledValue, że pierwsze CTE „oparte na danych” nie uwzględniają planów zawierających OSTRZEŻENIA (np. Niejawne konwersje), ponieważ CHARINDEX (funkcja wbudowana) szuka pierwszego ciągu dopasowującego wyrażenie dane wejściowe (tj.) i takie ostrzeżenia używają tych samych wyrażeń / węzłów.

Proponuję zatem zastąpić tę sekcję poprawioną sekcją poniżej:

      CHARINDEX('<ParameterList>', qp.query_plan) + LEN('<ParameterList>') AS paramstart,
      CHARINDEX('</ParameterList>', qp.query_plan) AS paramend

Zmieniona sekcja:

       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