Rozwiązywanie problemów z różnicą wydajności zapytań między aplikacją bazy danych a programem SSMS

Podczas wykonywania zapytania w aplikacji bazy danych działa wolniej niż to samo zapytanie w aplikacji, takiej jak SQL Server Management Studio (SSMS), Azure Data Studio lub SQLCMD.

Przyczyny występowania tego problemu mogą być następujące:

• Zapytania używają różnych parametrów lub zmiennych.

• Zapytania są przesyłane do serwera za pośrednictwem różnych sieci lub istnieją różnice w sposobie przetwarzania danych przez aplikacje.

• Opcje ZESTAWU w aplikacji bazy danych i programie SSMS różnią się.

Aby rozwiązać problem, wykonaj następujące kroki:

Krok 1. Sprawdzanie, czy zapytania są przesyłane przy użyciu tych samych parametrów lub zmiennych

Aby porównać te zapytania i upewnić się, że są identyczne w każdy sposób, wykonaj następujące kroki:

1. Otwórz program SSMS i połącz go z aparatem bazy danych, którego używasz.

2. Uruchom następujące polecenia, aby utworzyć sesję zdarzeń rozszerzonych:

   CREATE EVENT SESSION <EventSessionName> ON SERVER
   ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)),
   ADD EVENT sqlserver.login(SET collect_options_text=(1)
       ACTION(sqlserver.client_app_name)),
   ADD EVENT sqlserver.rpc_completed,
   ADD EVENT sqlserver.sp_statement_completed(
       ACTION(sqlserver.client_app_name)),
   ADD EVENT sqlserver.sql_batch_completed(
       ACTION(sqlserver.client_app_name)),
   ADD EVENT sqlserver.sql_statement_completed(
       ACTION(sqlserver.client_app_name))
   ADD TARGET package0.event_file(SET filename=N'<FilePath>')
   WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 
   SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
   GO
   

   Uwaga 16.

   Zastąp symbole zastępcze <EventSessionName> i <FilePath> tymi, które chcesz utworzyć.

3. Uruchom następujące polecenia, aby uruchomić sesję EventSessionName:

   ALTER EVENT SESSION <EventSessionName> ON SERVER
   STATE = START
   

4. Uruchom zapytania, aby odtworzyć problem.

5. Użyj jednej z następujących metod, aby przeanalizować zebrane dane:

   • Otwórz Eksploratora Windows, znajdź docelowy plik xel i kliknij go dwukrotnie. Plik zostanie otwarty w innym oknie programu SSMS, którego można użyć do wyświetlania i analizowania.

   • W Eksplorator obiektów rozwiń węzeł Zarządzanie>sesjami>zdarzeń rozszerzonych EventSessionName>, kliknij prawym przyciskiem myszy package0.event_file, a następnie wybierz pozycję Wyświetl dane docelowe....

   • Znajdź lokalizację plików xel i odczytaj ten plik przy użyciu sys.fn_xe_file_target_read_file funkcji.

6. Porównaj instrukcję Field , sprawdzając następujące zdarzenia:

   • sp_statement_completed
   • sql_batch_completed
   • sql_statement_completed
   • rpc_completed

Aby uzyskać więcej informacji na temat identycznych zapytań, zobacz następujące przykłady:

• Jeśli procedury składowane lub funkcje mają różne wartości parametrów, czasy zapytania mogą być różne:

  • SpUserProc @p1 = 100

  • SpUserProc @p1 = 270

• Następujące zapytania różnią się. Pierwsze zapytanie używa średniej gęstości z histogramu do szacowania kardynalności, podczas gdy drugie zapytanie używa kroku histogramu do szacowania kardynalności:

  • declare @variable1 = 123
    select * from table where c1 = @variable1
    

  • select * from table where c1 = 123
    

Z tego samego powodu, jak powyżej, porównanie wykonywania procedury składowanej do wykonywania równoważnego zapytania ad hoc (przy użyciu zmiennych lokalnych) może być inne. Identyczne instrukcje muszą być porównywane.

Krok 2. Mierzenie czasu wykonywania na serwerze

Aby uzyskać dokładne porównanie czasów trwania zapytań, można wykluczyć czas opóźnienia sieci lub czas przetwarzania danych specyficzny dla aplikacji. Użyj jednej z następujących metod, aby zmierzyć tylko czas wykonywania w programie SQL Server:

• Uruchom zapytanie przy użyciu funkcji SET STATISTICS TIME:

  SET STATISTICS TIME ON
  <YourQuery>
  SET STATISTICS TIME OFF
  

• Użyj elementu XEvent z kroku 1 , aby sprawdzić czas trwania lub czas upływu zapytania (klasa SQL:StmtCompletedzdarzeń , SQL:BatchCompletedlub RPC:Completed).

W niektórych przypadkach różnica czasu między zapytaniami może być spowodowana przez jedną aplikację działającą w innej sieci lub samej aplikacji. Podczas porównywania wykonywania na serwerze porównujesz czas wykonywania zapytań na serwerze.

Krok 3. Sprawdzanie opcji SET dla każdego połączenia

Istnieją opcje SET, które mają wpływ na plan zapytania, co oznacza, że mogą zmienić wybór planu zapytania. W związku z tym jeśli aplikacja bazy danych używa różnych opcji zestawu z programu SSMS, każda opcja zestawu może uzyskać inny plan zapytania. Na przykład ARITHABORT, NUMERIC_ROUNDABORT, ROWCOUNT, FORCEPLAN i ANSI_NULLS. Najczęstszą różnicą zaobserwowaną między aplikacjami SSMS i .NET jest opcja SET ARITHABORT . Domyślnie opcja jest ustawiona na WŁ. w programie SSMS, ale w większości aplikacji bazy danych jest ustawiona wartość OFF. W zależności od potrzeb aplikacji ustaw wartość ARITHABORT na to samo ustawienie zarówno w programie SSMS, jak i w aplikacji, aby uzyskać prawidłowe porównanie między nimi.

Ostrzeżenie

Domyślne ustawienie ARITHABORT dla programu SQL Server Management Studio to ON (WŁ.). Aplikacje klienckie ustawiając wartość ARITHABORT na WYŁ., mogą otrzymywać różne plany zapytań, co utrudnia rozwiązywanie problemów z niską wydajnością zapytań. Oznacza to, że to samo zapytanie może działać szybko w programie Management Studio, ale działa wolno w aplikacji. Podczas rozwiązywania problemów z zapytaniami za pomocą programu Management Studio zawsze odpowiada ustawieniu ARITHABORT klienta.

Aby uzyskać listę wszystkich opcji wpływających na plan, zobacz Ustawianie opcji.

Aby upewnić się, że opcje SET w programie SSMS i aplikacji są takie same, aby móc wykonać prawidłowe porównanie, wykonaj następujące kroki:

1. Użyj zebranych danych w kroku 1.

2. Porównaj opcje zestawu, sprawdzając zdarzenia login i existing_connection, w szczególności options_text kolumny i opcje.