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

Quand utiliser Optimize pour les charges de travail ad hoc dans SQL Server for Perf

Les charges de travail ad hoc dans SQL Server font référence à des requêtes exécutées rarement ou une seule fois.

Contrairement aux requêtes répétitives qui bénéficient de la capacité de SQL Server à réutiliser les plans d'exécution, les requêtes ad hoc peuvent entraîner des inefficacités en termes d'utilisation de la mémoire et de gestion du cache des plans.

Leoptimiser pour les charges de travail ad hocCette option est spécialement conçue pour remédier à ces inefficacités.

Comment ça marche

Lorsqu'il est activé, leoptimiser pour les charges de travail ad hocL'option modifie le comportement de SQL Server dans la mise en cache des plans d'exécution pour les requêtes à usage unique. Au lieu de stocker le plan d'exécution complet lors de la première exécution, SQL Server stocke un stub ou une représentation compacte du plan. Cette approche réduit considérablement la consommation de mémoire dans les environnements où les requêtes ad hoc sont répandues.

Critères de décision pour l'habilitation

Pression de la mémoire

Si votre serveur subit une pression mémoire, l'activation de cette option peut atténuer une partie du stress en réduisant l'empreinte mémoire du cache du plan.

Nature des charges de travail

Utilisez des outils de surveillance des performances et des DMV (Dynamic Management Views) comme

Apprendre encore plus:Comment diviser efficacement une chaîne à l'aide de la fonction STRING_SPLIT dans le serveur SQL

sys.dm_exec_cached_plans

et

sys.dm_exec_query_stats

Les systèmes comportant un volume élevé de requêtes uniques et ponctuelles en bénéficieront le plus. Pour les systèmes exécutant principalement un petit ensemble de requêtes répétées, les avantages peuvent être moins prononcés.

Analyse des performances

pour analyser la nature de vos charges de travail et l'impact des requêtes ad hoc sur votre système.

Activation de l'option :

-- 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;

Implications pratiques


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';

Dépannage et optimisation

Test des performances des requêtes ad hoc

Ce code exécute une simple requête ad hoc avant et après avoir activé l'option de comparaison des performances :

 

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';

Bien que cette option puisse améliorer les performances, elle complique également l'analyse des plans d'exécution pour les requêtes ad hoc. Étant donné que seul un stub de plan est stocké après la première exécution, une analyse détaillée nécessite des étapes supplémentaires pour capturer l'intégralité du plan d'exécution.

  • Stratégie de mise en œuvre

Tenez compte des caractéristiques de la charge de travail de votre serveur avant d'activer cette option. Ce n’est pas une solution universelle mais peut être très efficace dans le bon contexte.

  • Surveillance et ajustement

Après l'activation, surveillez les performances de votre serveur et la taille du cache de votre plan. Les ajustements doivent être basés sur des données empiriques et des objectifs de performance spécifiques.

Lire aussi