optimize for adhoc workloads

Laisser une réponse

SQL Server maintient un cache de plans d’exécution. Ces plans permettent au moteur de ne pas ré-optimiser les requêtes si elles sont exécutées une deuxième fois. C’est valable bien sûr pour les procédures stockées, mais aussi pour les requêtes ad-hoc. Le problème avec les requêtes ad-hoc (celles qui sont générées dans le code de l’application au lieu de faire un appel de procédure stockée), c’est que le plan concerne une requête, et non pas une requête paramétrée. En d’autres termes, ces deux requêtes :

SELECT * FROM dbo.Client WHERE Nom = 'MAUGHAN';
SELECT * FROM dbo.Client WHERE Nom = 'LLOYD';

Vont générer deux plans en mémoire (probablement identiques), parce que SQL Server ne paramétrise pas la requête automatiquement, car le plan pourrait être différent selon le nombre de valeur à retourner par paramètre.
Nous pouvons voir la taille occupée par ces plans qui ne sont utilisés qu’une fois à l’aide de la requête suivante :

SELECT SUM(CAST(cp.size_in_bytes as bigint)) / 1024 / 1024 as adhoc_once_mb
FROM sys.dm_exec_cached_plans AS cp
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
OPTION (RECOMPILE);

Pour économiser cette mémoire, une option de l’instance disponible à partir de SQL Server 2008 peut être activée. Son nom est « optimize for adhoc workloads ». Vous la trouvez dans les propriétés de l’instance, ou vous pouvez la modifier comme suit :

SP_CONFIGURE 'show advanced options',1
RECONFIGURE
GO

SP_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE
GO

Cette option modifie le comportement de SQL Server de la manière suivante : lorsqu’un plan de requête ad-hoc est calculé, il n’est plus directement stocké dans le cache, seul l’est un résumé du plan (un stub) qui permettra de reconnaître le même plan ensuite. Si le même plan doit être calculé une deuxième fois, il sera cette fois gardé en cache.
Je vous recommande d’activer cette option systématiquement sur tous vos serveurs SQL.
Voici une requête synoptique pour vous rendre compte de la situation sur votre serveur:

SELECT
    cacheobjtype,
    objtype,
    'total' as [type],
    SUM(CAST(size_in_bytes as bigint)) / 1024 / 1014 as size_in_mb
FROM sys.dm_exec_cached_plans
GROUP BY cacheobjtype, objtype
UNION ALL
SELECT
    cacheobjtype,
    objtype,
    '1x',
    SUM(CAST(size_in_bytes as bigint)) / 1024 / 1014 as size_in_mb
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1
GROUP BY cacheobjtype, objtype
ORDER BY cacheobjtype, objtype, [type];

Laisser un commentaire