Recommandations pour le paramétrage des requêtes
Certaines requêtes consomment davantage de ressources que d'autres. Par exemple, les requêtes qui renvoient des ensembles de résultats volumineux et celles qui contiennent des clauses WHERE non uniques sont toujours gourmandes en ressources. Aucun degré de l'intelligence de l'optimiseur de requête ne peut éliminer le coût en ressources de ces constructions par rapport à une requête moins complexe. SQL Server utilise le plan d'accès optimal, mais l'optimisation des requêtes est limitée par ce qui est possible.
Toutefois, pour améliorer les performances des requêtes, vous pouvez effectuer les opérations suivantes :
Ajouter de la mémoire. Cette solution peut s'avérer utile, surtout si le serveur exécute de nombreuses requêtes complexes et que plusieurs d'entre elles s'exécutent lentement.
Utiliser plusieurs processeurs. En effet, plusieurs processeurs permettent au Moteur de base de données d'exécuter des requêtes en parallèle. Pour plus d'informations, consultez Traitement de requêtes en parallèle.
Réécrire la requête. Considérez les points suivants :
Si la requête utilise un curseur, déterminez si la requête de curseur peut être écrite en faisant appel soit à un type de curseur plus efficace (comme un curseur rapide en avant uniquement), soit à une requête unique. Les requêtes uniques sont généralement plus performantes que les opérations de curseur. Comme un ensemble d'instructions de curseur consiste la plupart du temps en une opération de boucle externe où chaque ligne de la boucle externe est traitée une fois à l'aide d'une instruction interne, envisagez d'utiliser une instruction GROUP BY ou CASE, ou une sous-requête. Pour plus d'informations, consultez Types de curseurs (moteur de base de données) et Principes de base des requêtes.
Si une application comporte une boucle, pensez à placer cette boucle à l'intérieur de la requête. En effet, une application contient souvent une boucle comportant une requête paramétrée, exécutée de nombreuses fois, ce qui nécessite des allers‑retours sur le réseau entre l'ordinateur exécutant l'application et SQL Server. Il vaut mieux dans ce cas créer une requête unique, plus complexe, utilisant une table temporaire. Un seul aller-retour réseau est alors nécessaire et l'optimiseur de requêtes peut améliorer cette requête unique plus efficacement. Pour plus d'informations, consultez Procédures dans Transact-SQL et Variables Transact-SQL.
N'utilisez pas plusieurs alias pour une même table de la même requête afin de simuler une intersection d'index. Ceci n'est plus nécessaire car SQL Server prend en compte automatiquement l'intersection d'index et peut utiliser plusieurs index de la même table dans la même requête. Considérez l'exemple de requête suivant :
SELECT * FROM lineitem WHERE partkey BETWEEN 17000 AND 17100 AND shipdate BETWEEN '1/1/1994' AND '1/31/1994'SQL Server peut exploiter les index des deux colonnes partkey et shipdate, et effectuer ensuite une correspondance de hachage entre les deux sous-ensembles pour obtenir l'intersection d'index.
Utilisez le paramétrage de requête pour autoriser la réutilisation de plans d'exécution de requêtes mis en cache. Si un jeu de requêtes a les mêmes hachage de requête et hachage de plan de requête, vous pouvez améliorer les performances en créant une requête paramétrable. Le fait d'appeler une seule requête avec des paramètres au lieu de requêtes multiples avec des valeurs littérales autorise la réutilisation du plan d'exécution de requêtes mis en cache. Pour plus d'informations, consultez Recherche et paramétrage de requêtes semblables à l'aide de requête et de hachages de plan de requête et Mise en mémoire cache et réutilisation du plan d'exécution.
Si vous ne pouvez pas modifier l'application, vous pouvez utiliser des guides de plan de modèle avec paramétrage forcé pour obtenir un résultat semblable. Pour plus d'informations, consultez Indication du comportement du paramétrage de requêtes grâce aux repères de plan.
Ne faites appel aux options des requêtes que si c'est nécessaire. Les requêtes utilisant des options exécutées sur des versions antérieures de SQL Server doivent d'abord être testées sans ces options. Les indicateurs peuvent empêcher l'optimiseur de requêtes de choisir un meilleur plan d'exécution. Pour plus d'informations, consultez SELECT (Transact-SQL).
Utilisez query_plan_hash pour capturer, stocker et comparer les plans d'exécution de requêtes pour les requêtes sur la durée. Par exemple, après avoir modifié la configuration du système, vous pouvez comparer des valeurs de hachage de plan de requête pour les requêtes critiques à leurs valeurs de hachage du plan de requête d'origine. Les différences dans les valeurs de hachage de plan de requête peuvent vous indiquer si la modification de configuration du système s'est traduite par des plans d'exécution de requêtes mis à jour pour les requêtes importantes. Vous pouvez également décider d'arrêter l'exécution d'une requête longue actuelle si son hachage de plan de requête dans sys.dm_exec_requests diffère de son hachage du plan de requête de la ligne de base afin de bénificier de bonnes performances. Pour plus d'informations, consultez Recherche et paramétrage de requêtes semblables à l'aide de requête et de hachages de plan de requête.
Utilisez l'option de configuration query governor. L'option de configuration query governor peut servir à empêcher les requêtes d'exécution longue de consommer les ressources système. Par défaut, l'option autorise l'exécution de toutes les requêtes, quelle que soit la durée de leur exécution. Toutefois, il est possible de configurer l'administrateur de requêtes de manière à limiter le nombre maximal de secondes autorisé pour l'exécution de toutes les requêtes pour toutes les connexions, ou de seulement certaines requêtes pour une connexion donnée. Parce que l'administrateur des requêtes se base sur un coût estimé et non pas sur le temps réel écoulé, il n'intègre pas la notion de charge à l'exécution . Il bloque également les requêtes d'exécution longue avant leur démarrage au lieu de les faire tourner pendant le temps spécifié. Pour plus d'informations, consultez Option query governor cost limit et SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL).
Optimisez la réutilisation des plans de requêtes à partir du cache de plan. Le Moteur de base de données met en cache des plans de requêtes pour une réutilisation éventuelle. Un plan de requête non mis en cache n'est jamais réutilisable. Il doit être compilé chaque fois qu'il est exécuté, ce qui amoindrit les performances. Les options d'instruction SET Transact-SQL suivantes empêchent la réutilisation des plans de requêtes mis en cache. Un lot Transact-SQL dans lequel ces options SET sont activées (ON) ne peut pas partager ses plans de requêtes avec le même lot dans lequel ces options SET étaient désactivées (OFF) lors de sa compilation :
SET ANSI_NULL_DFLT_OFF
SET ANSI_NULL_DFLT_ON
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_WARNINGS
SET ARITHABORT
SET CONCAT_NULL_YIELDS_NULL
SET DATEFIRST
SET DATEFORMAT
SET FORCEPLAN
SET LANGUAGE
SET NO_BROWSETABLE
SET NUMERIC_ROUNDABORT
SET QUOTED_IDENTIFIER
SET TEXTSIZE
En outre, l'option SET ANSI_DEFAULTS affecte la réutilisation des plans de requêtes mis en cache car elle peut être utilisée pour modifier les options ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS et QUOTED_IDENTIFIER SET. La plupart des options SET modifiables avec l'option SET ANSI_DEFAULTS sont répertoriées comme options SET susceptibles d'affecter la réutilisation des plans de requêtes.
Vous pouvez modifier certaines de ces options SET au moyen des méthodes suivantes :
Utiliser la procédure stockée sp_configure pour des modifications à l'échelle du serveur. Pour plus d'informations, consultez sp_configure (Transact-SQL).
Utiliser la clause SET de l'instruction ALTER DATABASE. Pour plus d'informations, consultez ALTER DATABASE (Transact-SQL).
Changer OLE DB et les paramètres de connexion ODBC. Pour plus d'informations, consultez Configuration du réseau client.
[!REMARQUE]
Pour éviter les recompilations de plans de requêtes provoquées par les options SET, définissez celles-ci au moment de la connexion et assurez-vous qu'elles ne changent pas au cours de la connexion. Vous devez attribuer à certaines options SET des valeurs spécifiques pour utiliser des vues indexées ou des index sur des colonnes calculées. Pour plus d'informations, consultez Options SET affectant les résultats.