Résoudre les problèmes de performances UPDATE avec des plans étroits et larges dans SQL Server
S’applique à : SQL Server
Une UPDATE instruction peut être plus rapide dans certains cas et plus lente dans d’autres. De nombreux facteurs peuvent entraîner une telle variation, notamment le nombre de lignes mises à jour et l’utilisation des ressources sur le système (blocage, processeur, mémoire ou E/S). Cet article traite d’une raison spécifique de l’écart : le choix du plan de requête effectué par SQL Server.
Que sont les plans étroits et larges ?
Lorsque vous exécutez une UPDATE instruction sur une colonne d’index cluster, SQL Server met à jour non seulement l’index cluster lui-même, mais également tous les index non cluster, car les index non cluster contiennent la clé d’index de cluster.
SQL Server dispose de deux options pour effectuer la mise à jour :
Plan étroit : effectuez la mise à jour de l’index non cluster, ainsi que la mise à jour de la clé d’index cluster. Cette approche simple est facile à comprendre ; mettre à jour l’index cluster, puis mettre à jour tous les index non cluster en même temps. SQL Server mettre à jour une ligne et passer à la suivante jusqu’à ce que toutes les lignes soient terminées. Cette approche est appelée mise à jour de plan étroit ou mise à jour Per-Row. Toutefois, cette opération est relativement coûteuse, car l’ordre des données d’index non cluster qui seront mises à jour peut ne pas être dans l’ordre des données d’index cluster. Si de nombreuses pages d’index sont impliquées dans la mise à jour, lorsque les données sont sur le disque, un grand nombre de demandes d’E/S aléatoires peuvent se produire.
Plan large : pour optimiser les performances et réduire les E/S aléatoires, SQL Server pouvez choisir un plan large. Il n’effectue pas la mise à jour des index non cluster, ainsi que la mise à jour de l’index cluster. Au lieu de cela, il trie d’abord toutes les données d’index non cluster en mémoire, puis met à jour tous les index dans cet ordre. Cette approche est appelée plan large (également appelée mise à jour Per-Index).
Voici une capture d’écran des plans étroits et larges :
Quand SQL Server choisissez-vous un plan large ?
Deux critères doivent être remplis pour SQL Server choisir un plan large :
- Le nombre de lignes affectées est supérieur à 250.
- La taille du niveau feuille des index non cluster (nombre de pages d’index * 8 Ko) est d’au moins 1/1 000 du paramètre de mémoire maximale du serveur.
Comment fonctionnent les plans étroits et larges ?
Pour comprendre le fonctionnement des plans étroits et larges, procédez comme suit dans l’environnement suivant :
- SQL Server 2019 CU11
- Mémoire maximale du serveur = 1 500 Mo
Exécutez le script suivant pour créer une table
mytable1contenant 41 501 lignes, un index cluster sur la colonnec1et cinq index non cluster sur le reste des colonnes, respectivement.CREATE TABLE mytable1(c1 INT,c2 CHAR(30),c3 CHAR(20),c4 CHAR(30),c5 CHAR(30)) GO WITH cte AS ( SELECT ROW_NUMBER() OVER(ORDER BY c1.object_id) id FROM sys.columns CROSS JOIN sys.columns c1 ) INSERT mytable1 SELECT TOP 41000 id,REPLICATE('a',30),REPLICATE('a',20),REPLICATE('a',30),REPLICATE('a',30) FROM cte GO INSERT mytable1 SELECT TOP 250 50000,c2,c3,c4,c5 FROM mytable1 GO INSERT mytable1 SELECT TOP 251 50001,c2,c3,c4,c5 FROM mytable1 GO CREATE CLUSTERED INDEX ic1 ON mytable1(c1) CREATE INDEX ic2 ON mytable1(c2) CREATE INDEX ic3 ON mytable1(c3) CREATE INDEX ic4 ON mytable1(c4) CREATE INDEX ic5 ON mytable1(c5)Exécutez les trois instructions T-SQL
UPDATEsuivantes et comparez les plans de requête :UPDATE mytable1 SET c1=c1 WHERE c1=1 OPTION(RECOMPILE)- une ligne est mise à jourUPDATE mytable1 SET c1=c1 WHERE c1=50000 OPTION(RECOMPILE)- 250 lignes sont mises à jour.UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE)- 251 lignes sont mises à jour.
Examinez les résultats en fonction du premier critère (le seuil du nombre de lignes affecté est de 250).
La capture d’écran suivante montre les résultats basés sur le premier critère :
Comme prévu, l’optimiseur de requête choisit un plan étroit pour les deux premières requêtes, car le nombre de lignes affectées est inférieur à 250. Un plan large est utilisé pour la troisième requête, car le nombre de lignes affectées est de 251, ce qui est supérieur à 250.
Examinez les résultats en fonction du deuxième critère (la mémoire de la taille de l’index feuille est au moins égale à 1/1000 du paramètre mémoire maximale du serveur).
La capture d’écran suivante montre les résultats basés sur le deuxième critère :
Un plan large est sélectionné pour la troisième
UPDATErequête. Toutefois, l’indexic3(sur la colonnec3) n’est pas visible dans le plan. Le problème se produit parce que le deuxième critère n’est pas rempli : la taille de l’index des pages feuilles par rapport au paramètre mémoire maximale du serveur.Le type de données de colonne , et est , tandis que le type de données de colonne
c3estchar(20).char(30)c4c4c2La taille de chaque ligne d’indexic3étant inférieure à celle des autres, le nombre de pages feuilles est inférieur à celui des autres.Avec l’aide de la fonction de gestion dynamique (DMF),
sys.dm_db_database_page_allocationsvous pouvez calculer le nombre de pages pour chaque index. Pour lesic2index ,ic4etic5, chaque index a 214 pages, dont 209 sont des pages feuilles (les résultats peuvent varier légèrement). La mémoire consommée par les pages feuilles est de 209 x 8 = 1 672 Ko. Par conséquent, le rapport est de 1672/(1500 x 1024) = 0,00108854101, ce qui est supérieur à 1/1000. Cependant, leic3seul compte 161 pages ; 159 d’entre elles sont des pages feuilles. Le rapport est de 159 x 8/(1500 x 1024) = 0,000828125, ce qui est inférieur à 1/1000 (0,001).Si vous insérez plus de lignes ou si vous réduisez la mémoire maximale du serveur pour répondre au critère, le plan change. Pour que la taille de niveau feuille de l’index soit supérieure à 1/1000, vous pouvez réduire le paramètre de mémoire maximale du serveur à 1 200 en exécutant les commandes suivantes :
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'max server memory', 1200; GO RECONFIGURE GO UPDATE mytable1 SET c1=c1 WHERE c1=50001 OPTION(RECOMPILE) --251 rows are updated.Dans ce cas, 159 x 8/(1200 x 1024) = 0,00103515625 > 1/1000. Après cette modification, le
ic3apparaît dans le plan.Pour plus d’informations sur
show advanced options, consultez Utiliser Transact-SQL.La capture d’écran suivante montre que le plan large utilise tous les index lorsque le seuil de mémoire est atteint :
Un plan large est-il plus rapide qu’un plan étroit ?
La réponse est que cela dépend de la mise en cache ou non des pages de données et d’index dans le pool de mémoires tampons.
Les données sont mises en cache dans le pool de mémoires tampons
Si les données se trouvent déjà dans le pool de mémoires tampons, la requête avec le plan large n’offre pas nécessairement d’avantages supplémentaires en matière de performances par rapport aux plans étroits, car le plan large est conçu pour améliorer les performances d’E/S (lectures physiques, et non lectures logiques).
Pour tester si un plan large est plus rapide qu’un plan étroit lorsque les données se situent dans un pool de mémoires tampons, procédez comme suit dans l’environnement suivant :
SQL Server 2019 CU11
Mémoire maximale du serveur : 30 000 Mo
La taille des données est de 64 Mo, tandis que la taille de l’index est d’environ 127 Mo.
Les fichiers de base de données se trouvent sur deux disques physiques différents :
- I :\sql19\dbWideplan.mdf
- H :\sql19\dbWideplan.ldf
Créez une autre table,
mytable2, en exécutant les commandes suivantes :CREATE TABLE mytable2(C1 INT,C2 INT,C3 INT,C4 INT,C5 INT) GO CREATE CLUSTERED INDEX IC1 ON mytable2(C1) CREATE INDEX IC2 ON mytable2(C2) CREATE INDEX IC3 ON mytable2(C3) CREATE INDEX IC4 ON mytable2(C4) CREATE INDEX IC5 ON mytable2(C5) GO DECLARE @N INT=1 WHILE @N<1000000 BEGIN DECLARE @N1 INT=RAND()*4500 DECLARE @N2 INT=RAND()*100000 DECLARE @N3 INT=RAND()*100000 DECLARE @N4 INT=RAND()*100000 DECLARE @N5 INT=RAND()*100000 INSERT mytable2 VALUES(@N1,@N2,@N3,@N4,@N5) SET @N+=1 END GO UPDATE STATISTICS mytable2 WITH FULLSCANExécutez les deux requêtes suivantes pour comparer les plans de requête :
update mytable2 set c1=c1 where c2<260 option(querytraceon 8790) --trace flag 8790 will force Wide plan update mytable2 set c1=c1 where c2<260 option(querytraceon 2338) --trace flag 2338 will force Narrow planPour plus d’informations, consultez indicateur de trace 8790 et indicateur de trace 2338.
La requête avec le plan large prend 0,136 seconde, tandis que la requête avec le plan étroit prend seulement 0,112 seconde. Les deux durées sont très proches et la mise à jour Per-Index (plan large) est moins bénéfique, car les données se trouvent déjà dans la mémoire tampon avant l’exécution de l’instruction
UPDATE.La capture d’écran suivante montre des plans larges et étroits lorsque les données sont mises en cache dans le pool de mémoires tampons :
Les données ne sont pas mises en cache dans le pool de mémoires tampons
Pour tester si un plan large est plus rapide qu’un plan étroit lorsque les données ne sont pas dans le pool de mémoires tampons, exécutez les requêtes suivantes :
Remarque
Lorsque vous effectuez le test, vérifiez que votre charge de travail est la seule dans SQL Server et que les disques sont dédiés à SQL Server.
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
WAITFOR DELAY '00:00:02' --wait for 1~2 seconds
UPDATE mytable2 SET c1=c1 WHERE c2 < 260 OPTION (QUERYTRACEON 8790) --force Wide plan
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO
WAITFOR DELAY '00:00:02' --wait for 1~2 SECONDS
UPDATE mytable2 SET c1=c1 WHERE c2 < 260 OPTION (QUERYTRACEON 2338) --force Narrow plan
La requête avec un plan large prend 3,554 secondes, tandis que la requête avec un plan étroit prend 6,701 secondes. Cette fois, la requête de plan large s’exécute plus rapidement.
La capture d’écran suivante montre le plan large lorsque les données ne sont pas mises en cache dans le pool de mémoires tampons :
La capture d’écran suivante montre le plan étroit lorsque les données ne sont pas mises en cache dans le pool de mémoires tampons :
Une requête de plan large est-elle toujours plus rapide qu’un plan de requête étroit lorsque les données ne sont pas dans la mémoire tampon ?
La réponse est « pas toujours ». Pour tester si la requête de plan large est toujours plus rapide que le plan de requête étroit lorsque les données ne sont pas dans la mémoire tampon, procédez comme suit :
Créez une autre table,
mytable2, en exécutant les commandes suivantes :SELECT c1,c1 AS c2,c1 AS C3,c1 AS c4,c1 AS C5 INTO mytable3 FROM mytable2 GO CREATE CLUSTERED INDEX IC1 ON mytable3(C1) CREATE INDEX IC2 ON mytable3(C2) CREATE INDEX IC3 ON mytable3(C3) CREATE INDEX IC4 ON mytable3(C4) CREATE INDEX IC5 ON mytable3(C5) GOest
mytable3identique àmytable2, à l’exception des données.mytable3a les cinq colonnes avec la même valeur, ce qui fait que l’ordre des index non cluster suit l’ordre de l’index cluster. Ce tri des données réduit l’avantage du plan large.Exécutez les commandes suivantes pour comparer les plans de requête :
CHECKPOINT GO DBCC DROPCLEANBUFFERS go UPDATE mytable3 SET c1=c1 WHERE c2<12 OPTION(QUERYTRACEON 8790) --tf 8790 will force Wide plan CHECKPOINT GO DBCC DROPCLEANBUFFERS GO UPDATE mytable3 SET c1=c1 WHERE c2<12 OPTION(QUERYTRACEON 2338) --tf 2338 will force Narrow planLa durée des deux requêtes est considérablement réduite ! Le plan large prend 0,304 seconde, ce qui est un peu plus lent que le plan étroit cette fois.
La capture d’écran suivante montre la comparaison des performances lorsque large et étroite sont utilisées :
Scénarios où les plans larges sont appliqués
Voici les autres scénarios dans lesquels des plans larges sont également appliqués :
La colonne d’index cluster a une clé unique ou primaire, et plusieurs lignes sont mises à jour
Voici un exemple pour reproduire le scénario :
CREATE TABLE mytable4(c1 INT primary key,c2 INT,c3 INT,c4 INT)
GO
CREATE INDEX ic2 ON mytable4(c2)
CREATE INDEX ic3 ON mytable4(c3)
CREATE INDEX ic4 ON mytable4(c4)
GO
INSERT mytable4 VALUES(0,0,0,0)
INSERT mytable4 VALUES(1,1,1,1)
La capture d’écran suivante montre que le plan large est utilisé lorsque l’index de cluster a une clé unique :
Pour plus d’informations, consultez Maintenance des index uniques.
La colonne d’index de cluster est spécifiée dans le schéma de partition
Voici un exemple pour reproduire le scénario :
CREATE TABLE mytable5(c1 INT,c2 INT,c3 INT,c4 INT)
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name='PS1')
DROP PARTITION SCHEME PS1
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name='PF1')
DROP PARTITION FUNCTION PF1
GO
CREATE PARTITION FUNCTION PF1(INT) AS
RANGE right FOR VALUES
(2000)
GO
CREATE PARTITION SCHEME PS1 AS
PARTITION PF1 all TO
([PRIMARY])
GO
CREATE CLUSTERED INDEX c1 ON mytable5(c1) ON PS1(c1)
CREATE INDEX c2 ON mytable5(c2)
CREATE INDEX c3 ON mytable5(c3)
CREATE INDEX c4 ON mytable5(c4)
GO
UPDATE mytable5 SET c1=c1 WHERE c1=1
La capture d’écran suivante montre que le plan large est utilisé lorsqu’il existe une colonne en cluster dans le schéma de partition :
La colonne d’index cluster ne fait pas partie du schéma de partition et la colonne du schéma de partition est mise à jour
Voici un exemple pour reproduire le scénario :
CREATE TABLE mytable6(c1 INT,c2 INT,c3 INT,c4 INT)
GO
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name='PS2')
DROP PARTITION SCHEME PS2
GO
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name='PF2')
DROP PARTITION FUNCTION PF2
GO
CREATE PARTITION FUNCTION PF2(int) AS
RANGE right FOR VALUES
(2000)
GO
CREATE PARTITION SCHEME PS2 AS
PARTITION PF2 all TO
([PRIMARY])
GO
CREATE CLUSTERED INDEX c1 ON mytable6(c1) ON PS2(c2) --on c2 column
CREATE INDEX c3 ON mytable6(c3)
CREATE INDEX c4 ON mytable6(c4)
La capture d’écran suivante montre que le plan large est utilisé lorsque la colonne du schéma de partition est mise à jour :
Conclusion
SQL Server choisit une mise à jour de plan étendue lorsque les critères suivants sont remplis en même temps :
- Le nombre de lignes affectées est supérieur à 250.
- La mémoire de l’index feuille est au moins égale à 1/000 du paramètre de mémoire maximale du serveur.
Les plans étendus augmentent les performances au détriment de la consommation de mémoire supplémentaire.
Si le plan de requête attendu n’est pas utilisé, cela peut être dû à des statistiques obsolètes (ne signalant pas la taille correcte des données), au paramètre de mémoire maximale du serveur ou à d’autres problèmes non liés tels que les plans sensibles aux paramètres.
La durée des
UPDATEénoncés utilisant un plan large dépend de plusieurs facteurs et, dans certains cas, cela peut prendre plus de temps que des plans étroits.L’indicateur de trace 8790 force un plan large ; l’indicateur de trace 2338 force un plan étroit.
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour