Uwaga
Dostęp do tej strony wymaga autoryzacji. Może spróbować zalogować się lub zmienić katalogi.
Dostęp do tej strony wymaga autoryzacji. Możesz spróbować zmienić katalogi.
Dotyczy:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
W tym artykule omówiono sposób monitorowania wydajności natywnie skompilowanych procedur składowanych i innych natywnie skompilowanych modułów języka T-SQL.
Używanie zdarzeń rozszerzonych
Użyj sp_statement_completed rozszerzonego zdarzenia, aby prześledzić wykonanie zapytania. Utwórz rozszerzoną sesję zdarzeń z tym zdarzeniem, opcjonalnie z filtrem na object_id dla określonej natywnie skompilowanej procedury składowanej. Zdarzenie rozszerzone jest wywoływane po wykonaniu każdego zapytania. Czas CPU i czas trwania zgłaszane przez zdarzenie rozszerzone wskazują, ile czasu CPU użyło zapytanie oraz czas jego wykonania. Natywnie skompilowana procedura składowana, która używa dużo czasu procesora, może mieć problemy z wydajnością.
Do zbadania zapytania można użyć line_numberwraz z object_id w zdarzeniu rozszerzonym. Poniższe zapytanie może służyć do pobrania definicji procedury. Numer wiersza może służyć do identyfikowania zapytania w definicji:
SELECT [definition]
FROM sys.sql_modules
WHERE object_id=object_id;
Korzystanie z widoków zarządzania danymi i magazynu zapytań
Programy SQL Server i Azure SQL Database obsługują zbieranie statystyk wykonywania dla natywnie skompilowanych procedur składowanych, zarówno na poziomie procedury, jak i na poziomie zapytania. Zbieranie statystyk wykonywania nie jest domyślnie włączone z powodu wpływu na wydajność.
Statystyki wykonywania są odzwierciedlane w widokach systemu sys.dm_exec_procedure_stats i sys.dm_exec_query_stats, a także w Magazyn zapytań.
Statystyki Wykonania Procedure-Level
serwera SQL Server: włączanie lub wyłączanie zbierania statystyk dotyczących natywnie kompilowanych procedur na poziomie procedury, przy użyciu sys.sp_xtp_control_proc_exec_stats (Transact-SQL). Poniższa instrukcja umożliwia zbieranie statystyk wykonywania na poziomie procedury dla wszystkich natywnie skompilowanych modułów języka T-SQL w bieżącym wystąpieniu:
EXEC sys.sp_xtp_control_proc_exec_stats 1
bazy danych Azure SQL Database i SQL Server: włączanie lub wyłączanie zbierania statystyk w natywnie skompilowanych procedurach składowanych na poziomie procedury przy użyciu opcji konfiguracji w zakresie bazy danych XTP_PROCEDURE_EXECUTION_STATISTICS. Poniższa instrukcja umożliwia zbieranie statystyk wykonywania na poziomie procedury dla wszystkich natywnie skompilowanych modułów języka T-SQL w bieżącej bazie danych:
ALTER DATABASE SCOPED CONFIGURATION SET XTP_PROCEDURE_EXECUTION_STATISTICS = ON;
Statystyki wykonania Query-Level
SQL Server: Włączanie lub wyłączanie zbierania statystyk dla natywnie skompilowanych procedur składowanych na poziomie zapytań za pomocą sys.sp_xtp_control_query_exec_stats (Transact-SQL). Poniższa instrukcja umożliwia zbieranie statystyk wykonywania na poziomie zapytania dla wszystkich natywnie skompilowanych modułów języka T-SQL w bieżącym wystąpieniu:
EXEC sys.sp_xtp_control_query_exec_stats 1
Azure SQL Database i SQL Server: włączanie lub wyłączanie zbierania statystyk na natywnie skompilowanych procedurach składowanych na poziomie instrukcji przy użyciu opcji konfiguracji o zakresie bazy danych XTP_QUERY_EXECUTION_STATISTICS. Poniższa instrukcja umożliwia zbieranie statystyk wykonywania na poziomie zapytania dla wszystkich natywnie skompilowanych modułów języka T-SQL w bieżącej bazie danych:
ALTER DATABASE SCOPED CONFIGURATION SET XTP_QUERY_EXECUTION_STATISTICS = ON;
Przykładowe zapytania
Po zebraniu statystyk, statystyki wykonywania dla natywnie skompilowanych procedur składowanych mogą być odpytane za pomocą sys.dm_exec_procedure_stats (Transact-SQL), a statystyki dla zapytań za pomocą sys.dm_exec_query_stats (Transact-SQL).
Następujące zapytanie zwraca nazwy procedur i statystyki wykonywania dla natywnie skompilowanych procedur składowanych w bieżącej bazie danych po zbieraniu statystyk:
SELECT object_id, object_name(object_id) AS 'object name',
cached_time, last_execution_time, execution_count,
total_worker_time, last_worker_time,
min_worker_time, max_worker_time,
total_elapsed_time, last_elapsed_time,
min_elapsed_time, max_elapsed_time
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;
Następujące zapytanie zwraca tekst zapytania oraz statystyki wykonywania dla wszystkich zapytań w natywnie skompilowanych procedurach składowanych w bieżącej bazie danych, dla których zebrano statystyki uporządkowane według łącznego czasu roboczego w kolejności malejącej:
SELECT st.objectid,
OBJECT_NAME(st.objectid) AS 'object name',
SUBSTRING(
st.text,
(qs.statement_start_offset/2) + 1,
((qs.statement_end_offset-qs.statement_start_offset)/2) + 1
) AS 'query text',
qs.creation_time, qs.last_execution_time, qs.execution_count,
qs.total_worker_time, qs.last_worker_time, qs.min_worker_time,
qs.max_worker_time, qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE database_id = DB_ID()
AND object_id IN (SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1)
ORDER BY total_worker_time desc;
Plany wykonywania zapytań
Natywnie skompilowane procedury składowane obsługują SHOWPLAN_XML (szacowany plan wykonania). Szacowany plan wykonania może służyć do sprawdzania planu zapytania w celu znalezienia problemów z nieprawidłowym planem. Typowe przyczyny nieprawidłowych planów to:
Statystyki nie zostały zaktualizowane przed utworzeniem procedury.
Brakujące indeksy
Kod XML programu Showplan jest uzyskiwany przez wykonanie następującego języka Transact-SQL:
SET SHOWPLAN_XML ON
GO
EXEC my_proc
GO
SET SHOWPLAN_XML OFF
GO
Alternatywnie w programie SQL Server Management Studio wybierz nazwę procedury i kliknij Wyświetl szacowany plan wykonania.
Szacowany plan wykonywania dla natywnie skompilowanych procedur składowanych przedstawia operatory zapytań i wyrażenia dla zapytań w procedurze. Program SQL Server 2014 (12.x) nie obsługuje wszystkich atrybutów SHOWPLAN_XML dla natywnie skompilowanych procedur składowanych. Na przykład atrybuty związane z kosztowaniem optymalizatora zapytań nie są częścią SHOWPLAN_XML procedury.