Jak profilować przechowywane procedury

Korzystam z programu SQL Server 2012 i zastanawiałem się, jak profilować procedury przechowywane

Na przykład, czy profiler może przechwycić każdą instrukcję SQL w procedurze przechowywanej, co to jest i ile czasu zajmuje uruchomienie itp.?

Próbuję zdiagnozować procedury składowane replikacji scalania i musi to zostać przechwycone jako część pełnego uruchomienia agenta scalania. Wydaje się, że nie można pobrać procedury składowanej z problemami z wydajnością i uruchomić ją ponownie, ponieważ w tym momencie nie jest ona wolna.

Odpowiedź Kevina opisuje, jakie zdarzenia należy przechwytywać w programie SQL Trace / SQL Profiler. Aby nieco rozwinąć tę odpowiedź - SP:StmtCompletedpokaże, jak brzmi każda instrukcja w ramach procedury składowanej.

Również jeśli jesteś zajęty w systemie i próbujesz zdiagnozować problem z wydajnością, powinieneś być ostrożny z SQL Profiler. SQL Profiler działa znacznie wolniej niż śledzenie pliku lub korzystanie z rozszerzonych zdarzeń. Ten post na blogu autorstwa Jonathana Kehayiasa pokazuje około 90% narzutu na wydajność systemu wynikającego z używania SQL Profiler i około 10% narzutu od śledzenia do pliku. Mniej dla wydarzeń rozszerzonych. Dlatego zwykle zaleca się, aby nie uruchamiać samego SQL Profiler

Chociaż informacje te są dostępne za pośrednictwem Rozszerzonych zdarzeń, sugerowałbym nadal używanie śledzenia SQL (technologii stojącej za SQL Profiler), ale zamiast tego śledzenie pliku(jeśli chcesz zainwestować w naukę i korzystanie z rozszerzonych zdarzeń, to byłaby właściwa droga, w przyszłej wersji SQL Server SQL Trace zniknie, a wszystko, co będziemy mieć, to Rozszerzone zdarzenia). Sugeruję również, abyś przefiltrował przez przycisk Filtry kolumnowe jak najwięcej szumów tła, aby upewnić się, że przechwytujesz tylko to, co jest konieczne. Możesz skonfigurować śledzenie za pomocą narzędzia Profiler, wykonując czynności opisane przez Kevina w dobrej odpowiedzi, a następnie dodaj filtr z tego samego GUI. Następnie możesz wyeksportować dane śledzenia jako skrypt i uruchomić ten skrypt na śledzeniu programu SQL Server do pliku w folderze, który nie zawiera bazy danych ani plików dziennika transakcji. Aby wyeksportować, po prostu skonfiguruj swój ślad, uruchom go na kilka sekund, aby upewnić się, że przechwytujesz to, czego chcesz, zatrzymaj go, a następnie przejdź do paska menu i File->Export-> Script Trace Definitioni zapisz plik. Następnie otwórz ten plik w nowym oknie zapytania na serwerze, który chcesz śledzić. Możesz dowiedzieć się więcej o opcjach i definicjach skryptu, który utworzyłeś, przeglądając artykuły pomocy dotyczące różnych procedur przechowywanych używanych w skrypcie, który właśnie utworzyłeś, zaczynając tutaj .

Jeśli masz czas i chcesz się uczyć, możesz również przeczytać artykuły na temat wydarzeń rozszerzonych i dowiedzieć się, jak uchwycić te informacje. Jonathan Kehayias to świetne źródło postów na blogach, gdy jesteś gotowy, aby tam zacząć.

Można przechwytywać poszczególne instrukcje w procedurze przechowywanej za pomocą SQL Server Profiler. Aby to zrobić, na karcie Wybór wydarzeń kliknij pole wyboru „Pokaż wszystkie zdarzenia”. Następnie przewiń w dół do kategorii Procedury składowane i zaznacz pole obok SP: StmtCompleted . Jeśli masz również wybrane zdarzenia SQL: BatchStarted i SQL: BatchCompleted , możesz uzyskać od początku do końca obraz przebiegu procedury składowanej, wiążąc wszystko razem za pomocą SPID.

Pomocne może być także sprawdzenie pamięci podręcznej planu i sprawdzenie, czy można uzyskać plan zapytań dla wolno wykonywanej procedury. Możesz zacząć od czegoś takiego:

SELECT 
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM 
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE 
    cp.objtype = 'Proc'
    AND OBJECT_NAME(st.objectid,st.dbid) = 'YourStoredProcedure';