Guide d'architecture de traitement de requêtes – SQL Server – Bien choisir son serveur d impression

<! – ->

S'APPLIQUE À: Ouiserveur SQL nonBase de données SQL Azure nonAzure SQL Data Warehouse nonEntrepôt de données parallèle

Le moteur de base de données SQL Server traite les requêtes sur diverses architectures de stockage de données, telles que des tables locales, des tables partitionnées et des tables réparties sur plusieurs serveurs. Les rubriques suivantes décrivent comment SQL Server traite les requêtes et optimise leur réutilisation via la mise en cache du plan d'exécution.

Modes d'exécution

Le moteur de base de données SQL Server peut traiter les instructions Transact-SQL en utilisant deux modes de traitement distincts:

  • Exécution en mode ligne
  • Exécution en mode batch

Exécution en mode ligne

Exécution en mode ligne est une méthode de traitement des requêtes utilisée avec les tables RDMBS traditionnelles, dans lesquelles les données sont stockées au format de ligne. Lorsqu'une requête est exécutée et accède aux données des tables de stockage de lignes, les opérateurs de l'arborescence d'exécution et les opérateurs enfants lisent chaque ligne requise dans toutes les colonnes spécifiées dans le schéma de table. SQL Server extrait ensuite chaque colonne lue des colonnes requises pour le jeu de résultats, comme indiqué par une instruction SELECT, un prédicat JOIN ou un prédicat de filtre.

Remarque

L'exécution en mode ligne est très efficace pour les scénarios OLTP, mais peut être moins efficace lors de l'analyse de grandes quantités de données, par exemple dans des scénarios de stockage de données.

Exécution en mode batch

Exécution en mode batch est une méthode de traitement de requête utilisée pour traiter plusieurs lignes ensemble (d'où le terme batch). Chaque colonne d'un lot est stockée en tant que vecteur dans une zone de mémoire distincte, de sorte que le traitement en mode batch est basé sur un vecteur. Le traitement en mode batch utilise également des algorithmes optimisés pour les processeurs multicœurs et un débit de mémoire accru que l'on trouve sur les matériels modernes.

L'exécution en mode batch est étroitement intégrée et optimisée autour du format de stockage columnstore. Le traitement en mode batch fonctionne lorsque cela est possible sur des données compressées et élimine l'opérateur d'échange utilisé pour l'exécution en mode ligne. Le résultat est un meilleur parallélisme et des performances plus rapides.

Lorsqu'une requête est exécutée en mode batch et accède aux données contenues dans les index columnstore, les opérateurs de l'arborescence d'exécution et les opérateurs enfants lisent plusieurs lignes ensemble dans des segments de colonne. SQL Server ne lit que les colonnes requises pour le résultat, telles que référencées par une instruction SELECT, un prédicat JOIN ou un prédicat de filtre.
Pour plus d'informations sur les index columnstore, voir Architecture d'index Columnstore.

Remarque

L'exécution en mode batch est très efficace dans les scénarios de Data Warehousing, dans lesquels de grandes quantités de données sont lues et agrégées.

Traitement des instructions SQL

Le traitement d'une seule instruction Transact-SQL est la méthode la plus élémentaire permettant à SQL Server d'exécuter des instructions Transact-SQL. Les étapes utilisées pour traiter un seul SÉLECTIONNER L'instruction qui référence uniquement les tables de base locales (pas de vues ni de tables distantes) illustre le processus de base.

Priorité des opérateurs logiques

Lorsque plusieurs opérateurs logiques sont utilisés dans une instruction, NE PAS est évalué en premier, puis ET, et enfin OU. Les opérateurs arithmétiques et binaires sont gérés avant les opérateurs logiques. Pour plus d'informations, voir Priorité des opérateurs.

Dans l'exemple suivant, la condition de couleur concerne le modèle de produit 21 et non le modèle de produit 20, car ET a priorité sur OU.

SELECT ProductID, ProductModelID
DE LA PRODUCTION.Produit
WHERE ProductModelID = 20 OU ProductModelID = 21
  ET Couleur = 'Rouge';
ALLER

Vous pouvez modifier le sens de la requête en ajoutant des parenthèses pour forcer l’évaluation de la OU premier. La requête suivante recherche uniquement les produits rouges des modèles 20 et 21.

SELECT ProductID, ProductModelID
DE LA PRODUCTION.Produit
WHERE (ProductModelID = 20 OU ProductModelID = 21)
  ET Couleur = 'Rouge';
ALLER

L'utilisation de parenthèses, même lorsqu'elles ne sont pas obligatoires, peut améliorer la lisibilité des requêtes et réduire le risque d'erreur subtile en raison de la priorité des opérateurs. L'utilisation de parenthèses n'entraîne aucune dégradation significative des performances. L'exemple suivant est plus lisible que l'exemple d'origine, même si leur syntaxe est identique.

SELECT ProductID, ProductModelID
DE LA PRODUCTION.Produit
WHERE ProductModelID = 20 OR (ProductModelID = 21
  ET Couleur = 'Rouge');
ALLER

Optimisation des instructions SELECT

UNE SÉLECTIONNER la déclaration est non procédurale; il ne précise pas les étapes exactes que le serveur de base de données doit utiliser pour extraire les données demandées. Cela signifie que le serveur de base de données doit analyser l'instruction pour déterminer le moyen le plus efficace d'extraire les données demandées. Ceci est appelé optimisation de la SÉLECTIONNER déclaration. Le composant qui fait cela s'appelle l'optimiseur de requête. L'entrée dans Query Optimizer comprend la requête, le schéma de la base de données (définitions de table et d'index) et les statistiques de la base de données. Le résultat de l'optimiseur de requête est un plan d'exécution de requête, parfois appelé plan de requête ou simplement plan. Le contenu d'un plan de requête est décrit plus en détail plus loin dans cette rubrique.

Les entrées et les sorties de l’optimiseur de requêtes lors de l’optimisation d’un seul SÉLECTIONNER déclaration sont illustrés dans le diagramme suivant:

query_processor_io

UNE SÉLECTIONNER instruction définit uniquement les éléments suivants:

  • Le format du jeu de résultats. Ceci est spécifié principalement dans la liste de sélection. Cependant, d’autres clauses telles que COMMANDÉ PAR et PAR GROUPE également affecter la forme finale de l'ensemble de résultats.
  • Les tables qui contiennent les données source. Ceci est spécifié dans le DE clause.
  • Comment les tables sont liées logiquement aux fins de la SÉLECTIONNER déclaration. Ceci est défini dans les spécifications de jointure, qui peuvent apparaître dans clause ou dans un SUR clause suivante DE.
  • Les conditions que doivent remplir les lignes des tables source pour être éligible à la SÉLECTIONNER déclaration. Ceux-ci sont spécifiés dans le et AYANT clauses.

Un plan d'exécution de requête est une définition de ce qui suit:

  • La séquence d'accès aux tables source.
    En règle générale, le serveur de base de données peut accéder aux tables de base pour créer le jeu de résultats dans de nombreuses séquences. Par exemple, si le SÉLECTIONNER instruction référence trois tables, le serveur de base de données pourrait d'abord accéder TableA, utilisez les données de TableA extraire les lignes correspondantes de TableB, puis utilisez les données de TableB extraire des données de TableC. Les autres séquences dans lesquelles le serveur de base de données pourrait accéder aux tables sont:
    TableC, TableB, TableA, ou
    TableB, TableA, TableC, ou
    TableB, TableC, TableA, ou
    TableC, TableA, TableB

  • Les méthodes utilisées pour extraire les données de chaque table.
    En règle générale, il existe différentes méthodes pour accéder aux données de chaque table. Si seules quelques lignes avec des valeurs de clé spécifiques sont requises, le serveur de base de données peut utiliser un index. Si toutes les lignes de la table sont requises, le serveur de base de données peut ignorer les index et effectuer une analyse de la table. Si toutes les lignes d'une table sont obligatoires mais qu'il existe un index dont les colonnes de clé sont dans un COMMANDÉ PAR, effectuer une analyse d'index au lieu d'une analyse de table peut enregistrer une sorte distincte du jeu de résultats. Si une table est très petite, les analyses de table peuvent être la méthode la plus efficace pour la quasi-totalité des accès à la table.

Le processus de sélection d'un plan d'exécution parmi potentiellement plusieurs plans possibles est appelé optimisation. Query Optimizer est l’un des composants les plus importants d’un système de base de données SQL. Bien que l'optimiseur utilise une surcharge pour analyser la requête et sélectionner un plan, cette surcharge est généralement enregistrée plusieurs fois lorsque l'optimiseur de requête sélectionne un plan d'exécution efficace. Par exemple, deux entreprises de construction peuvent recevoir des plans identiques pour une maison. Si une entreprise met quelques jours au début pour planifier la construction de la maison et que l’autre commence à construire sans planification, celle qui prend le temps de planifier son projet finira probablement d’abord.

L'optimiseur de requêtes SQL Server est un optimiseur de requêtes basé sur les coûts. Chaque plan d'exécution possible a un coût associé en termes de quantité de ressources informatiques utilisées. Query Optimizer doit analyser les plans possibles et choisir celui qui présente le coût estimé le plus bas. Certains complexes SÉLECTIONNER les déclarations ont des milliers de plans d'exécution possibles. Dans ces cas, Query Optimizer n'analyse pas toutes les combinaisons possibles. Au lieu de cela, il utilise des algorithmes complexes pour trouver un plan d'exécution dont le coût est raisonnablement proche du coût minimum possible.

SQL Server Query Optimizer ne choisit pas uniquement le plan d'exécution avec le coût de ressource le plus bas; il choisit le plan qui renvoie les résultats à l'utilisateur avec un coût raisonnable en ressources et qui renvoie les résultats le plus rapidement. Par exemple, traiter une requête en parallèle utilise généralement plus de ressources que de la traiter en série, mais termine la requête plus rapidement. SQL Server Query Optimizer utilisera un plan d'exécution parallèle pour renvoyer les résultats si la charge sur le serveur ne sera pas affectée.

SQL Server Query Optimizer s'appuie sur des statistiques de distribution pour estimer les coûts en ressources de différentes méthodes d'extraction d'informations d'une table ou d'un index. Les statistiques de distribution sont conservées pour les colonnes et les index et contiennent des informations sur la densité.1 des données sous-jacentes. Ceci est utilisé pour indiquer la sélectivité des valeurs dans un index ou une colonne particulière. Par exemple, dans un tableau représentant des voitures, de nombreuses voitures ont le même fabricant, mais chaque voiture a un numéro d'identification du véhicule (VIN) unique. Un indice sur le VIN est plus sélectif qu'un indice sur le fabricant, car sa densité est inférieure à celle du fabricant. Si les statistiques d'index ne sont pas actuelles, Query Optimizer peut ne pas faire le meilleur choix pour l'état actuel de la table. Pour plus d'informations sur les densités, voir Statistiques.

1 La densité définit la distribution des valeurs uniques présentes dans les données ou le nombre moyen de valeurs en double pour une colonne donnée. À mesure que la densité diminue, la sélectivité d'une valeur augmente.

L'optimiseur de requêtes SQL Server est important car il permet au serveur de base de données de s'adapter de manière dynamique aux conditions changeantes de la base de données sans nécessiter l'intervention d'un programmeur ou d'un administrateur de base de données. Cela permet aux programmeurs de se concentrer sur la description du résultat final de la requête. Ils peuvent avoir la certitude que SQL Server Query Optimizer générera un plan d'exécution efficace pour l'état de la base de données à chaque exécution de l'instruction.

Traitement d'une instruction SELECT

Les étapes de base utilisées par SQL Server pour traiter une seule instruction SELECT sont les suivantes:

  1. L'analyseur analyse le SÉLECTIONNER déclaration et la divise en unités logiques telles que mots-clés, expressions, opérateurs et identificateurs.
  2. Une arborescence de requête, parfois appelée arborescence de séquence, est construite et décrit les étapes logiques nécessaires pour transformer les données source au format requis par le jeu de résultats.
  3. Query Optimizer analyse différentes manières d'accéder aux tables source. Il sélectionne ensuite la série d'étapes qui renvoie les résultats plus rapidement tout en utilisant moins de ressources. L'arborescence de la requête est mise à jour pour enregistrer cette série exacte d'étapes. La version finale optimisée de l'arborescence de la requête s'appelle le plan d'exécution.
  4. Le moteur relationnel commence à exécuter le plan d'exécution. Lorsque les étapes nécessitant des données des tables de base sont traitées, le moteur relationnel demande au moteur de stockage de transmettre les données des ensembles de lignes demandés au moteur relationnel.
  5. Le moteur relationnel traite les données renvoyées par le moteur de stockage dans le format défini pour le jeu de résultats et renvoie le jeu de résultats au client.

Évaluation constante des plis et des expressions

SQL Server évalue certaines expressions constantes à un stade précoce pour améliorer les performances des requêtes. Ceci est appelé pliage constant. Une constante est un littéral Transact-SQL, tel que 3, 'ABC', '2005-12-31', 1.0e3 ou 0x12345678.

Expressions pliables

SQL Server utilise un repliement constant avec les types d'expressions suivants:

  • Expressions arithmétiques, telles que 1 + 1, 5/3 * 2, ne contenant que des constantes.
  • Expressions logiques, telles que 1 = 1 et 1> 2 ET 3> 4, qui ne contiennent que des constantes.
  • Fonctions intégrées considérées comme pliables par SQL Server, notamment JETER et CONVERTIR. En règle générale, une fonction intrinsèque est pliable si elle dépend uniquement de ses entrées et non d'autres informations contextuelles, telles que les options SET, les paramètres de langue, les options de base de données et les clés de cryptage. Les fonctions non déterministes ne sont pas pliables. Les fonctions intégrées déterministes sont pliables, à quelques exceptions près.

Remarque

Une exception est faite pour les types d'objets volumineux. Si le type de sortie du processus de pliage est un type d'objet volumineux (texte, image, nvarchar (max), varchar (max) ou varbinary (max)), SQL Server ne plie pas l'expression.

Expressions non pliables

Tous les autres types d'expression ne sont pas pliables. En particulier, les types d’expressions suivants ne sont pas pliables:

  • Expressions non constantes telles qu'une expression dont le résultat dépend de la valeur d'une colonne.
  • Expressions dont les résultats dépendent d'une variable ou d'un paramètre local, tel que @x.
  • Fonctions non déterministes.
  • Fonctions définies par l'utilisateur (Transact-SQL et CLR).
  • Expressions dont les résultats dépendent des paramètres de langue.
  • Expressions dont les résultats dépendent des options SET.
  • Expressions dont les résultats dépendent des options de configuration du serveur.

Exemples d'expressions constantes pliables et non pliables

Considérons la requête suivante:

SELECT *
FROM Sales.SalesOrderHeader AS s
INNER JOIN Sales.SalesOrderDetail AS d
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue> 117,00 + 1000,00;

Si la PARAMETERISATION L'option de base de données n'est pas définie sur FORCÉ pour cette requête, alors l'expression 117.00 + 1000.00 est évalué et remplacé par son résultat, 1117.00, avant que la requête ne soit compilée. Les avantages de ce pliage constant sont les suivants:

  • L'expression ne doit pas nécessairement être évaluée à plusieurs reprises au moment de l'exécution.
  • L'optimiseur de requêtes utilise la valeur de l'expression après son évaluation pour estimer la taille de l'ensemble de résultats de la partie de la requête. TotalDue> 117,00 + 1000,00.

D'autre part, si dbo.f est une fonction scalaire définie par l'utilisateur, l'expression dbo.f (100) n'est pas plié, car SQL Server ne plie pas les expressions impliquant des fonctions définies par l'utilisateur, même si elles sont déterministes. Pour plus d'informations sur le paramétrage, voir Paramétrage forcé plus loin dans cet article.

Évaluation de l'expression

De plus, certaines expressions qui ne sont pas pliées de manière constante mais dont les arguments sont connus au moment de la compilation, qu’il s’agisse de paramètres ou de constantes, sont évaluées par l’estimateur de taille du jeu de résultats (cardinalité) faisant partie de l’optimiseur lors de l’optimisation.

Plus précisément, les fonctions intégrées et les opérateurs spéciaux suivants sont évalués lors de la compilation si toutes leurs entrées sont connues: PLUS HAUT, INFÉRIEUR, RTRIM, DATEPART (AA seulement), AVOIR UN RENDEZ-VOUS, JETER, et CONVERTIR. Les opérateurs suivants sont également évalués lors de la compilation si toutes leurs entrées sont connues:

  • Opérateurs arithmétiques: +, -, *, /, unary –
  • Opérateurs logiques: ET, OU, NE PAS
  • Opérateurs de comparaison: <, >, <=, >=, <>, COMME, EST NULL, EST NON NULLE

Aucune autre fonction ou opérateur n'est évalué par Query Optimizer pendant l'estimation de cardinalité.

Exemples d'évaluation d'expressions au moment de la compilation

Considérez cette procédure stockée:

USE AdventureWorks2014;
ALLER
CREATE PROCEDURE MyProc (@d date / heure)
COMME
CHOISISSEZ LE COMPTE (*)
DE Sales.SalesOrderHeader
WHERE OrderDate> @ d + 1;

Lors de l'optimisation de la SÉLECTIONNER Query Optimizer tente d’évaluer la cardinalité attendue du jeu de résultats pour la condition. OrderDate> @ d + 1. L'expression @ d + 1 n'est pas constamment plié, car @ré est un paramètre. Cependant, au moment de l'optimisation, la valeur du paramètre est connue. Cela permet à l'optimiseur de requêtes d'estimer avec précision la taille de l'ensemble de résultats, ce qui l'aide à sélectionner un bon plan de requête.

Considérons maintenant un exemple similaire au précédent, sauf qu’une variable locale @ d2 remplace @ d + 1 dans la requête et l'expression est évaluée dans une instruction SET et non dans la requête.

USE AdventureWorks2014;
ALLER
CREATE PROCEDURE MyProc2 (@d date / heure)
COMME
COMMENCER
DECLARE @ d2 datetime
SET @ d2 = @ d + 1
CHOISISSEZ LE COMPTE (*)
DE Sales.SalesOrderHeader
WHERE OrderDate> @ d2
FIN;

Quand le SÉLECTIONNER déclaration dans MyProc2 est optimisé dans SQL Server, la valeur de @ d2 n'est pas connu. Par conséquent, l’optimiseur de requêtes utilise une estimation par défaut pour la sélectivité de OrderDate> @ d2, (dans ce cas 30 pour cent).

Traitement d'autres déclarations

Les étapes de base décrites pour traiter une SÉLECTIONNER instruction s’applique à d’autres instructions Transact-SQL telles que INSÉRER, METTRE À JOUR, et EFFACER. METTRE À JOUR et EFFACER Les deux instructions doivent cibler l'ensemble des lignes à modifier ou à supprimer. Le processus d'identification de ces lignes est identique à celui utilisé pour identifier les lignes source qui contribuent à l'ensemble de résultats d'un SÉLECTIONNER déclaration. le METTRE À JOUR et INSÉRER les déclarations peuvent toutes deux contenir des SÉLECTIONNER les instructions qui fournissent les valeurs de données à mettre à jour ou à insérer.

Même les instructions DDL (Data Data Language), telles que CREER PROCEDURE ou ALTER TABLE, sont finalement résolus en une série d’opérations relationnelles sur les tables du catalogue système et parfois (comme ALTER TABLE AJOUTER UNE COLONNE) par rapport aux tables de données.

Tables de travail

Le moteur relationnel peut avoir besoin de créer une table de travail pour effectuer une opération logique spécifiée dans une instruction Transact-SQL. Les tables de travail sont des tables internes utilisées pour conserver les résultats intermédiaires. Des tables de travail sont générées pour certains PAR GROUPE, COMMANDÉ PAR, ou SYNDICAT requêtes. Par exemple, si un COMMANDÉ PAR Si la clause fait référence à des colonnes qui ne sont couvertes par aucun index, le moteur relationnel peut avoir besoin de générer une table de travail pour trier le jeu de résultats dans l'ordre demandé. Les tables de travail sont également parfois utilisées en tant que spools qui conservent temporairement le résultat de l'exécution d'une partie d'un plan de requête. Les tables de travail sont intégrées à tempdb et sont automatiquement supprimées lorsqu'elles ne sont plus nécessaires.

Voir la résolution

Le processeur de requêtes SQL Server traite les vues indexées et non indexées différemment:

  • Les lignes d'une vue indexée sont stockées dans la base de données dans le même format qu'une table. Si l'optimiseur de requête décide d'utiliser une vue indexée dans un plan de requête, la vue indexée est traitée de la même manière qu'une table de base.
  • Seule la définition d'une vue non indexée est stockée, pas les lignes de la vue. L'optimiseur de requêtes incorpore la logique de la définition de vue dans le plan d'exécution qu'il construit pour l'instruction Transact-SQL qui fait référence à la vue non indexée.

La logique utilisée par SQL Server Query Optimizer pour décider du moment où utiliser une vue indexée est similaire à celle utilisée pour décider du moment où utiliser un index sur une table. Si les données de la vue indexée couvrent tout ou partie de l'instruction Transact-SQL et si l'optimiseur de requête détermine qu'un index de la vue est le chemin d'accès à faible coût, il choisit l'index, que la vue soit ou non référencé par nom dans la requête.

Lorsqu'une instruction Transact-SQL fait référence à une vue non indexée, l'analyseur et l'optimiseur de requêtes analysent la source de l'instruction Transact-SQL et de la vue, puis les résolvent en un seul plan d'exécution. Il n'existe pas de plan pour l'instruction Transact-SQL et un plan séparé pour la vue.

Par exemple, considérons la vue suivante:

USE AdventureWorks2014;
ALLER
CREATE VIEW EmployeeName AS
SELECT h.BusinessEntityID, p.LastName, p.PremierNom
DE HumanResources.Employee AS h
REJOINDRE Person.Person AS p
ON h.BusinessEntityID = p.BusinessEntityID;
ALLER

Sur la base de cette vue, ces deux instructions Transact-SQL effectuent les mêmes opérations sur les tables de base et produisent les mêmes résultats:

/ * SELECT référençant la vue EmployeeName. * /
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
FROM AdventureWorks2014.Sales.SalesOrderHeader AS soh
REJOINDRE AdventureWorks2014.dbo.EmployeeName AS EmpN
ON (soh.SalesPersonID = EmpN.BusinessEntityID)
WHERE OrderDate> '20020531';

/ * SELECT référençant directement les tables Personne et Employé. * /
SELECT LastName AS EmployeeLastName, SalesOrderID, OrderDate
DE AdventureWorks2014.HumanResources.Employee AS e
REJOINDRE AdventureWorks2014.Sales.SalesOrderHeader AS soh
ON soh.SalesPersonID = e.BusinessEntityID
REJOINDRE AdventureWorks2014.Person.Person AS p
SUR e.BusinessEntityID = p.BusinessEntityID
WHERE OrderDate> '20020531';

La fonctionnalité Showplan de SQL Server Management Studio indique que le moteur relationnel génère le même plan d’exécution pour ces deux applications. SÉLECTIONNER déclarations.

Utilisation d'indices avec des vues

Les indicateurs placés dans les vues d'une requête peuvent entrer en conflit avec d'autres indicateurs découverts lorsque la vue est développée pour accéder à ses tables de base. Lorsque cela se produit, la requête renvoie une erreur. Par exemple, considérons la vue suivante contenant un indicateur de table dans sa définition:

USE AdventureWorks2014;
ALLER
CREATE VIEW Person.AddrState AVEC SCHEMABINDING AS
SELECT a.AddressID, a.AddressLine1,
    le code d'état de province, le code de région de pays
FROM Person.Address a WITH (NOLOCK), Person.StateProvince s
WHERE a.StateProvinceID = s.StateProvinceID;

Supposons maintenant que vous entrez cette requête:

SELECT AddressID, AddressLine1, StateProvinceCode, CountryRegionCode
FROM Person.AddrState AVEC (SERIALIZABLE)
WHERE StateProvinceCode = 'WA';

La requête échoue, car l'indice Sérialisable qui est appliqué à la vue Person.AddrState dans la requête est propagé aux deux tables Personne.Adresse et Personne.EtatProvince dans la vue quand il est développé. Cependant, élargir la vue révèle aussi la AUCUN VERROU allusion à Personne.Adresse. Parce que le Sérialisable et AUCUN VERROU allusion conflit, la requête résultante est incorrecte.

le PAGLOCK, AUCUN VERROU, DAME DE NAGE, TABLOCK, ou TABLOCKX les indices de table sont en conflit, de même que les HOLDLOCK, AUCUN VERROU, READCOMMITTED, REPEATABLEREAD, Sérialisable conseils de table.

Les astuces peuvent se propager à travers les niveaux de vues imbriquées. Par exemple, supposons qu'une requête applique le HOLDLOCK allusion à une vue v1. Quand v1 est élargi, nous trouvons cette vue v2 fait partie de sa définition. v2La définition de comprend un AUCUN VERROU allusion sur l'une de ses tables de base. Mais cette table hérite aussi de la HOLDLOCK indice de la requête affichée v1. Parce que le AUCUN VERROU et HOLDLOCK indique un conflit, la requête échoue.

Quand le Commande de force Un indice est utilisé dans une requête contenant une vue, l'ordre de jointure des tables dans la vue est déterminé par la position de la vue dans la construction ordonnée. Par exemple, la requête suivante sélectionne parmi trois tables et une vue:

SELECT * FROM Table1, Table2, View1, Table3
WHERE Table1.Col1 = Table2.Col1
    AND Table2.Col1 = View1.Col1
    AND View1.Col2 = Table3.Col2;
OPTION (ORDRE DE FORCE);

Et Vue1 est défini comme suit:

CREATE VIEW View1 AS
SELECT Colx, Coly DE TableA, TableB
WHERE TableA.ColZ = TableB.Colz;

L'ordre de jointure dans le plan de requête est Tableau 1, Tableau 2, TableA, TableB, Tableau 3.

Résolution des index sur les vues

Comme pour tout index, SQL Server choisit d'utiliser une vue indexée dans son plan de requête uniquement si l'optimiseur de requête détermine qu'il est avantageux de le faire.

Les vues indexées peuvent être créées dans n’importe quelle édition de SQL Server. Dans certaines éditions de certaines versions de SQL Server, Query Optimizer considère automatiquement la vue indexée. Dans certaines éditions de certaines versions de SQL Server, pour utiliser une vue indexée, la NOEXPAND indice de table doit être utilisé. Pour plus de précisions, voir la documentation de chaque version.

SQL Server Query Optimizer utilise une vue indexée lorsque les conditions suivantes sont remplies:

  • Ces options de session sont définies sur SUR:
    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • ARITHABORT
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER
    • le NUMERIC_ROUNDABORT L'option de session est réglée sur OFF.
  • L'optimiseur de requêtes recherche une correspondance entre les colonnes d'index de vue et les éléments de la requête, par exemple:
    • La condition de recherche prédise dans la clause WHERE
    • Rejoindre des opérations
    • Fonctions d'agrégat
    • PAR GROUPE clauses
    • Références de table
  • Le coût estimé de l'utilisation de l'index est le coût le plus bas de tous les mécanismes d'accès considérés par Query Optimizer.
  • Toutes les tables référencées dans la requête (soit directement, soit en développant une vue pour accéder à ses tables sous-jacentes) qui correspond à une référence de table dans la vue indexée doivent avoir le même ensemble de conseils appliqués dans la requête.

Remarque

le READCOMMITTED et READCOMMITTEDLOCK Les astuces sont toujours considérées comme des astuces différentes dans ce contexte, quel que soit le niveau d'isolation actuel de la transaction.

Autre que les exigences pour la ENSEMBLE options et indicateurs de table, il s’agit des mêmes règles que Query Optimizer utilise pour déterminer si un index de table couvre une requête. Rien d'autre ne doit être spécifié dans la requête pour qu'une vue indexée soit utilisée.

Une requête ne doit pas explicitement référencer une vue indexée dans la DE clause permettant à l’optimiseur de requêtes d’utiliser la vue indexée. Si la requête contient des références aux colonnes des tables de base qui sont également présentes dans la vue indexée et que l'optimiseur de requêtes estime que l'utilisation de la vue indexée fournit le mécanisme d'accès au coût le plus bas, il optimise la vue indexée de la même manière. choisit les index de table de base lorsqu'ils ne sont pas directement référencés dans une requête. L'optimiseur de requêtes peut choisir la vue lorsqu'il contient des colonnes qui ne sont pas référencées par la requête, à condition que la vue offre l'option la moins coûteuse pour couvrir une ou plusieurs des colonnes spécifiées dans la requête.

L’optimiseur de requêtes traite une vue indexée référencée dans la DE clause en tant que vue standard. L'optimiseur de requêtes étend la définition de la vue dans la requête au début du processus d'optimisation. Ensuite, la correspondance de vue indexée est effectuée. La vue indexée peut être utilisée dans le plan d'exécution final sélectionné par l'optimiseur de requêtes ou à la place, le plan peut matérialiser les données nécessaires à partir de la vue en accédant aux tables de base référencées par la vue. L'optimiseur de requêtes choisit la solution la moins coûteuse.

Utilisation d'indices avec des vues indexées

Vous pouvez empêcher l’utilisation d’index de vue dans une requête à l’aide de la commande Agrandir les vues indice de requête, ou vous pouvez utiliser le NOEXPAND indice de table pour forcer l’utilisation d’un index pour une vue indexée spécifiée dans DE clause d'une requête. Cependant, vous devez laisser Query Optimizer déterminer dynamiquement les meilleures méthodes d'accès à utiliser pour chaque requête. Limitez votre utilisation de DÉVELOPPER et NOEXPAND à des cas spécifiques où les tests ont montré qu’ils améliorent considérablement les performances.

le Agrandir les vues option spécifie que l'optimiseur de requête n'utilise aucun index de vue pour la requête entière.

Quand NOEXPAND Query Optimizer envisage d’utiliser les index définis sur la vue. NOEXPAND spécifié avec l'option INDICE() La clause oblige l'optimiseur de requête à utiliser les index spécifiés. NOEXPAND peut être spécifié uniquement pour une vue indexée et ne peut pas être spécifié pour une vue non indexée.

Quand ni NOEXPAND ni Agrandir les vues est spécifié dans une requête contenant une vue, celle-ci est développée pour accéder aux tables sous-jacentes. Si la requête qui constitue la vue contient des indicateurs de table, ceux-ci sont propagés aux tables sous-jacentes. (Ce processus est expliqué plus en détail dans Résolution de la vue.) Tant que l'ensemble des astuces existant sur les tables sous-jacentes de la vue sont identiques, la requête peut être mise en correspondance avec une vue indexée. La plupart du temps, ces astuces se correspondent car elles sont directement héritées de la vue. Toutefois, si la requête fait référence à des tables au lieu de vues et que les indicateurs appliqués directement à ces tables ne sont pas identiques, une telle requête ne peut pas être mise en correspondance avec une vue indexée. Si la INDICE, PAGLOCK, DAME DE NAGE, TABLOCKX, UPDLOCK, ou XLOCK Les astuces s'appliquent aux tables référencées dans la requête après le développement de la vue. La requête n'est pas éligible pour la correspondance de vue indexée.

Si un indice de table sous la forme de INDEX (index_val[ ,...n] ) référence une vue dans une requête et vous ne spécifiez pas également la NOEXPAND indice, l'indicateur d'index est ignoré. Pour spécifier l’utilisation d’un index particulier, utilisez NOEXPAND.

En règle générale, lorsque l'optimiseur de requête fait correspondre une vue indexée à une requête, les indicateurs spécifiés dans les tables ou les vues de la requête sont appliqués directement à la vue indexée. Si Query Optimizer choisit de ne pas utiliser une vue indexée, les indicateurs sont propagés directement aux tables référencées dans la vue. Pour plus d'informations, voir Afficher la résolution. Cette propagation ne s'applique pas aux indications de jointure. Ils sont appliqués uniquement dans leur position d'origine dans la requête. Query Optimizer ne prend pas en compte les astuces de jointure lors de la mise en correspondance de requêtes avec des vues indexées. Si un plan de requête utilise une vue indexée qui correspond à une partie d'une requête contenant un indicateur de jointure, celui-ci n'est pas utilisé dans le plan.

Les astuces ne sont pas autorisées dans les définitions des vues indexées. En mode de compatibilité 80 et supérieur, SQL Server ignore les repères dans les définitions de vue indexée lors de leur maintenance ou lors de l'exécution de requêtes utilisant des vues indexées. Bien que l'utilisation d'indications dans les définitions de vue indexée ne génère pas d'erreur de syntaxe en mode de compatibilité 80, elles sont ignorées.

Résolution des vues partitionnées distribuées

Le processeur de requêtes SQL Server optimise les performances des vues partitionnées distribuées. L'aspect le plus important des performances de la vue partitionnée distribuée est de minimiser la quantité de données transférées entre les serveurs membres.

SQL Server crée des plans intelligents et dynamiques qui exploitent efficacement les requêtes distribuées pour accéder aux données des tables de membres distants:

  • Le processeur de requêtes utilise d'abord OLE DB pour extraire les définitions de contrainte de vérification de chaque table de membres. Cela permet au processeur de requêtes de mapper la distribution des valeurs de clé sur les tables de membres.
  • Le processeur de requêtes compare les plages de clés spécifiées dans une instruction Transact-SQL clause de la carte qui montre comment les lignes sont réparties dans les tables de membres. Le processeur de requêtes crée ensuite un plan d'exécution de la requête qui utilise des requêtes distribuées pour extraire uniquement les lignes distantes nécessaires à l'exécution de l'instruction Transact-SQL. Le plan d'exécution est également conçu de manière à ce que tout accès aux tables de membres distants, qu'il s'agisse de données ou de métadonnées, est retardé jusqu'à ce que les informations soient requises.

Par exemple, considérons un système sur lequel une table clients est partitionnée sur Server1 (N ° de client de 1 à 3299999), Server2 (N ° de client de 3300000 à 6599999) et Server3 (N ° de client de 6600000 à 9999999).

Considérez le plan d'exécution créé pour cette requête exécutée sur Server1:

SELECT *
FROM CompanyData.dbo.Customers
LORSQUE le numéro de client est compris entre 3200000 et 3400000;

Le plan d’exécution de cette requête extrait les lignes avec N ° de client valeurs de clé comprises entre 3200000 et 3299999 à partir de la table des membres locaux et émet une requête distribuée pour extraire les lignes contenant des valeurs de clé comprises entre 3300000 et 3400000 à partir de Server2.

Le processeur de requêtes SQL Server peut également créer une logique dynamique dans les plans d'exécution de requêtes pour les instructions Transact-SQL dans lesquelles les valeurs de clé ne sont pas connues lorsque le plan doit être généré. Par exemple, considérons cette procédure stockée:

CREATE PROCEDURE GetCustomer @CustomerIDParameter INT
COMME
SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID = @CustomerIDParameter;

SQL Server ne peut prédire quelle valeur de clé sera fournie par le @CustomerIDParameter paramètre à chaque exécution de la procédure. Comme la valeur de la clé ne peut pas être prédite, le processeur de requêtes ne peut pas non plus prédire à quelle table de membres il faudra accéder. Pour gérer ce cas, SQL Server crée un plan d'exécution doté d'une logique conditionnelle, appelée filtres dynamiques, permettant de contrôler la table de membres à laquelle accéder, en fonction de la valeur du paramètre d'entrée. En supposant que GetCustomer Si la procédure stockée a été exécutée sur Server1, la logique du plan d’exécution peut être représentée comme suit:

IF @CustomerIDParameter ENTRE 1 et 3299999
   Récupérer une ligne de la table locale CustomerData.dbo.Customer_33
ELSE IF @CustomerIDParameter ENTRE 3300000 et 6599999
   Récupérer une ligne de la table liée Server2.CustomerData.dbo.Customer_66
ELSE IF @CustomerIDParameter ENTRE 6600000 et 9999999
   Récupérer une ligne de la table liée Server3.CustomerData.dbo.Customer_99

SQL Server construit parfois ces types de plans d'exécution dynamiques même pour des requêtes non paramétrées. L'optimiseur de requêtes peut paramétrer une requête afin que le plan d'exécution puisse être réutilisé. Si Query Optimizer paramètre une requête référençant une vue partitionnée, Query Optimizer ne peut plus présumer que les lignes requises proviendront d'une table de base spécifiée. It will then have to use dynamic filters in the execution plan.

Stored Procedure and Trigger Execution

SQL Server stores only the source for stored procedures and triggers. When a stored procedure or trigger is first executed, the source is compiled into an execution plan. If the stored procedure or trigger is again executed before the execution plan is aged from memory, the relational engine detects the existing plan and reuses it. If the plan has aged out of memory, a new plan is built. This process is similar to the process SQL Server follows for all Transact-SQL statements. The main performance advantage that stored procedures and triggers have in SQL Server compared with batches of dynamic Transact-SQL is that their Transact-SQL statements are always the same. Therefore, the relational engine easily matches them with any existing execution plans. Stored procedure and trigger plans are easily reused.

The execution plan for stored procedures and triggers is executed separately from the execution plan for the batch calling the stored procedure or firing the trigger. This allows for greater reuse of the stored procedure and trigger execution plans.

Execution Plan Caching and Reuse

SQL Server has a pool of memory that is used to store both execution plans and data buffers. The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. The part of the memory pool that is used to store execution plans is referred to as the plan cache.

SQL Server execution plans have the following main components:

  • Query Execution Plan
    The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. This is referred to as the query plan. No user context is stored in the query plan. There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. The parallel copy covers all parallel executions, regardless of their degree of parallelism.
  • Execution Context
    Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. This data structure is referred to as the execution context. The execution context data structures are reused. If a user executes a query and one of the structures is not being used, it is reinitialized with the context for the new user.

execution_context

When any Transact-SQL statement is executed in SQL Server, the Relational Engine first looks through the plan cache to verify that an existing execution plan for the same Transact-SQL statement exists. The Transact-SQL statement qualifies as existing if it literally matches a previously executed Transact-SQL statement with a cached plan, character per character. SQL Server reuses any existing plan it finds, saving the overhead of recompiling the Transact-SQL statement. If no existing execution plan exists, SQL Server generates a new execution plan for the query.

Remarque

Some Transact-SQL statements are not cached, such as bulk operation statements running on rowstore or statements containing string literals larger than 8 KB in size.

SQL Server has an efficient algorithm to find any existing execution plans for any specific Transact-SQL statement. In most systems, the minimal resources that are used by this scan are less than the resources that are saved by being able to reuse existing plans instead of compiling every Transact-SQL statement.

The algorithms to match new Transact-SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, assume that La personne is the default schema for the user executing the below SÉLECTIONNER statements. While in this example it is not required that the La personne table is fully qualified to execute, it means that the second statement is not matched with an existing plan, but the third is matched:

SELECT * FROM Person;
ALLER
SELECT * FROM Person.Person;
ALLER
SELECT * FROM Person.Person;
ALLER

Removing Execution Plans from the Plan Cache

Execution plans remain in the plan cache as long as there is enough memory to store them. When memory pressure exists, the SQL Server Database Engine uses a cost-based approach to determine which execution plans to remove from the plan cache. To make a cost-based decision, the SQL Server Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.

When a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost; for ad-hoc execution plans, the user process sets the current cost to zero. Thereafter, each time a user process references an execution plan, it resets the current cost to the original compile cost; for ad-hoc execution plans the user process increases the current cost. For all plans, the maximum value for the current cost is the original compile cost.

When memory pressure exists, the SQL Server Database Engine responds by removing execution plans from the plan cache. To determine which plans to remove, the SQL Server Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the SQL Server Database Engine examines the plan and the current cost is zero. When examining an execution plan, the SQL Server Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan.

The SQL Server Database Engine repeatedly examines the execution plans until enough have been removed to satisfy memory requirements. While memory pressure exists, an execution plan may have its cost increased and decreased more than once. When memory pressure no longer exists, the SQL Server Database Engine stops decreasing the current cost of unused execution plans and all execution plans remain in the plan cache, even if their cost is zero.

The SQL Server Database Engine uses the resource monitor and user worker threads to free memory from the plan cache in response to memory pressure. The resource monitor and user worker threads can examine plans run concurrently to decrease the current cost for each unused execution plan. The resource monitor removes execution plans from the plan cache when global memory pressure exists. It frees memory to enforce policies for system memory, process memory, resource pool memory, and maximum size for all caches.

The maximum size for all caches is a function of the buffer pool size and cannot exceed the maximum server memory. For more information on configuring the maximum server memory, see the max server memory mise en sp_configure.

The user worker threads remove execution plans from the plan cache when single cache memory pressure exists. They enforce policies for maximum single cache size and maximum single cache entries.

The following examples illustrate which execution plans get removed from the plan cache:

  • An execution plan is frequently referenced so that its cost never goes to zero. The plan remains in the plan cache and is not removed unless there is memory pressure and the current cost is zero.
  • An ad-hoc execution plan is inserted and is not referenced again before memory pressure exists. Since ad-hoc plans are initialized with a current cost of zero, when the SQL Server Database Engine examines the execution plan, it will see the zero current cost and remove the plan from the plan cache. The ad-hoc execution plan remains in the plan cache with a zero current cost when memory pressure does not exist.

To manually remove a single plan or all plans from the cache, use DBCC FREEPROCCACHE. Starting with SQL Server 2016 (13.x), the ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE to clear the procedure (plan) cache for the database in scope.

Recompiling Execution Plans

Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. A new plan must then be recompiled for the next connection that executes the query. The conditions that invalidate a plan include the following:

  • Changes made to a table or view referenced by the query (ALTER TABLE et ALTER VIEW).
  • Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  • Changes to any indexes used by the execution plan.
  • Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  • Dropping an index used by the execution plan.
  • An explicit call to sp_recompile.
  • Large numbers of changes to keys (generated by INSÉRER ou EFFACER statements from other users that modify a table referenced by the query).
  • For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  • Executing a stored procedure using the WITH RECOMPILE option.

Most recompilations are required either for statement correctness or to obtain potentially faster query execution plans.

In SQL Server 2000, whenever a statement within a batch causes recompilation, the whole batch, whether submitted through a stored procedure, trigger, ad-hoc batch, or prepared statement, is recompiled. Starting with SQL Server 2005 (9.x), only the statement inside the batch that causes recompilation is recompiled. Because of this difference, recompilation counts in SQL Server 2000 and later releases are not comparable. Also, there are more types of recompilations in SQL Server 2005 (9.x) and later because of its expanded feature set.

Statement-level recompilation benefits performance because, in most cases, a small number of statements causes recompilations and their associated penalties, in terms of CPU time and locks. These penalties are therefore avoided for the other statements in the batch that do not have to be recompiled.

le sql_statement_recompile extended event (xEvent) reports statement-level recompilations. This xEvent occurs when a statement-level recompilation is required by any kind of batch. This includes stored procedures, triggers, ad hoc batches and queries. Batches may be submitted through several interfaces, including sp_executesql, dynamic SQL, Prepare methods or Execute methods.
le recompile_cause colonne de sql_statement_recompile xEvent contains an integer code that indicates the reason for the recompilation. The following table contains the possible reasons:

Schema changed Statistics changed
Deferred compile SET option changed
Temporary table changed Remote rowset changed
FOR BROWSE permission changed Query notification environment changed
Partitioned view changed Cursor options changed
OPTION (RECOMPILE) demandé Parameterized plan flushed
Plan affecting database version changed Query Store plan forcing policy changed
Query Store plan forcing failed Query Store missing the plan

Remarque

In SQL Server versions where xEvents are not available, then the SQL Server Profiler SP:Recompile trace event can be used for the same purpose of reporting statement-level recompilations.
The trace event SQL:StmtRecompile also reports statement-level recompilations, and this trace event can also be used to track and debug recompilations.
Whereas SP:Recompile generates only for stored procedures and triggers, SQL:StmtRecompile generates for stored procedures, triggers, ad-hoc batches, batches that are executed by using sp_executesql, prepared queries, and dynamic SQL.
le EventSubClass colonne de SP:Recompile et SQL:StmtRecompile contains an integer code that indicates the reason for the recompilation. The codes are described here.

Remarque

Quand le AUTO_UPDATE_STATISTICS database option is set to SUR, queries are recompiled when they target tables or indexed views whose statistics have been updated or whose cardinalities have changed significantly since the last execution.
This behavior applies to standard user-defined tables, temporary tables, and the inserted and deleted tables created by DML triggers. If query performance is affected by excessive recompilations, consider changing this setting to DE. Quand le AUTO_UPDATE_STATISTICS database option is set to DE, no recompilations occur based on statistics or cardinality changes, with the exception of the inserted and deleted tables that are created by DML INSTEAD OF triggers. Because these tables are created in tempdb, the recompilation of queries that access them depends on the setting of AUTO_UPDATE_STATISTICS in tempdb.
Note that in SQL Server 2000, queries continue to recompile based on cardinality changes to the DML trigger inserted and deleted tables, even when this setting is DE.

Parameters and Execution Plan Reuse

The use of parameters, including parameter markers in ADO, OLE DB, and ODBC applications, can increase the reuse of execution plans.

Attention

Using parameters or parameter markers to hold values that are typed by end users is more secure than concatenating the values into a string that is then executed by using either a data access API method, the EXÉCUTER statement, or the sp_executesql stored procedure.

The only difference between the following two SÉLECTIONNER statements is the values that are compared in the clause:

SELECT *
FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;
SELECT *
FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 4;

The only difference between the execution plans for these queries is the value stored for the comparison against the ProductSubcategoryID colonne. While the goal is for SQL Server to always recognize that the statements generate essentially the same plan and reuse the plans, SQL Server sometimes does not detect this in complex Transact-SQL statements.

Separating constants from the Transact-SQL statement by using parameters helps the relational engine recognize duplicate plans. You can use parameters in the following ways:

  • In Transact-SQL , use sp_executesql:

    DECLARE @MyIntParm INT
    SET @MyIntParm = 1
    EXEC sp_executesql
       N'SELECT * 
       FROM AdventureWorks2014.Production.Product 
       WHERE ProductSubcategoryID = @Parm',
       N'@Parm INT',
       @MyIntParm
    

    This method is recommended for Transact-SQL scripts, stored procedures, or triggers that generate SQL statements dynamically.

  • ADO, OLE DB, and ODBC use parameter markers. Parameter markers are question marks (?) that replace a constant in an SQL statement and are bound to a program variable. For example, you would do the following in an ODBC application:

    • Utilisation SQLBindParameter to bind an integer variable to the first parameter marker in an SQL statement.
    • Put the integer value in the variable.
    • Execute the statement, specifying the parameter marker (?):
    SQLExecDirect(hstmt, 
       "SELECT * 
       FROM AdventureWorks2014.Production.Product 
       WHERE ProductSubcategoryID = ?",
       SQL_NTS);
    

    The SQL Server Native Client OLE DB Provider and the SQL Server Native Client ODBC driver included with SQL Server use sp_executesql to send statements to SQL Server when parameter markers are used in applications.

  • To design stored procedures, which use parameters by design.

If you do not explicitly build parameters into the design of your applications, you can also rely on the SQL Server Query Optimizer to automatically parameterize certain queries by using the default behavior of simple parameterization. Alternatively, you can force the Query Optimizer to consider parameterizing all queries in the database by setting the PARAMETERIZATION option of the ALTER DATABASE statement to FORCÉ.

When forced parameterization is enabled, simple parameterization can still occur. For example, the following query cannot be parameterized according to the rules of forced parameterization:

SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;

However, it can be parameterized according to simple parameterization rules. When forced parameterization is tried but fails, simple parameterization is still subsequently tried.

Simple Parameterization

In SQL Server, using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new Transact-SQL statements with existing, previously-compiled execution plans.

Attention

Using parameters or parameter markers to hold values typed by end users is more secure than concatenating the values into a string that is then executed using either a data access API method, the EXÉCUTER statement, or the sp_executesql stored procedure.

If a Transact-SQL statement is executed without parameters, SQL Server parameterizes the statement internally to increase the possibility of matching it against an existing execution plan. This process is called simple parameterization. In SQL Server 2000, the process was referred to as auto-parameterization.

Consider this statement:

SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;

The value 1 at the end of the statement can be specified as a parameter. The relational engine builds the execution plan for this batch as if a parameter had been specified in place of the value 1. Because of this simple parameterization, SQL Server recognizes that the following two statements generate essentially the same execution plan and reuses the first plan for the second statement:

SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 1;
SELECT * FROM AdventureWorks2014.Production.Product 
WHERE ProductSubcategoryID = 4;

When processing complex Transact-SQL statements, the relational engine may have difficulty determining which expressions can be parameterized. To increase the ability of the relational engine to match complex Transact-SQL statements to existing, unused execution plans, explicitly specify the parameters using either sp_executesql or parameter markers.

Remarque

When the +, -, *, /, or % arithmetic operators are used to perform implicit or explicit conversion of int, smallint, tinyint, or bigint constant values to the float, real, decimal or numeric data types, SQL Server applies specific rules to calculate the type and precision of the expression results. However, these rules differ, depending on whether the query is parameterized or not. Therefore, similar expressions in queries can, in some cases, produce differing results.

Under the default behavior of simple parameterization, SQL Server parameterizes a relatively small class of queries. However, you can specify that all queries in a database be parameterized, subject to certain limitations, by setting the PARAMETERIZATION option of the ALTER DATABASE command to FORCÉ. Doing so may improve the performance of databases that experience high volumes of concurrent queries by reducing the frequency of query compilations.

Alternatively, you can specify that a single query, and any others that are syntactically equivalent but differ only in their parameter values, be parameterized.

Forced Parameterization

You can override the default simple parameterization behavior of SQL Server by specifying that all SÉLECTIONNER, INSÉRER, METTRE À JOUR, et EFFACER statements in a database be parameterized, subject to certain limitations. Forced parameterization is enabled by setting the PARAMETERIZATION option de FORCÉ dans le ALTER DATABASE statement. Forced parameterization may improve the performance of certain databases by reducing the frequency of query compilations and recompilations. Databases that may benefit from forced parameterization are generally those that experience high volumes of concurrent queries from sources such as point-of-sale applications.

Quand le PARAMETERIZATION option is set to FORCÉ, any literal value that appears in a SÉLECTIONNER, INSÉRER, METTRE À JOUR, ou EFFACER statement, submitted in any form, is converted to a parameter during query compilation. The exceptions are literals that appear in the following query constructs:

  • INSERT...EXECUTE statements.
  • Statements inside the bodies of stored procedures, triggers, or user-defined functions. SQL Server already reuses query plans for these routines.
  • Prepared statements that have already been parameterized on the client-side application.
  • Statements that contain XQuery method calls, where the method appears in a context where its arguments would typically be parameterized, such as a clause. If the method appears in a context where its arguments would not be parameterized, the rest of the statement is parameterized.
  • Statements inside a Transact-SQL cursor. (SÉLECTIONNER statements inside API cursors are parameterized.)
  • Deprecated query constructs.
  • Any statement that is run in the context of ANSI_PADDING ou ANSI_NULLS mis à DE.
  • Statements that contain more than 2,097 literals that are eligible for parameterization.
  • Statements that reference variables, such as WHERE T.col2 >= @bb.
  • Statements that contain the RECOMPILE query hint.
  • Statements that contain a COMPUTE clause.
  • Statements that contain a WHERE CURRENT OF clause.

Additionally, the following query clauses are not parameterized. Note that in these cases, only the clauses are not parameterized. Other clauses within the same query may be eligible for forced parameterization.

  • le de toute SÉLECTIONNER statement. Ceci comprend SÉLECTIONNER lists of subqueries and SÉLECTIONNER lists inside INSÉRER statements.
  • Subquery SÉLECTIONNER statements that appear inside an SI statement.
  • le TOP, TABLESAMPLE, AYANT, GROUP BY, COMMANDÉ PAR, OUTPUT...INTO, ou FOR XML clauses of a query.
  • Arguments, either direct or as subexpressions, to OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML, or any FULLTEXT operator.
  • The pattern and escape_character arguments of a COMME clause.
  • The style argument of a CONVERT clause.
  • Integer constants inside an IDENTITY clause.
  • Constants specified by using ODBC extension syntax.
  • Constant-foldable expressions that are arguments of the +, -, *, /, and % operators. When considering eligibility for forced parameterization, SQL Server considers an expression to be constant-foldable when either of the following conditions is true:
    • No columns, variables, or subqueries appear in the expression.
    • The expression contains a CASE clause.
  • Arguments to query hint clauses. These include the number_of_rows argument of the VITE query hint, the number_of_processors argument of the MAXDOP query hint, and the number argument of the MAXRECURSION query hint.

Parameterization occurs at the level of individual Transact-SQL statements. In other words, individual statements in a batch are parameterized. After compiling, a parameterized query is executed in the context of the batch in which it was originally submitted. If an execution plan for a query is cached, you can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects dynamic management view. If a query is parameterized, the names and data types of parameters come before the text of the submitted batch in this column, such as (@1 tinyint).

Remarque

Parameter names are arbitrary. Users or applications should not rely on a particular naming order. Also, the following can change between versions of SQL Server and Service Pack upgrades: Parameter names, the choice of literals that are parameterized, and the spacing in the parameterized text.

Data Types of Parameters

When SQL Server parameterizes literals, the parameters are converted to the following data types:

  • Integer literals whose size would otherwise fit within the int data type parameterize to int. Larger integer literals that are parts of predicates that involve any comparison operator (includes <, <=, =, !=, >, >=, , !<, !>, <>, TOUT, TOUT, SOME, ENTRE, et DANS) parameterize to numeric(38,0). Larger literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision is just large enough to support its size and whose scale is 0.
  • Fixed-point numeric literals that are parts of predicates that involve comparison operators parameterize to numeric whose precision is 38 and whose scale is just large enough to support its size. Fixed-point numeric literals that are not parts of predicates that involve comparison operators parameterize to numeric whose precision and scale are just large enough to support its size.
  • Floating point numeric literals parameterize to float(53).
  • Non-Unicode string literals parameterize to varchar(8000) if the literal fits within 8,000 characters, and to varchar(max) if it is larger than 8,000 characters.
  • Unicode string literals parameterize to nvarchar(4000) if the literal fits within 4,000 Unicode characters, and to nvarchar(max) if the literal is larger than 4,000 characters.
  • Binary literals parameterize to varbinary(8000) if the literal fits within 8,000 bytes. If it is larger than 8,000 bytes, it is converted to varbinary(max).
  • Money type literals parameterize to money.

Guidelines for Using Forced Parameterization

Consider the following when you set the PARAMETERIZATION option to FORCED:

  • Forced parameterization, in effect, changes the literal constants in a query to parameters when compiling a query. Therefore, the Query Optimizer might choose suboptimal plans for queries. In particular, the Query Optimizer is less likely to match the query to an indexed view or an index on a computed column. It may also choose suboptimal plans for queries posed on partitioned tables and distributed partitioned views. Forced parameterization should not be used for environments that rely heavily on indexed views and indexes on computed columns. Generally, the PARAMETERIZATION FORCED option should only be used by experienced database administrators after determining that doing this does not adversely affect performance.
  • Distributed queries that reference more than one database are eligible for forced parameterization as long as the PARAMETERIZATION option is set to FORCÉ in the database whose context the query is running.
  • Setting the PARAMETERIZATION option de FORCÉ flushes all query plans from the plan cache of a database, except those that currently are compiling, recompiling, or running. Plans for queries that are compiling or running during the setting change are parameterized the next time the query is executed.
  • Setting the PARAMETERIZATION option is an online operation that it requires no database-level exclusive locks.
  • The current setting of the PARAMETERIZATION option is preserved when reattaching or restoring a database.

You can override the behavior of forced parameterization by specifying that simple parameterization be attempted on a single query, and any others that are syntactically equivalent but differ only in their parameter values. Conversely, you can specify that forced parameterization be attempted on only a set of syntactically equivalent queries, even if forced parameterization is disabled in the database. Plan guides are used for this purpose.

Remarque

Quand le PARAMETERIZATION option is set to FORCÉ, the reporting of error messages may differ from when the PARAMETERIZATION option is set to SIMPLE: multiple error messages may be reported under forced parameterization, where fewer messages would be reported under simple parameterization, and the line numbers in which errors occur may be reported incorrectly.

Preparing SQL Statements

The SQL Server relational engine introduces full support for preparing Transact-SQL statements before they are executed. If an application has to execute an Transact-SQL statement several times, it can use the database API to do the following:

  • Prepare the statement once. This compiles the Transact-SQL statement into an execution plan.
  • Execute the precompiled execution plan every time it has to execute the statement. This prevents having to recompile the Transact-SQL statement on each execution after the first time.
    Preparing and executing statements is controlled by API functions and methods. It is not part of the Transact-SQL language. The prepare/execute model of executing Transact-SQL statements is supported by the SQL Server Native Client OLE DB Provider and the SQL Server Native Client ODBC driver. On a prepare request, either the provider or the driver sends the statement to SQL Server with a request to prepare the statement. SQL Server compiles an execution plan and returns a handle for that plan to the provider or driver. On an execute request, either the provider or the driver sends the server a request to execute the plan that is associated with the handle.

Prepared statements cannot be used to create temporary objects on SQL Server. Prepared statements cannot reference system stored procedures that create temporary objects, such as temporary tables. These procedures must be executed directly.

Excess use of the prepare/execute model can degrade performance. If a statement is executed only once, a direct execution requires only one network round-trip to the server. Preparing and executing an Transact-SQL statement executed only one time requires an extra network round-trip; one trip to prepare the statement and one trip to execute it.

Preparing a statement is more effective if parameter markers are used. For example, assume that an application is occasionally asked to retrieve product information from the AdventureWorks sample database. There are two ways the application can do this.

Using the first way, the application can execute a separate query for each product requested:

SELECT * FROM AdventureWorks2014.Production.Product
WHERE ProductID = 63;

Using the second way, the application does the following:

  1. Prepares a statement that contains a parameter marker (?):
    SELECT * FROM AdventureWorks2014.Production.Product  
    WHERE ProductID = ?;
    
  2. Binds a program variable to the parameter marker.
  3. Each time product information is needed, fills the bound variable with the key value and executes the statement.

The second way is more efficient when the statement is executed more than three times.

In SQL Server, the prepare/execute model has no significant performance advantage over direct execution, because of the way SQL Server reuses execution plans. SQL Server has efficient algorithms for matching current Transact-SQL statements with execution plans that are generated for prior executions of the same Transact-SQL statement. If an application executes a Transact-SQL statement with parameter markers multiple times, SQL Server will reuse the execution plan from the first execution for the second and subsequent executions (unless the plan ages from the plan cache). The prepare/execute model still has these benefits:

  • Finding an execution plan by an identifying handle is more efficient than the algorithms used to match an Transact-SQL statement to existing execution plans.
  • The application can control when the execution plan is created and when it is reused.
  • The prepare/execute model is portable to other databases, including earlier versions of SQL Server.

Parameter Sniffing

"Parameter sniffing" refers to a process whereby SQL Server "sniffs" the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer so that they can be used to generate potentially more efficient query execution plans.

Parameter values are sniffed during compilation or recompilation for the following types of batches:

  • Stored procedures
  • Queries submitted via sp_executesql
  • Prepared queries

For more information on troubleshooting bad parameter sniffing issues, see Troubleshoot queries with parameter-sensitive query execution plan issues.

Remarque

For queries using the RECOMPILE hint, both parameter values and current values of local variables are sniffed. The values sniffed (of parameters and local variables) are those that exist at the place in the batch just before the statement with the RECOMPILE hint. In particular, for parameters, the values that came along with the batch invocation call are not sniffed.

Parallel Query Processing

SQL Server provides parallel queries to optimize query execution and index operations for computers that have more than one microprocessor (CPU). Because SQL Server can perform a query or index operation in parallel by using several operating system worker threads, the operation can be completed quickly and efficiently.

During query optimization, SQL Server looks for queries or index operations that might benefit from parallel execution. For these queries, SQL Server inserts exchange operators into the query execution plan to prepare the query for parallel execution. An exchange operator is an operator in a query execution plan that provides process management, data redistribution, and flow control. The exchange operator includes the Distribute Streams, Repartition Streams, et Gather Streams logical operators as subtypes, one or more of which can appear in the Showplan output of a query plan for a parallel query.

Important

Certain constructs inhibit SQL Server's ability to leverage parallelism on the entire execution plan, or parts or the execution plan.

Constructs that inhibit parallelism include:

After exchange operators are inserted, the result is a parallel-query execution plan. A parallel-query execution plan can use more than one worker thread. A serial execution plan, used by a nonparallel query, uses only one worker thread for its execution. The actual number of worker threads used by a parallel query is determined at query plan execution initialization and is determined by the complexity of the plan and the degree of parallelism. Degree of parallelism determines the maximum number of CPUs that are being used; it does not mean the number of worker threads that are being used. The degree of parallelism value is set at the server level and can be modified by using the sp_configure system stored procedure. You can override this value for individual query or index statements by specifying the MAXDOP query hint or MAXDOP index option.

The SQL Server Query Optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:

  • The serial execution cost of the query is not high enough to consider an alternative, parallel execution plan.
  • A serial execution plan is considered faster than any possible parallel execution plan for the particular query.
  • The query contains scalar or relational operators that cannot be run in parallel. Certain operators can cause a section of the query plan to run in serial mode, or the whole plan to run in serial mode.

Degree of Parallelism

SQL Server automatically detects the best degree of parallelism for each instance of a parallel query execution or index data definition language (DDL) operation. It does this based on the following criteria:

  1. Whether SQL Server is running on a computer that has more than one microprocessor or CPU, such as a symmetric multiprocessing computer (SMP).
    Only computers that have more than one CPU can use parallel queries.

  2. Whether sufficient worker threads are available.
    Each query or index operation requires a certain number of worker threads to execute. Executing a parallel plan requires more worker threads than a serial plan, and the number of required worker threads increases with the degree of parallelism. When the worker thread requirement of the parallel plan for a specific degree of parallelism cannot be satisfied, the SQL Server Database Engine decreases the degree of parallelism automatically or completely abandons the parallel plan in the specified workload context. It then executes the serial plan (one worker thread).

  3. The type of query or index operation executed.
    Index operations that create or rebuild an index, or drop a clustered index and queries that use CPU cycles heavily are the best candidates for a parallel plan. For example, joins of large tables, large aggregations, and sorting of large result sets are good candidates. Simple queries, frequently found in transaction processing applications, find the additional coordination required to execute a query in parallel outweigh the potential performance boost. To distinguish between queries that benefit from parallelism and those that do not benefit, the SQL Server Database Engine compares the estimated cost of executing the query or index operation with the cost threshold for parallelism value. Users can change the default value of 5 using sp_configure if proper testing found that a different value is better suited for the running workload.

  4. Whether there are a sufficient number of rows to process.
    If the Query Optimizer determines that the number of rows is too low, it does not introduce exchange operators to distribute the rows. Consequently, the operators are executed serially. Executing the operators in a serial plan avoids scenarios when the startup, distribution, and coordination costs exceed the gains achieved by parallel operator execution.

  5. Whether current distribution statistics are available.
    If the highest degree of parallelism is not possible, lower degrees are considered before the parallel plan is abandoned.
    For example, when you create a clustered index on a view, distribution statistics cannot be evaluated, because the clustered index does not yet exist. In this case, the SQL Server Database Engine cannot provide the highest degree of parallelism for the index operation. However, some operators, such as sorting and scanning, can still benefit from parallel execution.

Remarque

Parallel index operations are only available in SQL Server Enterprise, Developer, and Evaluation editions.

At execution time, the SQL Server Database Engine determines whether the current system workload and configuration information previously described allow for parallel execution. If parallel execution is warranted, the SQL Server Database Engine determines the optimal number of worker threads and spreads the execution of the parallel plan across those worker threads. When a query or index operation starts executing on multiple worker threads for parallel execution, the same number of worker threads is used until the operation is completed. The SQL Server Database Engine re-examines the optimal number of worker thread decisions every time an execution plan is retrieved from the plan cache. For example, one execution of a query can result in the use of a serial plan, a later execution of the same query can result in a parallel plan using three worker threads, and a third execution can result in a parallel plan using four worker threads.

In a parallel query execution plan, the insert, update, and delete operators are executed serially. However, the WHERE clause of an UPDATE or a DELETE statement, or the SELECT part of an INSERT statement may be executed in parallel. The actual data changes are then serially applied to the database.

Static and keyset-driven cursors can be populated by parallel execution plans. However, the behavior of dynamic cursors can be provided only by serial execution. The Query Optimizer always generates a serial execution plan for a query that is part of a dynamic cursor.

Overriding Degrees of Parallelism

You can use the max degree of parallelism (MAXDOP) server configuration option (ALTER DATABASE SCOPED CONFIGURATION on SQL Database ) to limit the number of processors to use in parallel plan execution. The max degree of parallelism option can be overridden for individual query and index operation statements by specifying the MAXDOP query hint or MAXDOP index option. MAXDOP provides more control over individual queries and index operations. For example, you can use the MAXDOP option to control, by increasing or reducing, the number of processors dedicated to an online index operation. In this way, you can balance the resources used by an index operation with those of the concurrent users.

Setting the max degree of parallelism option to 0 (default) enables SQL Server to use all available processors up to a maximum of 64 processors in a parallel plan execution. Although SQL Server sets a runtime target of 64 logical processors when MAXDOP option is set to 0, a different value can be manually set if needed. Setting MAXDOP to 0 for queries and indexes allows SQL Server to use all available processors up to a maximum of 64 processors for the given queries or indexes in a parallel plan execution. MAXDOP is not an enforced value for all parallel queries, but rather a tentative target for all queries eligible for parallelism. This means that if not enough worker threads are available at runtime, a query may execute with a lower degree of parallelism than the MAXDOP server configuration option.

Refer to this Microsoft Support Article for best practices on configuring MAXDOP.

Parallel Query Example

The following query counts the number of orders placed in a specific quarter, starting on April 1, 2000, and in which at least one line item of the order was received by the customer later than the committed date. This query lists the count of such orders grouped by each order priority and sorted in ascending priority order.

This example uses theoretical table and column names.

SELECT o_orderpriority, COUNT(*) AS Order_Count
FROM orders
WHERE o_orderdate >= '2000/04/01'
   AND o_orderdate < DATEADD (mm, 3, '2000/04/01')
   AND EXISTS
         (
          SELECT *
            FROM    lineitem
            WHERE l_orderkey = o_orderkey
               AND l_commitdate < l_receiptdate
         )
   GROUP BY o_orderpriority
   ORDER BY o_orderpriority

Assume the following indexes are defined on the lineitem et ordres tables:

CREATE INDEX l_order_dates_idx 
   ON lineitem
      (l_orderkey, l_receiptdate, l_commitdate, l_shipdate)

CREATE UNIQUE INDEX o_datkeyopr_idx
   ON ORDERS
      (o_orderdate, o_orderkey, o_custkey, o_orderpriority)

Here is one possible parallel plan generated for the query previously shown:

|--Stream Aggregate(GROUP BY:([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=COUNT(*)))
    |--Parallelism(Gather Streams, ORDER BY:
                  ([ORDERS].[o_orderpriority] ASC))
         |--Stream Aggregate(GROUP BY:
                  ([ORDERS].[o_orderpriority])
                  DEFINE:([Expr1005]=Count(*)))
              |--Sort(ORDER BY:([ORDERS].[o_orderpriority] ASC))
                   |--Merge Join(Left Semi Join, MERGE:
                  ([ORDERS].[o_orderkey])=
                        ([LINEITEM].[l_orderkey]),
                  RESIDUAL:([ORDERS].[o_orderkey]=
                        [LINEITEM].[l_orderkey]))
                        |--Sort(ORDER BY:([ORDERS].[o_orderkey] ASC))
                        | |--Parallelism(Repartition Streams,
                           PARTITION COLUMNS:
                           ([ORDERS].[o_orderkey]))
                        | |--Index Seek(OBJECT:
                     ([tpcd1G].[dbo].[ORDERS].[O_DATKEYOPR_IDX]),
                     SEEK:([ORDERS].[o_orderdate] >=
                           Apr  1 2000 12:00AM AND
                           [ORDERS].[o_orderdate] <
                           Jul  1 2000 12:00AM) ORDERED)
                        |--Parallelism(Repartition Streams,
                     PARTITION COLUMNS:
                     ([LINEITEM].[l_orderkey]),
                     ORDER BY:([LINEITEM].[l_orderkey] ASC))
                             |--Filter(WHERE:
                           ([LINEITEM].[l_commitdate]<
                           [LINEITEM].[l_receiptdate]))
                                  |--Index Scan(OBJECT:
         ([tpcd1G].[dbo].[LINEITEM].[L_ORDER_DATES_IDX]), ORDERED)

The illustration below shows a query plan executed with a degree of parallelism equal to 4 and involving a two-table join.

parallel_plan

The parallel plan contains three parallelism operators. Both the Index Seek operator of the o_datkey_ptr index and the Index Scan operator of the l_order_dates_idx index are performed in parallel. This produces several exclusive streams. This can be determined from the nearest Parallelism operators above the Index Scan and Index Seek operators, respectively. Both are repartitioning the type of exchange. That is, they are just reshuffling data among the streams and producing the same number of streams on their output as they have on their input. This number of streams is equal to the degree of parallelism.

The parallelism operator above the l_order_dates_idx Index Scan operator is repartitioning its input streams using the value of L_ORDERKEY as a key. In this way, the same values of L_ORDERKEY end up in the same output stream. At the same time, output streams maintain the order on the L_ORDERKEY column to meet the input requirement of the Merge Join operator.

The parallelism operator above the Index Seek operator is repartitioning its input streams using the value of O_ORDERKEY. Because its input is not sorted on the O_ORDERKEY column values and this is the join column in the Merge Join operator, the Sort operator between the parallelism and Merge Join operators make sure that the input is sorted for the Merge Join operator on the join columns. le Sort operator, like the Merge Join operator, is performed in parallel.

The topmost parallelism operator gathers results from several streams into a single stream. Partial aggregations performed by the Stream Aggregate operator below the parallelism operator are then accumulated into a single SUM value for each different value of the O_ORDERPRIORITY in the Stream Aggregate operator above the parallelism operator. Because this plan has two exchange segments, with degree of parallelism equal to 4, it uses eight worker threads.

For more information on the operators used in this example, refer to the Showplan Logical and Physical Operators Reference.

Parallel Index Operations

The query plans built for the index operations that create or rebuild an index, or drop a clustered index, allow for parallel, multi-worker threaded operations on computers that have multiple microprocessors.

Remarque

Parallel index operations are only available in Enterprise Edition, starting with SQL Server 2008.

SQL Server uses the same algorithms to determine the degree of parallelism (the total number of separate worker threads to run) for index operations as it does for other queries. The maximum degree of parallelism for an index operation is subject to the max degree of parallelism server configuration option. You can override the max degree of parallelism value for individual index operations by setting the MAXDOP index option in the CREATE INDEX, ALTER INDEX, DROP INDEX, and ALTER TABLE statements.

When the SQL Server Database Engine builds an index execution plan, the number of parallel operations is set to the lowest value from among the following:

  • The number of microprocessors, or CPUs in the computer.
  • The number specified in the max degree of parallelism server configuration option.
  • The number of CPUs not already over a threshold of work performed for SQL Server worker threads.

For example, on a computer that has eight CPUs, but where max degree of parallelism is set to 6, no more than six parallel worker threads are generated for an index operation. If five of the CPUs in the computer exceed the threshold of SQL Server work when an index execution plan is built, the execution plan specifies only three parallel worker threads.

The main phases of a parallel index operation include the following:

  • A coordinating worker thread quickly and randomly scans the table to estimate the distribution of the index keys. The coordinating worker thread establishes the key boundaries that will create a number of key ranges equal to the degree of parallel operations, where each key range is estimated to cover similar numbers of rows. For example, if there are four million rows in the table and the degree of parallelism is 4, the coordinating worker thread will determine the key values that delimit four sets of rows with 1 million rows in each set. If enough key ranges cannot be established to use all CPUs, the degree of parallelism is reduced accordingly.
  • The coordinating worker thread dispatches a number of worker threads equal to the degree of parallel operations and waits for these worker threads to complete their work. Each worker thread scans the base table using a filter that retrieves only rows with key values within the range assigned to the worker thread. Each worker thread builds an index structure for the rows in its key range. In the case of a partitioned index, each worker thread builds a specified number of partitions. Partitions are not shared among worker threads.
  • After all the parallel worker threads have completed, the coordinating worker thread connects the index subunits into a single index. This phase applies only to offline index operations.

Individuel CREATE TABLE ou ALTER TABLE statements can have multiple constraints that require that an index be created. These multiple index creation operations are performed in series, although each individual index creation operation may be a parallel operation on a computer that has multiple CPUs.

Distributed Query Architecture

Microsoft SQL Server supports two methods for referencing heterogeneous OLE DB data sources in Transact-SQL statements:

  • Linked server names
    The system stored procedures sp_addlinkedserver et sp_addlinkedsrvlogin are used to give a server name to an OLE DB data source. Objects in these linked servers can be referenced in Transact-SQL statements using four-part names. For example, if a linked server name of DeptSQLSrvr is defined against another instance of SQL Server, the following statement references a table on that server:

    SELECT JobTitle, HireDate 
    FROM DeptSQLSrvr.AdventureWorks2014.HumanResources.Employee;
    

    The linked server name can also be specified in an OPENQUERY statement to open a rowset from the OLE DB data source. This rowset can then be referenced like a table in Transact-SQL statements.

  • Ad hoc connector names
    For infrequent references to a data source, the OPENROWSET ou OPENDATASOURCE functions are specified with the information needed to connect to the linked server. The rowset can then be referenced the same way a table is referenced in Transact-SQL statements:

    SELECT *
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
          'c:MSOfficeAccessSamplesNorthwind.mdb';'Admin';'';
          Employees);
    

SQL Server uses OLE DB to communicate between the relational engine and the storage engine. The relational engine breaks down each Transact-SQL statement into a series of operations on simple OLE DB rowsets opened by the storage engine from the base tables. This means the relational engine can also open simple OLE DB rowsets on any OLE DB data source.
oledb_storage
The relational engine uses the OLE DB application programming interface (API) to open the rowsets on linked servers, fetch the rows, and manage transactions.

For each OLE DB data source accessed as a linked server, an OLE DB provider must be present on the server running SQL Server. The set of Transact-SQL operations that can be used against a specific OLE DB data source depends on the capabilities of the OLE DB provider.

For each instance of SQL Server, members of the sysadmin fixed server role can enable or disable the use of ad-hoc connector names for an OLE DB provider using the SQL Server DisallowAdhocAccess propriété. When ad-hoc access is enabled, any user logged on to that instance can execute Transact-SQL statements containing ad-hoc connector names, referencing any data source on the network that can be accessed using that OLE DB provider. To control access to data sources, members of the sysadmin role can disable ad-hoc access for that OLE DB provider, thereby limiting users to only those data sources referenced by linked server names defined by the administrators. By default, ad-hoc access is enabled for the SQL Server OLE DB provider, and disabled for all other OLE DB providers.

Distributed queries can allow users to access another data source (for example, files, non-relational data sources such as Active Directory, and so on) using the security context of the Microsoft Windows account under which the SQL Server service is running. SQL Server impersonates the login appropriately for Windows logins; however, that is not possible for SQL Server logins. This can potentially allow a distributed query user to access another data source for which they do not have permissions, but the account under which the SQL Server service is running does have permissions. Utilisation sp_addlinkedsrvlogin to define the specific logins that are authorized to access the corresponding linked server. This control is not available for ad-hoc names, so use caution in enabling an OLE DB provider for ad-hoc access.

When possible, SQL Server pushes relational operations such as joins, restrictions, projections, sorts, and group by operations to the OLE DB data source. SQL Server does not default to scanning the base table into SQL Server and performing the relational operations itself. SQL Server queries the OLE DB provider to determine the level of SQL grammar it supports, and, based on that information, pushes as many relational operations as possible to the provider.

SQL Server specifies a mechanism for an OLE DB provider to return statistics indicating how key values are distributed within the OLE DB data source. This lets the SQL Server Query Optimizer better analyze the pattern of data in the data source against the requirements of each Transact-SQL statement, increasing the ability of the Query Optimizer to generate optimal execution plans.

Query Processing Enhancements on Partitioned Tables and Indexes

SQL Server 2008 improved query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhanced the partitioning information provided in both compile-time and run-time execution plans. This topic describes these improvements, provides guidance on how to interpret the query execution plans of partitioned tables and indexes, and provides best practices for improving query performance on partitioned objects.

Remarque

Partitioned tables and indexes are supported only in the SQL Server Enterprise, Developer, and Evaluation editions.

New Partition-Aware Seek Operation

In SQL Server, the internal representation of a partitioned table is changed so that the table appears to the query processor to be a multicolumn index with PartitionID as the leading column. PartitionID is a hidden computed column used internally to represent the ID of the partition containing a specific row. For example, assume the table T, defined as T(a, b, c), is partitioned on column a, and has a clustered index on column b. In SQL Server, this partitioned table is treated internally as a nonpartitioned table with the schema T(PartitionID, a, b, c) and a clustered index on the composite key (PartitionID, b). This allows the Query Optimizer to perform seek operations based on PartitionID on any partitioned table or index.

Partition elimination is now done in this seek operation.

In addition, the Query Optimizer is extended so that a seek or scan operation with one condition can be done on PartitionID (as the logical leading column) and possibly other index key columns, and then a second-level seek, with a different condition, can be done on one or more additional columns, for each distinct value that meets the qualification for the first-level seek operation. That is, this operation, called a skip scan, allows the Query Optimizer to perform a seek or scan operation based on one condition to determine the partitions to be accessed and a second-level index seek operation within that operator to return rows from these partitions that meet a different condition. For example, consider the following query.

SELECT * FROM T WHERE a < 10 and b = 2;

For this example, assume that table T, defined as T(a, b, c), is partitioned on column a, and has a clustered index on column b. The partition boundaries for table T are defined by the following partition function:

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

To solve the query, the query processor performs a first-level seek operation to find every partition that contains rows that meet the condition T.a < 10. This identifies the partitions to be accessed. Within each partition identified, the processor then performs a second-level seek into the clustered index on column b to find the rows that meet the condition T.b = 2 et T.a < 10.

The following illustration is a logical representation of the skip scan operation. It shows table T with data in columns une et b. The partitions are numbered 1 through 4 with the partition boundaries shown by dashed vertical lines. A first-level seek operation to the partitions (not shown in the illustration) has determined that partitions 1, 2, and 3 meet the seek condition implied by the partitioning defined for the table and the predicate on column une. C'est, T.a < 10. The path traversed by the second-level seek portion of the skip scan operation is illustrated by the curved line. Essentially, the skip scan operation seeks into each of these partitions for rows that meet the condition b = 2. The total cost of the skip scan operation is the same as that of three separate index seeks.

skip_scan

Displaying Partitioning Information in Query Execution Plans

The execution plans of queries on partitioned tables and indexes can be examined by using the Transact-SQL ENSEMBLE des déclarations SET SHOWPLAN_XML ou SET STATISTICS XML, or by using the graphical execution plan output in SQL Server Management Studio. For example, you can display the compile-time execution plan by clicking Display Estimated Execution Plan on the Query Editor toolbar and the run-time plan by clicking Include Actual Execution Plan.

Using these tools, you can ascertain the following information:

  • The operations such as scans, cherche, inserts, mises à jour, merges, et supprime that access partitioned tables or indexes.
  • The partitions accessed by the query. For example, the total count of partitions accessed and the ranges of contiguous partitions that are accessed are available in run-time execution plans.
  • When the skip scan operation is used in a seek or scan operation to retrieve data from one or more partitions.

Partition Information Enhancements

SQL Server provides enhanced partitioning information for both compile-time and run-time execution plans. Execution plans now provide the following information:

  • An optional Partitioned attribute that indicates that an operator, such as a chercher, balayage, insérer, mettre à jour, fusionner, ou effacer, is performed on a partitioned table.
  • Un nouveau SeekPredicateNew element with a SeekKeys subelement that includes PartitionID as the leading index key column and filter conditions that specify range seeks on PartitionID. The presence of two SeekKeys subelements indicates that a skip scan operation on PartitionID est utilisé.
  • Summary information that provides a total count of the partitions accessed. This information is available only in run-time plans.

To demonstrate how this information is displayed in both the graphical execution plan output and the XML Showplan output, consider the following query on the partitioned table fact_sales. This query updates data in two partitions.

UPDATE fact_sales
SET quantity = quantity * 2
WHERE date_id BETWEEN 20080802 AND 20080902;

The following illustration shows the properties of the Clustered Index Seek operator in the compile-time execution plan for this query. To view the definition of the fact_sales table and the partition definition, see "Example" in this topic.

clustered_index_seek

Partitioned Attribute

When an operator such as an Index Seek is executed on a partitioned table or index, the Partitioned attribute appears in the compile-time and run-time plan and is set to True (1). The attribute does not display when it is set to False (0).

le Partitioned attribute can appear in the following physical and logical operators:

  • Table Scan
  • Index Scan
  • Index Seek
  • Insert
  • Mettre à jour
  • Effacer
  • Merge

As shown in the previous illustration, this attribute is displayed in the properties of the operator in which it is defined. In the XML Showplan output, this attribute appears as Partitioned="1" dans le RelOp node of the operator in which it is defined.

New Seek Predicate

In XML Showplan output, the SeekPredicateNew element appears in the operator in which it is defined. It can contain up to two occurrences of the SeekKeys sub-element. La première SeekKeys item specifies the first-level seek operation at the partition ID level of the logical index. That is, this seek determines the partitions that must be accessed to satisfy the conditions of the query. La deuxième SeekKeys item specifies the second-level seek portion of the skip scan operation that occurs within each partition identified in the first-level seek.

Partition Summary Information

In run-time execution plans, partition summary information provides a count of the partitions accessed and the identity of the actual partitions accessed. You can use this information to verify that the correct partitions are accessed in the query and that all other partitions are eliminated from consideration.

The following information is provided: Actual Partition Count, et Partitions Accessed.

Actual Partition Count is the total number of partitions accessed by the query.

Partitions Accessed, in XML Showplan output, is the partition summary information that appears in the new RuntimePartitionSummary element in RelOp node of the operator in which it is defined. The following example shows the contents of the RuntimePartitionSummary element, indicating that two total partitions are accessed (partitions 2 and 3).



    

        

    


Displaying Partition Information by Using Other Showplan Methods

The Showplan methods SHOWPLAN_ALL, SHOWPLAN_TEXT, et STATISTICS PROFILE do not report the partition information described in this topic, with the following exception. As part of the SEEK predicate, the partitions to be accessed are identified by a range predicate on the computed column representing the partition ID. The following example shows the SEEK predicate for a Clustered Index Seek operator. Partitions 2 and 3 are accessed, and the seek operator filters on the rows that meet the condition date_id BETWEEN 20080802 AND 20080902.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),

        SEEK:([PtnId1000] >= (2) AND [PtnId1000] <= (3) 

                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802) 

                ET [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902)) 

                ORDERED FORWARD)

Interpreting Execution Plans for Partitioned Heaps

A partitioned heap is treated as a logical index on the partition ID. Partition elimination on a partitioned heap is represented in an execution plan as a Table Scan operator with a SEEK predicate on partition ID. The following example shows the Showplan information provided:

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

Interpreting Execution Plans for Collocated Joins

Join collocation can occur when two tables are partitioned using the same or equivalent partitioning function and the partitioning columns from both sides of the join are specified in the join condition of the query. The Query Optimizer can generate a plan where the partitions of each table that have equal partition IDs are joined separately. Collocated joins can be faster than non-collocated joins because they can require less memory and processing time. The Query Optimizer chooses a non-collocated plan or a collocated plan based on cost estimates.

In a collocated plan, the Nested Loops join reads one or more joined table or index partitions from the inner side. The numbers within the Constant Scan operators represent the partition numbers.

When parallel plans for collocated joins are generated for partitioned tables or indexes, a Parallelism operator appears between the Constant Scan et le Nested Loops join operators. In this case, multiple worker threads on the outer side of the join each read and work on a different partition.

The following illustration demonstrates a parallel query plan for a collocated join.
colocated_join

Parallel Query Execution Strategy for Partitioned Objects

The query processor uses a parallel execution strategy for queries that select from partitioned objects. As part of the execution strategy, the query processor determines the table partitions required for the query and the proportion of worker threads to allocate to each partition. In most cases, the query processor allocates an equal or almost equal number of worker threads to each partition, and then executes the query in parallel across the partitions. The following paragraphs explain worker thread allocation in greater detail.

worker thread1

If the number of worker threads is less than the number of partitions, the query processor assigns each worker thread to a different partition, initially leaving one or more partitions without an assigned worker thread. When a worker thread finishes executing on a partition, the query processor assigns it to the next partition until each partition has been assigned a single worker thread. This is the only case in which the query processor reallocates worker threads to other partitions.
Shows worker thread reassigned after it finishes. If the number of worker threads is equal to the number of partitions, the query processor assigns one worker thread to each partition. When a worker thread finishes, it is not reallocated to another partition.

worker thread2

If the number of worker threads is greater than the number of partitions, the query processor allocates an equal number of worker threads to each partition. If the number of worker threads is not an exact multiple of the number of partitions, the query processor allocates one additional worker thread to some partitions in order to use all of the available worker threads. Note that if there is only one partition, all worker threads will be assigned to that partition. In the diagram below, there are four partitions and 14 worker threads. Each partition has 3 worker threads assigned, and two partitions have an additional worker thread, for a total of 14 worker thread assignments. When a worker thread finishes, it is not reassigned to another partition.

worker thread3

Although the above examples suggest a straightforward way to allocate worker threads, the actual strategy is more complex and accounts for other variables that occur during query execution. For example, if the table is partitioned and has a clustered index on column A and a query has the predicate clause WHERE A IN (13, 17, 25), the query processor will allocate one or more worker threads to each of these three seek values (A=13, A=17, and A=25) instead of each table partition. It is only necessary to execute the query in the partitions that contain these values, and if all of these seek predicates happen to be in the same table partition, all of the worker threads will be assigned to the same table partition.

To take another example, suppose that the table has four partitions on column A with boundary points (10, 20, 30), an index on column B, and the query has a predicate clause WHERE B IN (50, 100, 150). Because the table partitions are based on the values of A, the values of B can occur in any of the table partitions. Thus, the query processor will seek for each of the three values of B (50, 100, 150) in each of the four table partitions. The query processor will assign worker threads proportionately so that it can execute each of these 12 query scans in parallel.

Table partitions based on column A Seeks for column B in each table partition
Table Partition 1: A < 10 B=50, B=100, B=150
Table Partition 2: A >= 10 AND A < 20 B=50, B=100, B=150
Table Partition 3: A >= 20 AND A < 30 B=50, B=100, B=150
Table Partition 4: A >= 30 B=50, B=100, B=150

Best Practices

To improve the performance of queries that access a large amount of data from large partitioned tables and indexes, we recommend the following best practices:

  • Stripe each partition across many disks. This is especially relevant when using spinning disks.
  • When possible, use a server with enough main memory to fit frequently accessed partitions or all partitions in memory to reduce I/O cost.
  • If the data you query will not fit in memory, compress the tables and indexes. This will reduce I/O cost.
  • Use a server with fast processors and as many processor cores as you can afford, to take advantage of parallel query processing capability.
  • Ensure the server has sufficient I/O controller bandwidth.
  • Create a clustered index on every large partitioned table to take advantage of B-tree scanning optimizations.
  • Follow the best practice recommendations in the white paper, The Data Loading Performance Guide, when bulk loading data into partitioned tables.

Exemple

The following example creates a test database containing a single table with seven partitions. Use the tools described previously when executing the queries in this example to view partitioning information for both compile-time and run-time plans.

Remarque

This example inserts more than 1 million rows into the table. Running this example may take several minutes depending on your hardware. Before executing this example, verify that you have more than 1.5 GB of disk space available.

USE master;
ALLER
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
ALLER
CREATE DATABASE db_sales_test;
ALLER
USE db_sales_test;
ALLER
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
ALLER
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
ALLER
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
ALLER
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
ALLER
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
COMMENCER
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @ i + = 1;
END;
ALLER
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
COMMENCER
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @ i + = 1;
END;
PRINT 'Done.';
ALLER
-- Two-partition query.
SET STATISTICS XML ON;
ALLER
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
ALLER
SET STATISTICS XML OFF;
ALLER
-- Single-partition query.
SET STATISTICS XML ON;
ALLER
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
ALLER
SET STATISTICS XML OFF;
ALLER

Lecture supplémentaire

Showplan Logical and Physical Operators Reference
Extended Events
Best Practice with the Query Store
Cardinality Estimation
Intelligent query processing
Operator Precedence
Execution Plans
Performance Center for SQL Server Database Engine and Azure SQL Database

<! – ->

  • Gum Bain de Bouche Gingidex Traitement d'Attaque Anti-Plaque 300ml
    Gum Gingidex Bain de Bouche Traitement d'Attaque anti-plaque est indiqué dans la lutte contre la plaque dentaire et la santé de vos gencives. Il stoppe les saignements, réduit l'inflammation des gencives et la formation de la plaque dentaire. En traitement d'attaque (0,12%) Grace à son système double action
  • Bien Etre Bien-Être L'Eau Parfumée des Familles Tendresse d'Enfance Eau de Cologne 250ml
    Grand connaisseur des plantes et de leurs essences naturelles, Bien-être vous propose de découvrir tous les bienfaits de son Eau de Cologne Tendresse d'Enfance 250ml subtile, délicate et naturellement hydratante. Laissez-vous emporter par son parfum réconfortant comme un souvenir d'enfance, alliant la
  • Wonderbox Coffret cadeau - Accès au spa d'un Château 3* en Normandie - Beauté & bien-être
    Coffret cadeau Wonderbox - À Hébécrevon, dans la Manche, le Château de la Roque, niché dans un remarquable cadre de verdure, invite au repos et à la détente. Cette demeure de charme vous ouvre les portes de son spa doté d'un hammam, d'un sauna et d'une piscine. Avec ses murs en pierres apparentes et ses
  • Theseo Sanocidex Traitement de l'Eau de Boisson 210kg
    Sanocidex est un produit destiné au détartrage, décapage des bacs, canalisations d'eau de boisson des animaux et machines à soupe. Son action biocide est efficace à faibles concentrations sur de nombreux micro-organismes (bactéries, virus, champignons, algues).
  • Theseo Sanocidex Traitement de l'Eau de Boisson 24kg
    Sanocidex est un produit destiné au détartrage, décapage des bacs, canalisations d'eau de boisson des animaux et machines à soupe. Son action biocide est efficace à faibles concentrations sur de nombreux micro-organismes (bactéries, virus, champignons, algues).
  • Theseo Sanocidex Traitement de l'Eau de Boisson 10kg
    Sanocidex est un produit destiné au détartrage, décapage des bacs, canalisations d'eau de boisson des animaux et machines à soupe. Son action biocide est efficace à faibles concentrations sur de nombreux micro-organismes (bactéries, virus, champignons, algues).
  • LG Barre de son LG SK1D
    Intégrant les technologies Bluetooth, Adaptative Sound Control et Auto Sound Engine, l'enceinte amplifiée LG SK1D dévoile un son optimisé de qualité et vous permet de profiter de toute votre musique, quel que soit le support, grâce à une connexion sans fil. Facile à utiliser, elle deviendra bien vite un
  • Bien choisir son école d'ingénieurs - Céline Manceau - Livre
    Orientation - Occasion - Bon Etat - Les Guides de l'Etudiant - Poche - Structure Coopérative d'insertion à but non lucratif.
  • Bien choisir son Ecole d'art - Céline Manceau - Livre
    Orientation - Occasion - Bon Etat - Les Guides de l'Etudiant - Poche - Structure Coopérative d'insertion à but non lucratif.
  • Bien choisir et préparer son école d'art - Xavier De La Mola - Livre
    Orientation - Occasion - Bon Etat - Poche Studyrama - Poche - Structure Coopérative d'insertion à but non lucratif.

Laisser un commentaire