Problema

La stessa stored procedure utilizza piani di esecuzione diversi determinanti differenti tempi di esecuzione.

Esempio

Dimostreremo come una stored procedure, a parità di parametri passati, può essere eseguita in tempi diversi a seconda del riutilizzo o meno del piano di esecuzione creato per un determinato set di parametri

Consideriamo una semplice stored procedure usp_TABLETEST

create procedure [dbo].[usp_TABLETEST] @P1 int = null, @P2 int = null

as

begin

select count(pk) from testtable where col1=@P1 and col2 = @P2

end

A. Esecuzione della stored procedure con i parametri @P1=1, @P2=20 e creazione del piano di esecuzione per tali parametri

Eseguendo la stored procedure con i parametric @P1=1, @P2=20 , vedremo un piano di esecuzione utilizzante i due indici pk_testtable e idx1 e la letture di poche righe

set statistics time on

set statistics profile on

exec usp_TABLETEST 1,20

Tempo di esecuzione:

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 87 ms.

B. Esecuzione della stored procedure con i parametri @P1=2, @P2=2 , riutilizzo del piano di esecuzione precedentemente creato e verifica del rallentamento

Eseguendo successivamente la stored procedure, con i parametri @P1=2, @P2=2, , vedremo un piano di esecuzione identico a quello precedente, ma effettuante numerose letture ed utilizzante un tempo di esecuzione superiore

set statistics time on

set statistics profile on

exec usp_TABLETEST 2,2

Tempo di esecuzione:

SQL Server Execution Times:

CPU time = 1701 ms, elapsed time = 3648 ms.

C. Esecuzione della stored procedure con i parametri @P1=2, @P2=2, creazione del piano di esecuzione per tali parametri e verifica delle buone performance

Eseguendo la stored procedure con gli stessi parametri precedenti ma dopo aver pulito la procedure cache di Sql server , vedremo un nuovo piano di esecuzione, un numero di letture inferiore e conseguente beneficio sul tempo di esecuzione

Dbcc freeproccache

set statistics time on

set statistics profile on

exec usp_TABLETEST 2,2

 

Tempo di esecuzione:

SQL Server Execution Times:

CPU time = 94 ms, elapsed time = 102 ms.

Individuazione

Catturare l’output della sys.dm_exec_query_stats, nel momento in cui la query viene eseguita lentamente

select

sql_text.text,

p.query_plan as exec_plan,

max_elapsed_time,

last_elapsed_time,

min_elapsed_time,

total_elapsed_time,

qs.execution_count,

creation_time,

last_execution_time

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(sql_handle) as sql_text

cross apply sys.dm_exec_query_plan(plan_handle) as p

where sql_text.text like '%nome stored procedure%'

Verificare le variazioni nel tempo di esecuzione della query

• Dal confronto tra max_elapsed_time , last_elapsed_time e min_elapsed time potremo verificare le variazioni nel tempo di esecuzione della stored procedure e confermare che l'output sia stato raccolto durante un'esecuzione non ottimale
• Se last_elapsed_time ≈ max_elapsed_time e min_elapsed_time << max_elapsed_time, avremo catturato informazioni durante un'esecuzione lenta della stored procedure e potremo verificare il piano di esecuzione determinante il problema In questo caso , last _elapsed_time e max_elapsed_time corrispondono esattamente all' elapsed time = 3648 ms ottenuto al punto B

Verificare il piano di esecuzione ed i parametri con cui è stato creato

• La colonna exec_plan dell'output sopra indicato, contiene il piano di esecuzione in formato xml
  In Sql server Management Studio, con un semplice click sul valore di exec_plan per la stored procedure in esame, visualizzeremo l' execution plan corrispondente in formato grafico

Da notare la presenza dell'informazione relativa a "Missing Index"

• Tasto destro sul piano di esecuzione in formato grafico , selezionare Show Execution Plan XML... e potremo visualizzare il piano di esecuzione in formato XML

 

• In formato XML sarà possibile individuare i parametri con cui il piano è stato compilato
  <ColumnReference Column="@P2" ParameterCompiledValue="(20)" />
  <ColumnReference Column="@P1" ParameterCompiledValue="(1)" />

Soluzione

Consiglio sempre di catturare le informazione sopra riportate in presenza e assenza di problemi di performance, confrontare i tempi di esecuzione, piani di esecuzione e i parametri utilizzati nella compilazione del piano, al fine di riprodurre il problema successivamente e capire quale tra le soluzioni sotto riportate potrebbero meglio adattarsi alla specifica situazione

Assicurarsi che le statistiche siano aggiornate, possibilmente tramite un fullscan (update statistics table_name with fullscan)

In questo modo , permetteremo a Sql server di avere le informazioni più recenti e complete possibile per creare un piano di esecuzione efficiente

Eseguire

Dbcc show_statistics (table_name , index name)

Verificare che Updated sia il più recente possibile e che Rows e RowsSampled siano uguali per avere le statistiche create su tutta la popolazione di dati della tabella

Di seguito alcune delle soluzioni adottate in problemi di questo tipo:

• Creazione di indici che permettano un'esecuzione ottimale per ogni set di parametri utilizzato
• Cercare nei piani di esecuzione la presenza di informazioni relative a "Missing Index" e verificare se l'aggiunta dei "Missing Index" consigliati risolve il problema
• Eseguire la stored procedure da Management Studio con i parametri indicati nei piani di esecuzione collezionati e utilizzare il Database Engine Tuning Advisor per verificare l'eventuale mancanza di indici
• Forzare la ricompilazione della stored procedure o statement al suo interno
  Si potrebbe determinare un maggiore utilizzo di CPU legato al maggiore numero di compilazioni della stored procedure o statement
• Forzare la ricompilazione dello statement all'interno della stored procedure che causa il problema di performance, utilizzando l'hint  option (recompile) a livello di statement (Query Hints)
  
  ALTER procedure [dbo].[usp_TABLETEST] @P1 int = null, @P2 int = null
  as
  begin
  select count(pk) from testtable where col1=@P1 and col2 = @P2 option (recompile)
  end
  
• Forzare la ricompilazione dell'intera stored procedure utilizzando l'opzione WITH RECOMPILE
  
  ALTER procedure [dbo].[usp_TABLETEST] @P1 int = null, @P2 int = null
  with recompile
  as
  begin
  select count(pk) from testtable where col1=@P1 and col2 = @P2
  end
  
• Utilizzare l'opzione OPTION (OPTIMIZE FOR (@param = ...) a livello di statement, per forzare la creazione di un piano di esecuzione ottimizzato per un determinato set di parametri (Query Hints)
  
  ALTER procedure [dbo].[usp_TABLETEST] @P1 int = null, @P2 int = null
  as
  begin
  select count(pk) from testtable where col1=@P1 and col2 = @P2
  option (optimize for (@P1=2, @P2=2))
  end
  
• Utilizzare all'interno della stored procedure variabili locali al posto dei parametri
  
  ALTER procedure [dbo].[usp_TABLETEST] @P1 int = null, @P2 int = null
  as
  begin
  declare @var1 int
  declare @var2 int
  set @var1=@P1
  set @var2=@P2
  select count(pk) from testtable where col1=@var1 and col2 = @var2
  end
  
• Utilizzare PlanGuide, forzando il piano di esecuzione della query all'interno della stored procedure tramite (Sp_create_plan_guide)

Raffaella Canobbio
Senior Support Escalation Engineer
Microsoft Enterprise SQL Support