Optimize for ad hoc workloads : Requêtes Ad Hoc et valeurs littérales

28 octobre 2016

Commentaires fermés

Dans un article dédié au tuning d’instance, l’activation de l’option “optimize for ad hoc workloads” a été évoquée. Nous allons mesurer l’impact de cette option sur le moteur.

Démo 1 : “optimize for ad hoc workloads” désactivé

1) En premier lieu, on ouvre une première session pour vider les caches et on désactive l’option “optimize for ad hoc workloads “:

DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE 

USE [master]
GO
EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

DBCC DROPCLEANBUFFERS

DBCC FREESYSTEMCACHE('ALL')

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

USE [master]

EXEC sp_configure 'Show Advanced Options', 1;

RECONFIGURE;

EXEC sp_configure;

EXEC sys.sp_configure N'optimize for ad hoc workloads', N'0'

RECONFIGURE WITH OVERRIDE

2) On ouvre une seconde session où l’on génère un lot de requêtes auxquelles on passe des valeurs littérales (VS paramètre) :

SELECT	TOP 100 'SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = ' + CAST(SalesOrderID AS VARCHAR) + CHAR(10) + 'GO'
FROM	Sales.SalesOrderDetail	
GROUP BY SalesOrderID
ORDER BY NEWID()

SELECT TOP 100 'SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = ' + CAST(SalesOrderID AS VARCHAR) + CHAR(10) + 'GO'

FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID

ORDER BY NEWID()

Copier coller et exécuter le résultat, soit le lot de requêtes obtenu sur la base de données AdventureWork, toujours depuis la seconde session.

3) Une fois l’exécution du lot terminée, retour à la première session pour consulter le cache des plans d’exécution :

SELECT	*
FROM	sys.dm_exec_cached_plans ecp
CROSS	APPLY sys.dm_exec_sql_text(ecp.plan_handle) sql
WHERE	sql.text LIKE 'SELECT * FROM Sales.SalesOrderDetail%'

SELECT *

FROM sys.dm_exec_cached_plans ecp

CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) sql

WHERE sql.text LIKE 'SELECT * FROM Sales.SalesOrderDetail%'

Observation : Il y’a un plan d’exécution par requête bien qu’il n’y est qu’une valeur littérale qui varie et la taille par plan est plutôt conséquente :

Démo 2 : “optimize for ad hoc workloads” activé

4) On revient à la première session pour vider à nouveau les caches MAIS cette fois, on active l’option “optimize for ad hoc workloads “:

DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE 

EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

DBCC DROPCLEANBUFFERS

DBCC FREESYSTEMCACHE('ALL')

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'

RECONFIGURE WITH OVERRIDE

5) On exécute à nouveau le lot de requêtes de l’étape 2, toujours depuis la seconde session

6) Une fois l’exécution du lot terminée, on revient à la première session pour consulter le cache des plans d’exécution :

SELECT	*
FROM	sys.dm_exec_cached_plans ecp
CROSS	APPLY sys.dm_exec_sql_text(ecp.plan_handle) sql
WHERE	sql.text LIKE 'SELECT * FROM Sales.SalesOrderDetail%'

SELECT *

FROM sys.dm_exec_cached_plans ecp

CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) sql

WHERE sql.text LIKE 'SELECT * FROM Sales.SalesOrderDetail%'

Observation : Cette fois, il y’a un sous-plan pour l’ensemble des requêtes et la taille de plan a été revue à la baisse, d’où l’importance de cette option.

Démo 3 : requête préparée

7) Remplaçons la seconde session pour générer un lot de requêtes préparées ayant le même pattern auxquelles on passe un paramètre (VS valeur littérale) :

SELECT	TOP 100 '

DECLARE @SalesOrderID INT = ' + CAST(SalesOrderID AS VARCHAR) + ', 
		@SQLQuery AS NVARCHAR(1500)

DECLARE @ParameterDefinition AS NVARCHAR(100) = N''@SalesOrderID INT''
SET @SQLQuery =  ''SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = @SalesOrderID''
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @SalesOrderID
' + CHAR(10) + 'GO'
FROM	Sales.SalesOrderDetail	
GROUP BY SalesOrderID
ORDER BY NEWID()

SELECT	*
FROM	sys.dm_exec_cached_plans ecp
CROSS	APPLY sys.dm_exec_sql_text(ecp.plan_handle) sql
WHERE	sql.text LIKE '(@SalesOrderID INT)SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = @SalesOrderID%'

SELECT TOP 100 '

DECLARE @SalesOrderID INT = ' + CAST(SalesOrderID AS VARCHAR) + ',

@SQLQuery AS NVARCHAR(1500)

DECLARE @ParameterDefinition AS NVARCHAR(100) = N''@SalesOrderID INT''

SET @SQLQuery = ''SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = @SalesOrderID''

EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @SalesOrderID

' + CHAR(10) + 'GO'

FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID

ORDER BY NEWID()

SELECT *

FROM sys.dm_exec_cached_plans ecp

CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) sql

WHERE sql.text LIKE '(@SalesOrderID INT)SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = @SalesOrderID%'

8) Exécutons le lot de requêtes préparées, toujours depuis la seconde session

9) Une fois l’exécution du lot terminée, on revient à la première session pour consulter le cache des plans d’exécution en modifiant la clause légèrement WHERE :

SELECT	*
FROM	sys.dm_exec_cached_plans ecp
CROSS	APPLY sys.dm_exec_sql_text(ecp.plan_handle) sql
WHERE	sql.text LIKE '(@SalesOrderID INT)SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = @SalesOrderID%'

SELECT *

FROM sys.dm_exec_cached_plans ecp

CROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) sql

WHERE sql.text LIKE '(@SalesOrderID INT)SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderID = @SalesOrderID%'

Observation : Pour cette 3ème démo, pas de sous-plan mais un plan unique lié à la requête préparée.

Conclusion

Pourquoi passer des valeurs littérales à une requête au fond ? La principale raison est une méconnaissance de l’impact sur le moteur SQL Server et des alternatives telles que l’utilisation de requêtes préparées. Voici comment procéder en C# :

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare(v=vs.110).aspx

En revanche, si c’est une solution éditeur qui génère majoritairement ce type d’appel, donc que vous n’avez pas la possibilité de changer ce comportement côté applicatif, vous devrez vous poser la question d’activer ou non le paramétrage en mode forcé sur la base de données concernée, autrement dit la conversion automatique par le moteur SQL Server des valeurs littérales par des paramètres, pour réduire drastiquement le nombre de compilations et donc la sollicitation du CPU mais aussi de la mémoire pour le stockage des plans. Le paramétrage est déjà activé en mode simple par défaut mais ne concerne que les plans triviaux. Limitations et recommandations pour le paramétrage en mode forcé :

https://technet.microsoft.com/fr-fr/library/ms175037(v=sql.105).aspx