Utiliser l’option OPTION (RECOMPILE) pour une déclaration

J’ai commencé cette série avec le post intitulé : Les petites tables de Bobby, l’injection SQL et EXECUTE AS. Je suis ensuite passé à la discussion de certaines différences avec le post intitulé : EXEC et sp_executesql – en quoi sont-ils différents ?

Aujourd’hui, je veux aborder quelques commentaires ainsi que continuer avec quelques conseils et astuces utilisant ces commandes.

Pour commencer – aurions-nous pu aider les performances de l’instruction sp_executesql ?

Oui…

Si nous savons qu’une instruction renvoie une quantité variable de données (en fonction des paramètres fournis), nous pouvons utiliser la fonctionnalité WITH RECOMPILE de SQL Server 2005 pour indiquer à SQL Server que l’instruction en cours d’exécution doit avoir son propre plan créé et que les plans antérieurs (s’ils existent) ne doivent pas réutiliser l’instruction. Elle indique également à SQL Server que ce plan particulier est un « plan à usage unique » qui ne doit pas être réutilisé par les utilisateurs suivants. Pour voir la combinaison de toutes ces choses – je vais utiliser certaines des DMV qui suivent le cache et l’utilisation des plans.

DBCC FREEPROCCACHE
GO

SELECT st.text, qs.EXECUTION_COUNT -, qs.*, cp.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS cp
WHERE st.text like ‘%FROM dbo.member%’
GO

Pour l’instant, cette requête renvoie 0 ligne.

Je vais exécuter ce qui suit, puis revérifier le cache du plan :

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.member WHERE lastname LIKE @lastname’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Tripp’
GO

Maintenant, nous avons une ligne pour notre plan de requête paramétré :

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 1

Alors, qu’est-ce que ça nous montre… ça nous montre qu’il y a un plan en cache pour cette déclaration. Et, si nous sommes intéressés à voir le plan, nous pouvons supprimer le cp.* commenté dans la requête ci-dessus pour obtenir la colonne cp.query_plan. Cliquez sur un showplan XML et SSMS ira DIRECTEMENT dans une fenêtre de plan de requête graphique (à partir de 2008):

Et, une fois de plus, nous voyons le plan optimal (utiliser l’index et faire une recherche de signet) car cette requête est très sélective (seulement 1 ligne).

Nous allons exécuter à nouveau exactement la même instruction – en utilisant la valeur d’Anderson, juste pour avoir une configuration au point où nous étions la semaine dernière :

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.member WHERE lastname LIKE @lastname’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Anderson’
GO

Et, nous voyons qu’il utilise EXACTEMENT le même plan (en regardant showplan). En fait, nous pouvons le voir en vérifiant notre cache de plan également :

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2

Et, alors que nous savons que ce plan (pour utiliser l’index) est bon pour la valeur hautement sélective de ‘Tripp’, il n’est pas bon pour la valeur d’Anderson car il y a de nombreuses lignes qui correspondent. Si nous nous en doutions et/ou si nous le savions lors de l’exécution (depuis le client), alors nous pourrions utiliser l’OPTION (RECOMPILE) pour forcer le serveur SQL à obtenir un nouveau plan :

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = ‘SELECT * FROM dbo.member WHERE lastname LIKE @lastname OPTION (RECOMPILE)’
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)’, ‘Anderson’
go

Le plan de requête résultant de cette exécution est :

Et, c’est le plan le plus optimal compte tenu de ce paramètre. À ce stade, les questions (IMO) sont:

1. Est-ce que quelqu’un serait vraiment capable d’estimer par programmation qu’un paramètre soumis par le client est « atypique » et/ou qu’il justifie une recompilation ? Et, je suppose que je peux répondre oui – pour certains paramètres – comme ceux avec un joker. Mais, si nous parlons juste de deux valeurs différentes contre une seule colonne, ce serait deviner les statistiques des données.
2. Doit-on toujours exécuter cette déclaration particulière pour recompiler et ne jamais sauvegarder un plan ?
3. Que fait SQL Server avec le plan lui-même ?

Je vais d’abord répondre au #3 car celui-ci est facile à répondre. En utilisant la même instruction, je vais à nouveau interroger le cache du plan :

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2

Même si c’est la troisième fois que nous exécutons cette instruction, cette dernière exécution n’a PAS été mise en cache. Elle a été utilisée uniquement pour l’exécution avec OPTION (RECOMPILE). Et, cela n’affectera PAS les exécutions futures. Si je reviens en arrière et que j’exécute sans l’OPTION (RECOMPILE), alors j’obtiendrai le plan antérieur (pour utiliser l’index).

Maintenant, les deux autres questions – celles-ci sont beaucoup plus intéressantes et c’est là que je pense que les procédures stockées devraient être utilisées. Personnellement, je pense que les développeurs qui connaissent les données et qui connaissent l’application – seront beaucoup plus aptes à créer le BON code surtout lorsqu’ils comprennent toutes les options qui s’offrent à eux.

Voici comment je pense aux procédures stockées et à la recompilation :

• Si je sais qu’une instruction particulière renvoie toujours le même nombre de lignes et utilise le même plan (et, je le saurais en faisant des tests), alors je vais créer la procédure stockée normalement et laisser le plan être mis en cache.
• Si je sais qu’une instruction particulière varie sauvagement d’une exécution à l’autre et que le plan optimal varie (là encore, je devrais le savoir en testant plusieurs exécutions d’échantillons), alors je créerai la procédure stockée normalement et j’utiliserai OPTION (RECOMPILE) pour m’assurer que le plan de l’instruction n’est pas mis en cache ou enregistré avec la procédure stockée. À chaque exécution, cette procédure stockée obtiendra des paramètres différents et l’instruction particulièrement méchante obtiendra un nouveau plan à chaque exécution.

Cependant, c’est aussi là que les choses deviennent plus difficiles. J’ai souvent vu des procédures stockées où les gens essaient d’utiliser la logique conditionnelle pour briser les différents types de paramètres et cela ne fonctionne généralement pas aussi bien que prévu (je blogue ceci ensuite). Et c’est toujours là que certains décident de construire dynamiquement l’instruction qui sera exécutée – maintenant, nous devons déterminer si oui ou non nous devons utiliser sp_executesql ou EXEC. Et, il y a vraiment plusieurs options à ce stade. En fin de compte, dans un ou deux posts supplémentaires – je vous montrerai finalement où EXEC est un vainqueur clair sur sp_executesql parce que même l’OPTION (RECOMPILE) n’aide pas toujours TOUS les types de plans (et surtout l’un des types de plans les plus courants que je vois).