310
Chapitre 9. Optimisation des procédures stockées
SQL Server 2008 intègre une option de serveur nommée « optimize for ad hoc
workloads », qui vous permet de diminuer la consommation de mémoire en cache de plan,
lorsque votre serveur est principalement utilisé avec des requêtes dynamiques, non paramétrées. Pour l’activer, exécutez ce code :
EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure 'optimize for ad hoc workloads',1
RECONFIGURE
9.3.2 Paramétrage du SQL dynamique
Une facilité est offerte pour l’exécution paramétrée du SQL dynamique. Au lieu
d’utiliser la commande EXECUTE(), qui se contente d’évaluer et d’envoyer l’instruction SQL telle quelle, la procédure stockées système sp_executesql permet de
déclarer des paramètres dans la chaîne et de les envoyer à chaque appel, paramétrant
ainsi explicitement le plan d’exécution. Voici un exemple qui présente les deux
solutions :
DBCC FREEPROCCACHE
GO
DECLARE @sql varchar(8000)
SET @sql = 'SELECT * FROM Person.Contact WHERE LastName = ''Allen'''
EXECUTE (@sql)
SET @sql = 'SELECT * FROM Person.Contact WHERE LastName = ''Ackerman'''
EXECUTE (@sql)
GO
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT * FROM Person.Contact WHERE LastName = @LastName'
EXECUTE sp_executesql @sql, N'@LastName NVARCHAR(80)', @LastName = 'Allen'
EXECUTE sp_executesql @sql, N'@LastName NVARCHAR(80)', @LastName = 'Ackerman'
GO
SELECT
cp.usecounts,
cp.size_in_bytes,
st.text
FROM sys.dm_exec_cached_plans cp
OUTER APPLY sys.dm_exec_sql_text (cp.plan_handle) st
JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
GO
Bien sûr, autant que possible, préférez la syntaxe avec sp_executesql.
Il est à noter qu’une fonctionnalité similaire est proposée par les bibliothèques
d’accès client, qui permettent de « préparer » le code SQL à envoyer, pour favoriser
la réutilisation du plan d’exécution. OLEDB expose l’interface IcommandPrepare
pour ce faire. N’utilisez pas systématiquement cette fonctionnalité, elle est souvent