Risolvere la differenza di prestazioni delle query tra l'applicazione di database e SSMS

Quando si esegue una query in un'applicazione di database, viene eseguita più lentamente rispetto alla stessa query in un'applicazione come SQL Server Management Studio (SSMS), Azure Data Studio o SQLCMD.

Questo problema può verificarsi per i seguenti motivi:

• Le query usano parametri o variabili diversi.

• Le query vengono inviate al server su reti diverse o esiste una differenza nel modo in cui le applicazioni elaborano i dati.

• Le opzioni SET nell'applicazione di database e in SSMS sono diverse.

Per risolvere il problema, seguire questa procedura:

Passaggio 1: Verificare che le query vengano inviate con gli stessi parametri o variabili

Per confrontare queste query e assicurarsi che siano identiche in ogni modo, seguire questa procedura:

1. Aprire SSMS e connetterlo al motore di database in uso.

2. Eseguire i comandi seguenti per creare una sessione eventi estesi:

   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
   

   Nota

   Sostituire i segnaposto <EventSessionName> e <FilePath> con quelli che si desidera creare.

3. Eseguire i comandi seguenti per avviare la sessione EventSessionName:

   ALTER EVENT SESSION <EventSessionName> ON SERVER
   STATE = START
   

4. Eseguire le query per riprodurre il problema.

5. Usare uno dei metodi seguenti per analizzare i dati raccolti:

   • Aprire Esplora risorse, trovare il file XEL di destinazione e fare doppio clic su di esso. Il file verrà aperto in un'altra finestra di SSMS che è possibile usare per visualizzare e analizzare.

   • In Esplora oggetti espandere Management>Extended Events>Sessions>EventSessionName, fare clic con il pulsante destro del mouse su package0.event_file e quindi scegliere Visualizza dati di destinazione.

   • Trovare il percorso dei file xel e leggere questo file usando la funzione sys.fn_xe_file_target_read_file.

6. Confrontare l'istruzione Field controllando gli eventi seguenti:

   • sp_statement_completed
   • sql_batch_completed
   • sql_statement_completed
   • rpc_completed

Per altre informazioni sulle query identiche, vedere gli esempi seguenti:

• Se le stored procedure o le funzioni hanno valori di parametro diversi, i tempi di query possono essere diversi:

  • SpUserProc @p1 = 100

  • SpUserProc @p1 = 270

• Le query seguenti sono diverse. La prima query usa la densità media dell'istogramma per la stima della cardinalità, mentre la seconda usa il passaggio dell'istogramma per la stima della cardinalità:

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

  • select * from table where c1 = 123
    

Per lo stesso motivo illustrato in precedenza, il confronto tra l'esecuzione di una stored procedure e l'esecuzione della query ad hoc equivalente (usando variabili locali) può essere diverso. È necessario confrontare istruzioni identiche.

Passaggio 2: Misurare il tempo di esecuzione nel server

Per un confronto accurato delle durate delle query, è possibile escludere il tempo di latenza di rete o il tempo di elaborazione dati specifico dell'applicazione. Usare uno dei metodi seguenti per misurare solo il tempo di esecuzione nel SQL Server:

• Eseguire la query usando SET STATISTICS TIME:

  SET STATISTICS TIME ON
  <YourQuery>
  SET STATISTICS TIME OFF
  

• Usare XEvent del passaggio 1 per esaminare la durata o il tempo trascorso di una query (classe SQL:StmtCompleteddi evento , SQL:BatchCompletedo RPC:Completed).

In alcuni casi, la differenza di tempo tra le query potrebbe essere causata da un'applicazione in esecuzione in una rete diversa o dall'applicazione stessa. Quando si confronta l'esecuzione nel server, si confronta il tempo impiegato per eseguire le query nel server.

Passaggio 3: Controllare le opzioni SET per ogni connessione

Sono disponibili opzioni SET che influiscono sul piano di query, il che significa che possono modificare la scelta del piano di query. Pertanto, se un'applicazione di database usa opzioni di set diverse da SSMS, ogni opzione di set può ottenere un piano di query diverso. Ad esempio, ARITHABORT, NUMERIC_ROUNDABORT, ROWCOUNT, FORCEPLAN e ANSI_NULLS. La differenza più comune osservata tra le applicazioni SSMS e .NET è l'opzione SET ARITHABORT . Per impostazione predefinita, l'opzione è impostata su ON in SSMS, ma su OFF nella maggior parte delle applicazioni di database. In base alle esigenze dell'applicazione, impostare ARITHABORT sulla stessa impostazione sia in SSMS che nell'applicazione per un confronto valido tra i due.

Avviso

L'impostazione predefinita di ARITHABORT per SQL Server Management Studio è ON. Le applicazioni client che impostano ARITHABORT su OFF potrebbero ricevere piani di query diversi, rendendo difficile la risoluzione dei problemi relativi alle query con prestazioni scarse. In altri casi, la stessa query potrebbe essere eseguita rapidamente in Management Studio ma lenta nell'applicazione. Per la risoluzione dei problemi relativi alle query con Management Studio, corrisponde sempre all'impostazione ARITHABORT del client.

Per un elenco di tutte le opzioni che influiscono sul piano, vedere Impostare le opzioni.

Per assicurarsi che le opzioni SET in SSMS e nell'applicazione siano le stesse per poter eseguire un confronto valido, seguire questa procedura:

1. Usare i dati raccolti nel passaggio 1.

2. Confrontare le opzioni impostate controllando gli eventi login e existing_connection, in particolare le options_text colonne delle opzioni e .