{"id":735,"date":"2012-10-10T22:11:23","date_gmt":"2012-10-10T20:11:23","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=735"},"modified":"2026-05-02T14:31:01","modified_gmt":"2026-05-02T12:31:01","slug":"index-pour-loccasion","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/index-pour-loccasion\/","title":{"rendered":"Un index pour l’occasion"},"content":{"rendered":"

Lorsque l’on consulte les diff\u00e9rentes vues syst\u00e8me d’une instance SQL, et plus particuli\u00e8rement la vue sys.dm_db_missing_index_details, il peut arriver de trouver des index qui sont tr\u00e8s utiles, mais seulement pour des actions \u00ab\u00a0rares\u00a0\u00bb.<\/p>\n

Dans ce cas, le fait de cr\u00e9er un index permanent n’est peut-\u00eatre pas la meilleure m\u00e9thode, et il est \u00e0 envisager de cr\u00e9er un index juste pour l’occasion.<\/p>\n

Prenons un exemple fictif. Tout d’abord, cr\u00e9ons une table.<\/p>\n

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test]') AND type in (N'U'))\r\nBEGIN\r\nCREATE TABLE [dbo].[Test](\r\n\t[Id] [int] IDENTITY(1,1) NOT NULL,\r\n\t[NewId] [uniqueidentifier] NOT NULL,\r\n\t[Aleatoire] [float] NOT NULL,\r\n\t[Chaine] [char](10) NOT NULL,\r\n CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED \r\n(\r\n\t[Id] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n) ON [PRIMARY]\r\nEND\r\nGO\r\n\r\nIF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_Test_NewId]') AND type = 'D')\r\nBEGIN\r\nALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_Test_NewId]  DEFAULT (newid()) FOR [NewId]\r\nEND\r\n\r\nIF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DF_Test_Aleatoire]') AND type = 'D')\r\nBEGIN\r\nALTER TABLE [dbo].[Test] ADD  CONSTRAINT [DF_Test_Aleatoire]  DEFAULT (rand()) FOR [Aleatoire]\r\nEND<\/pre>\n
Ensuite, remplissons-la avec quelques enregistrements. Pour l’exemple, ce remplissage correspondra \u00e0 une charge mensuelle de travail.<\/p>\n
truncate table Test\r\nGO\r\n\r\nSET NOCOUNT ON\r\nGO\r\n\r\nCREATE TABLE #Lot(Id int,Test bit)\r\nINSERT INTO #Lot (Test) values (0),(1) --2\r\nINSERT INTO #Lot (Test) SELECT Test FROM #Lot --4\r\nINSERT INTO #Lot (Test) SELECT Test FROM #Lot --8\r\nINSERT INTO #Lot (Test) SELECT Test FROM #Lot --16\r\nINSERT INTO #Lot (Test) SELECT Test FROM #Lot --32\r\nINSERT INTO #Lot (Test) SELECT Test FROM #Lot --64\r\nINSERT INTO #Lot (Test) SELECT Test FROM #Lot --128\r\nINSERT INTO #Lot (Test) SELECT Test FROM #Lot --256\r\nINSERT INTO #Lot (Test) SELECT Test FROM #Lot --512\r\nINSERT INTO #Lot (Test) SELECT Test FROM #Lot --1024\r\nGO\r\n\r\ninsert into Test (Chaine)\r\nSELECT 'Test' FROM #Lot\r\nGO 10000\r\n\r\nDROP TABLE #Lot\r\nGO<\/pre>\n
Dans l’\u00e9tat, sur une machine de test, ce script s’ex\u00e9cute en 4 minutes 45. Ce sera notre r\u00e9f\u00e9rence pour la charge mensuelle li\u00e9e \u00e0 l’alimentation de cette table.<\/p>\n
Maintenant, supposons un certain script qui tournerait une fois par mois.<\/p>\n
declare @Central float\r\ndeclare @Liste table (Central float, NbVoisins int)\r\n\r\nDECLARE curseur CURSOR FOR \r\nSELECT TOP 10000 Aleatoire\r\nFROM Test\r\nOrder by [NewId]\r\n\r\nOPEN curseur\r\nFETCH NEXT FROM curseur INTO @Central\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN\r\n\r\n\tINSERT INTO @Liste(Central,NbVoisins)\r\n\tSELECT @Central,COUNT(*)\r\n\tFROM Test\r\n\tWHERE Aleatoire BETWEEN @Central-0.00001 AND @Central+0.00001\r\n\r\n\tFETCH NEXT FROM curseur INTO @Central\r\nEND\r\n\r\nCLOSE curseur\r\nDEALLOCATE curseur\r\n\r\nselect * from @Liste<\/pre>\n
En l’\u00e9tat actuel des choses, ce script s’ex\u00e9cute en 2 heures 30. C’est un peu long, et m\u00eame si ce script est mensuel, il peut \u00eatre int\u00e9ressant de cherchre \u00e0 r\u00e9duire son temps d’ex\u00e9cution.<\/p>\n
En cherchant \u00e0 l’optimiser, on peut observer la vue syst\u00e8me des index manquants, et constater que le syst\u00e8me recommande un index sur la colonne [Aleatoire] de notre table.<\/p>\n
\"\"<\/a><\/p>\n
Cr\u00e9ons donc cet index, pour voir s’il est efficace.<\/p>\n
create index IX_Aleatoire ON Test(Aleatoire)<\/pre>\n
La cr\u00e9ation de l’index prend environ 40 secondes.<\/p>\n
Maintenant, en relan\u00e7ant le script mensuel, le temps d’ex\u00e9cution tombe \u00e0 1 minute 30 secondes.<\/p>\n
L’index s’av\u00e8re donc (tr\u00e8s)\u00a0efficace, et on serait tent\u00e9 de le conserver et d’en rester l\u00e0.<\/p>\n
Mais il s’agit l\u00e0 en fait d’une grave erreur. En effet, le syst\u00e8me devra maintenir cet index en permanence, tout au long du processus mensuel de remplissage de la table de donn\u00e9es. Et cela a un co\u00fbt non n\u00e9gligeable. En effet, en relan\u00e7ant le script simulant l’alimentation mensuelle, on obtient d\u00e9sormais une dur\u00e9e d’ex\u00e9cution de 7 minutes 15 l\u00e0 o\u00f9 pr\u00e9c\u00e9demment nous n’avions besoin que de moins de 5 minutes en temps cumul\u00e9.<\/p>\n
Cette augmentation est due au surco\u00fbt du maintien de l’index, rempli avec des valeurs non s\u00e9quentielles.<\/p>\n
Au final, nous avons le d\u00e9compte de temps suivant, pour la charge de remplissage plus le script mensuel :<\/p>\n\n\n\n\n\n\n
Cas<\/strong><\/td>\n\u00a0Dur\u00e9e charge mensuelle<\/strong><\/td>\nCr\u00e9ation index\u00a0<\/strong><\/td>\n\u00a0Traitement ponctuel<\/strong><\/td>\nTotal\u00a0<\/strong><\/td>\n<\/tr>\n
Sans index<\/td>\n\u00a04 min 45<\/td>\n<\/td>\n\u00a0150 min<\/td>\n\u00a0155 min<\/td>\n<\/tr>\n
Index permanent<\/td>\n\u00a07 min 15<\/td>\n<\/td>\n\u00a01 min 30<\/td>\n\u00a08 min 45<\/td>\n<\/tr>\n
Index pour l’occasion<\/td>\n\u00a04 min 45<\/td>\n\u00a040 s<\/td>\n\u00a01 min 30<\/td>\n\u00a06 min 35<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Cet exemple montre donc que des index, aussi efficaces soient-ils, on un surco\u00fbt pour \u00eatre maintenus, et qu’il peut parfois \u00eatre pr\u00e9f\u00e9rable de les cr\u00e9er juste le temps de les utiliser s’ils ne sont b\u00e9n\u00e9fiques que lors de certains traitements peu fr\u00e9quents.<\/p>\n","protected":false},"excerpt":{"rendered":"
Lorsque l’on consulte les diff\u00e9rentes vues syst\u00e8me d’une instance SQL, et plus particuli\u00e8rement la vue sys.dm_db_missing_index_details, il peut arriver de trouver des index qui sont tr\u00e8s utiles, mais seulement pour des actions \u00ab\u00a0rares\u00a0\u00bb. Dans ce cas, le fait de cr\u00e9er … Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-735","post","type-post","status-publish","format-standard","hentry","category-article_sql"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/735","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=735"}],"version-history":[{"count":13,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/735\/revisions"}],"predecessor-version":[{"id":1938,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/735\/revisions\/1938"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=735"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=735"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=735"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}