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 jest ono uruchamiane wolniej niż to samo zapytanie w aplikacji, takie 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 istnieje różnica w sposobie przetwarzania danych przez aplikacje.

• Opcje SET w aplikacji bazy danych i programie SSMS są różne.

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

Krok 1. Sprawdzanie, czy zapytania są przesyłane z tymi samymi parametrami lub zmiennymi

Aby porównać te zapytania i upewnić się, że są identyczne pod każdym względem, wykonaj następujące kroki:

1. Otwórz program SSMS i połącz go z używanym aparatem bazy danych.

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

   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ł Zdarzeniarozszerzone>>zarządzania>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 funkcji sys.fn_xe_file_target_read_file.

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 są różne. 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, co powyżej, porównanie wykonywania procedury składowanej z wykonywaniem równoważnego zapytania ad hoc (przy użyciu zmiennych lokalnych) może być inne. Identyczne instrukcje należy porównać.

Krok 2. Mierzenie czasu wykonywania na serwerze

W celu dokładnego porównania czasu trwania zapytania 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 na SQL Server:

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

  SET STATISTICS TIME ON
  <YourQuery>
  SET STATISTICS TIME OFF
  

• Użyj elementu XEvent z kroku 1 , aby zbadać czas trwania lub czas, który upłynął dla 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 w 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 zapytań, 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 niż 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ą między aplikacjami SSMS i .NET jest opcja SET ARITHABORT . Domyślnie opcja jest ustawiona na WŁĄCZONE w programie SSMS, ale w większości aplikacji bazy danych jest ustawiona na wartość WYŁĄCZONE. W zależności od potrzeb aplikacji ustaw ustawienie ARITHABORT na to samo ustawienie zarówno w programie SSMS, jak i w aplikacji, aby uzyskać prawidłowe porównanie tych dwóch elementów.

Ostrzeżenie

Domyślne ustawienie ARITHABORT dla SQL Server Management Studio jest włączone. Aplikacje klienckie ustawiące ustawienie ARITHABORT na WARTOŚĆ OFF mogą otrzymywać różne plany zapytań, co utrudnia rozwiązywanie problemów z nieprawidłowo działającymi zapytaniami. Oznacza to, że to samo zapytanie może być wykonywane szybko w programie Management Studio, ale działa wolno w aplikacji. Podczas rozwiązywania problemów z zapytaniami w programie Management Studio zawsze należy dopasować ustawienie 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 kolumny zdarzeń login i existing_connection, a w szczególności options_text kolumny i opcje.