sql-server - 索引插入期间的多个表假脱机(Eager 假脱机)

Question

我正在执行数百行的简单插入，例如:

INSERT INTO Foo
SELECT * FROM Bar

该表有一些二级索引。禁用这些索引后，查询几乎立即运行。启用二级索引后，查询耗时数秒，子树成本较高。

问题是对于每个二级索引，数据库执行:

• 物理操作:Table spool
• 逻辑运算:Lazy spool

缓存位置:

• 目标表中的所有列(当它只需要它需要的值时)
• 多次值(而不是一次)

虽然了解为什么 SQL Server (2008 R2 SP2) 认为它需要这样做可能很有趣，但我真正需要的是一种方法，使在实时服务器中插入 100 行不需要花费太多时间六秒。

真正非常可怕的是，对于每个表假脱机，SQL Server 都会每次缓存每个列的值:

这只是燃烧逻辑IO。

• 如果没有这些有问题的索引更新，一两秒内即可完成 60,000 行的导入
• 使用这些索引，完成导入实际上需要几十分钟

重现步骤

当然，我的真实 AuditLog 表包含 4M 行。但是我们可以使用空的 AuditLog 表来重现完全相同的运算符，但子树成本很高:

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)
)

我们有痛苦的索引:

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]

然后我们创建插入内容:

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

您询问的等待时间？

| 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 |

IO_COMPLETION 中 6 秒执行的 4.55 秒:

Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits.

你说的是非冗余索引？

| 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       |

无排序警告

批处理的 SQL Server Profiler 结果

• 持续时间:7,401 毫秒
• 阅读量:233,597
• 写入:17,077
• CPU:1,141 毫秒

没有排序警告。也没有任何注意、位图警告、执行警告、哈希警告、缺少列统计信息、缺少连接谓词、排序警告、用户错误消息。

索引全部重建。所有统计数据均已更新。

Answer 1

您遇到重叠和冗余索引的问题。

此查询将有帮助:T-SQL for finding Redundant Indexes