J'effectue une insertion simple de quelques centaines de lignes, par exemple:

INSERT INTO Foo SELECT * FROM Bar 

La table a une poignée d'index secondaires. Avec ces index désactivés, la requête s'exécute presque instantanément. Avec les index secondaires activés, la requête prend quelques secondes pour s'exécuter, avec un coût de sous-tree relativement élevé.

Le problème est que pour chaque index secondaire, la database effectue:

• Opération physique : Bobine de table
• Opération logique : Bobine paresseuse

où il met en cache:

• Comment accélérer une requête avec des loops nestedes
• Clés primaires composites en relation NM ou non?
• Quelle est la différence entre "BETWEEN AND" et "> AND <=" sur datetime?
• Comment optimiser le SQL 'XQuery'
• Optimisation des requêtes SQL Server Spatial

• toutes les colonnes de la table de destiation (quand elle a seulement besoin des valeurs dont elle a besoin)
• valeurs multiples (plutôt qu'une seule fois)

Bien qu'il puisse être intéressant de savoir pourquoi SQL Server (2008 R2 SP2) pense qu'il a besoin de faire cela, ce dont j'ai vraiment besoin, c'est de faire en sorte que l'insertion de 100 lignes dans un server ne prenne pas six secondes.

La partie vraiment, vraiment, horrible est que chaque pour spool de table, SQL Server met en cache la valeur de chaque colonne, à chaque fois:

Ce qui est juste la combustion IO logique.

• Sans ces mises à jour d'index problématiques, l'import complète de 60 000 lignes se produit dans une seconde ou deux
• Avec ces index, l'import complète prend littéralement des dizaines de minutes

Étapes à suivre pour reproduire

Bien sûr, ma vraie table AuditLog contient 4M lignes. Mais nous pouvons reproduire exactement les mêmes opérateurs, avec un coût de sous-tree élevé, en utilisant une table AuditLog vide:

 CREATE TABLE [dbo].[AuditLog]( [AuditLogID] [int] IDENTITY(216,1) NOT NULL, [ChangeDate] [datetime] NOT NULL CONSTRAINT [DF_AuditLog_ChangeDate] DEFAULT (getdate()), [RowGUID] [uniqueidentifier] NOT NULL, [ChangeType] [varchar](50) NOT NULL, [TableName] [varchar](128) NOT NULL, [FieldName] [varchar](128) NOT NULL, [OldValue] [varchar](max) NULL, [NewValue] [varchar](max) NULL, [SystemUser] [varchar](128) NULL CONSTRAINT [DF_AuditLog_SystemUser] DEFAULT (suser_sname()), [Username] [varchar](128) NOT NULL CONSTRAINT [DF_AuditLog_Username] DEFAULT (user_name()), [Hostname] [varchar](50) NOT NULL CONSTRAINT [DF_AuditLog_Hostname] DEFAULT (host_name()), [AppName] [varchar](128) NULL CONSTRAINT [DF_AuditLog_AppName] DEFAULT (app_name()), [UserGUID] [uniqueidentifier] NULL, [TagGUID] [uniqueidentifier] NULL, [Tag] [varchar](max) NULL, [timestamp] [timestamp] NOT NULL, CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED ([AuditLogID] ASC) ) 

Et nous avons les index douloureux:

 SET ANSI_PADDING OFF GO /****** Object: Index [IX_AuditLog_ChangeDate] Script Date: 11/17/2016 2:58:43 PM ******/ CREATE NONCLUSTERED INDEX [IX_AuditLog_ChangeDate] ON [dbo].[AuditLog] ( [ChangeDate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [IX_AuditLog_FieldName] Script Date: 11/17/2016 2:58:43 PM ******/ CREATE NONCLUSTERED INDEX [IX_AuditLog_FieldName] ON [dbo].[AuditLog] ( [FieldName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [IX_AuditLog_LastRowActionByTable] Script Date: 11/17/2016 2:58:43 PM ******/ CREATE NONCLUSTERED INDEX [IX_AuditLog_LastRowActionByTable] ON [dbo].[AuditLog] ( [TableName] ASC, [ChangeType] ASC, [RowGUID] ASC, [UserGUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO /****** Object: Index [IX_AuditLog_RowGUID] Script Date: 11/17/2016 2:58:43 PM ******/ CREATE NONCLUSTERED INDEX [IX_AuditLog_RowGUID] ON [dbo].[AuditLog] ( [RowGUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [IX_AuditLog_RowInsertedByUserGUID] Script Date: 11/17/2016 2:58:43 PM ******/ CREATE NONCLUSTERED INDEX [IX_AuditLog_RowInsertedByUserGUID] ON [dbo].[AuditLog] ( [ChangeType] ASC, [RowGUID] ASC, [UserGUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO /****** Object: Index [IX_AuditLog_RowLastModifiedByUserGUID] Script Date: 11/17/2016 2:58:43 PM ******/ CREATE NONCLUSTERED INDEX [IX_AuditLog_RowLastModifiedByUserGUID] ON [dbo].[AuditLog] ( [RowGUID] ASC, [ChangeDate] ASC, [UserGUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [IX_AuditLog_TableName] Script Date: 11/17/2016 2:58:43 PM ******/ CREATE NONCLUSTERED INDEX [IX_AuditLog_TableName] ON [dbo].[AuditLog] ( [TableName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO /****** Object: Index [IX_AuditLog_TagGUID] Script Date: 11/17/2016 2:58:43 PM ******/ CREATE NONCLUSTERED INDEX [IX_AuditLog_TagGUID] ON [dbo].[AuditLog] ( [TagGUID] ASC, [RowGUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] GO /****** Object: Index [IX_AuditLog_UserGUID] Script Date: 11/17/2016 2:58:43 PM ******/ CREATE NONCLUSTERED INDEX [IX_AuditLog_UserGUID] ON [dbo].[AuditLog] ( [ChangeDate] ASC, [UserGUID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] 

Et puis nous créons notre insert:

 INSERT INTO AuditLog( RowGUID, ChangeType, UserGUID, TableName, FieldName, TagGUID, Tag) SELECT 'E5E31EDD-7D39-47FD-BCFF-4B7044AC433D', 'INSERTED', '4A2FDACD-0209-403B-ADBC-1B8A68E90350', --UserGUID 'Customers', --TableName '', --FieldName '7A74267D-64F9-44D7-A1D7-1490A66136BF', --TagGUID 'Contoso' FROM ( --A dummy derived table that lets us select the above row 100 times SELECT TOP 400 (a.Number * 256) + b.Number AS Number FROM ( SELECT number FROM master..spt_values WHERE type = 'P' AND number <= 255) a (Number), (SELECT number FROM master..spt_values WHERE type = 'P' AND number <= 255) b (Number) ) dt 

Les time d'attente que vous requestz?

 | Wait Type | Wait Time (s) | Wait Count | |----------------|---------------|------------| | IO_COMPLETION | 4.55 s | 211 | | WRITELOG | 0.79 s | 37 | | PAGEIOLATCH_UP | 0.36 s | 1 | | PAGELATCH_UP | 0.09 s | 2 | | PAGEIOLATCH_EX | 0.07 s | 4 | 

4.55s d'une exécution 6s dans IO_COMPLETION :

Se produit en attendant que les opérations d'E / S se terminent. Ce type d'attente représente généralement les E / S de pages autres que datatables. Les attentes d'achèvement d'E / S de la page de données s'affichent au moment où PAGEIOLATCH_ * attend.

Indices non redondants, dites-vous?

 | Index Name | Columns | Index Entry Size | |---------------------------------------|------------------------------------------|--------------------------| | IX_AuditLog_ChangeDate | ChangeDate | 12 bytes per entry | | IX_AuditLog_UserGUID | ChangeDate, UserGUID | 28 bytes per entry | | IX_AuditLog_FieldName | FieldName | 4 bytes per entry (avg) | | IX_AuditLog_TableName | TableName | 13 bytes per entry (avg) | | IX_AuditLog_LastRowActionByTable | TableName, ChangeType, RowGUID, UserGUID | 52 bytes per entry (avg) | | IX_AuditLog_RowGUID | RowGUID | 20 bytes per entry | | IX_AuditLog_RowLastModifiedByUserGUID | RowGUID, ChangeDate, UserGUID | 44 bytes per entry | | IX_AuditLog_RowInsertedByUserGUID | ChangeType, RowGUID, UserGUID | 43 bytes per entry (avg) | | IX_AuditLog_TagGUID | TagGUID, RowGUID | 36 bytes per entry | 

Aucune alerte de sorting

Résultats SQL Server Profiler pour le lot

• Durée : 7 401 ms
• Lit : 233 597
• Écrit : 17 077
• CPU : 1,141 ms

Aucun avertissement de type. Il n'y a pas non plus d' attention , d' avertissement de bitmap, d'avertissement d' exécution, d'avertissement de hachage , de statistics de colonne manquantes , de prédicat de jointure manquant , d' avertissement de sorting , de message d'erreur d'user .

Les index ont tous été reconstruits. Toutes les statistics ont été mises à jour.

• Optimisation: O WH x IN (1, 2 .., 100.000) vs INNER JOIN tmp_table UTILISATION (x)?
• Optimiser le SQL généré par LINQ Query dans Entity Framework 4.1 avec des associations un-à-plusieurs
• Optimisation de l'instruction de cas T-SQL Where Clause (parameters facultatifs à StoredProc)
• Optimisation des plans d'exécution pour les requêtes T-SQL paramétrées contenant des fonctions de window