Nous utilisons des cookies pour garantir que nous vous offrons la meilleure expérience sur notre site Web.

Quando utilizzare Ottimizza per carichi di lavoro ad hoc in SQL Server per Perf

I carichi di lavoro ad hoc in SQL Server si riferiscono a query eseguite raramente o solo una volta.

A differenza delle query ripetitive che traggono vantaggio dalla capacità di SQL Server di riutilizzare i piani di esecuzione, le query ad hoc possono portare a inefficienze in termini di utilizzo della memoria e pianificazione della gestione della cache.

ILottimizzare per carichi di lavoro ad hocL'opzione è specificamente progettata per risolvere queste inefficienze.

Come funziona

Quando abilitato, ilottimizzare per carichi di lavoro ad hocL'opzione modifica il comportamento di SQL Server nella memorizzazione nella cache dei piani di esecuzione per le query monouso. Invece di archiviare l'intero piano di esecuzione alla prima esecuzione, SQL Server archivia uno stub o una rappresentazione compatta del piano. Questo approccio riduce significativamente il consumo di memoria negli ambienti in cui prevalgono le query ad hoc.

Criteri decisionali per l'abilitazione

Pressione della memoria

Se il tuo server è sottoposto a un sovraccarico di memoria, l'attivazione di questa opzione può alleviare parte dello stress riducendo l'impronta di memoria della cache del piano.

Natura dei carichi di lavoro

Utilizza strumenti di monitoraggio delle prestazioni e DMV (Dynamic Management Views) come

Saperne di più:Come dividere efficacemente una stringa utilizzando la funzione STRING_SPLIT nel server SQL

sys.dm_exec_cached_plans

E

sys.dm_exec_query_stats

I sistemi con un volume elevato di query uniche e una tantum sono quelli che trarranno i maggiori vantaggi. Per i sistemi che eseguono principalmente un piccolo insieme di query ripetute, i vantaggi potrebbero essere meno pronunciati.

Analisi delle prestazioni

per analizzare la natura dei carichi di lavoro e l'impatto delle query ad hoc sul sistema.

Abilitazione dell'opzione:

-- Connect to your SQL Server instance

DECLARE @Servername nvarchar(128) = 'YOUR_SERVER_NAME';

DECLARE @UserName nvarchar(50) = 'YOUR_USERNAME';

DECLARE @Password nvarchar(50) = 'YOUR_PASSWORD';

CONNECT @Servername, @UserName, @Password;

Implicazioni pratiche


DECLARE @BeforeSize int; EXEC sys.dm_os_memory_clerks WITH OBJECT = 'plan cache' OUTPUT, size = @BeforeSize; PRINT 'Plan cache size before: ' + CAST(@BeforeSize AS nvarchar(10)) + ' KB';
 

DECLARE @AfterSize int; EXEC sys.dm_os_memory_clerks WITH OBJECT = 'plan cache' OUTPUT, size = @AfterSize; PRINT 'Plan cache size after: ' + CAST(@AfterSize AS nvarchar(10)) + ' KB'; 

 

DECLARE @Difference int = @AfterSize - @BeforeSize; PRINT 'Size difference: ' + CAST(@Difference AS nvarchar(10)) + ' KB';

Risoluzione dei problemi e ottimizzazione

Test delle prestazioni delle query ad hoc

Questo codice esegue una semplice query ad hoc prima e dopo aver abilitato l'opzione per confrontare le prestazioni:

 

DECLARE @StartTime datetime = GETDATE(); SELECT TOP 100 * FROM YourTable; DECLARE @EndTime datetime = GETDATE(); PRINT 'Execution time before: ' + CAST(DATEDIFF(ms, @StartTime, @EndTime) AS nvarchar(10)) + ' ms'; 

 

 

DECLARE @StartTime datetime = GETDATE(); SELECT TOP 100 * FROM YourTable; DECLARE @EndTime datetime = GETDATE(); PRINT 'Execution time after: ' + CAST(DATEDIFF(ms, @StartTime, @EndTime) AS nvarchar(10)) + ' ms';

Sebbene questa opzione possa migliorare le prestazioni, complica anche l'analisi dei piani di esecuzione per le query ad hoc. Poiché dopo la prima esecuzione viene archiviato solo uno stub del piano, l'analisi dettagliata richiede passaggi aggiuntivi per acquisire l'intero piano di esecuzione.

  • Strategia di implementazione

Considera le caratteristiche del carico di lavoro del tuo server prima di abilitare questa opzione. Non è una soluzione universale ma può essere molto efficace nel giusto contesto.

  • Monitoraggio e regolazione

Dopo l'abilitazione, monitora le prestazioni del tuo server e la dimensione della cache del tuo piano. Gli aggiustamenti dovrebbero basarsi su dati empirici e obiettivi di performance specifici.

Lire aussi