SQL Server ->> DISABLE索引后插入更新数据再REBUILD索引 和 保留索引直接插入更新数据的性能差异

之前对于“DISABLE索引后插入更新数据再REBUILD索引 和 保留索引直接插入更新数据的性能差异”这两种方法一直认为其实应该差不多，因为无论如何索引最后都需要被维护，只不过是个时间顺序先后的问题，结果今天做了一个实验之后大吃一惊，原来这种“DISABLE索引后插入更新数据再REBUILD索引”确实可以在性能上取得不错的提升。当然了，前提假设是数据插入量达到一个级别，比如我的例子就是用了100万行的数据插入。感觉对于两者底层下SQL Server数据库引擎到底干了那些活确实还需要找时间找些技术丛书来研究下。

 

我的实验环境是：Microsoft SQL Server 2012 Enterprise Edition

下面session 1用了41秒，而session 2用了19秒

 

USE [Test]
GO

/****** Object:  Table [dbo].[Table_1]    Script Date: 9/16/2015 8:39:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_1](
    [col1] [int] NOT NULL,
    [col2] [datetime] NOT NULL,
    [col3] [nvarchar](100) NULL,
    [col4] [float] NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object:  Index [NonClusteredIndex-20150916-152116]    Script Date: 9/16/2015 8:39:07 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152116] ON [dbo].[Table_1]
(
    [col1] 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) ON [PRIMARY]
GO

/****** Object:  Index [NonClusteredIndex-20150916-152132]    Script Date: 9/16/2015 8:39:07 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152132] ON [dbo].[Table_1]
(
    [col2] 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) ON [PRIMARY]
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [NonClusteredIndex-20150916-152140]    Script Date: 9/16/2015 8:39:07 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152140] ON [dbo].[Table_1]
(
    [col3] 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) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [DF_Table_1_col2]  DEFAULT (getdate()) FOR [col2]
GO

/****** Object:  Table [dbo].[Table_2]    Script Date: 9/16/2015 8:39:23 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_2](
    [col1] [int] NOT NULL,
    [col2] [datetime] NOT NULL CONSTRAINT [DF_Table_2_col2]  DEFAULT (getdate()),
    [col3] [nvarchar](100) NULL,
    [col4] [float] NULL,
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED 
(
    [col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object:  Index [NonClusteredIndex-20150916-152202]    Script Date: 9/16/2015 8:39:23 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152202] ON [dbo].[Table_2]
(
    [col1] 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) ON [PRIMARY]
GO

ALTER INDEX [NonClusteredIndex-20150916-152202] ON [dbo].[Table_2] DISABLE
GO

/****** Object:  Index [NonClusteredIndex-20150916-152210]    Script Date: 9/16/2015 8:39:23 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152210] ON [dbo].[Table_2]
(
    [col2] 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) ON [PRIMARY]
GO

ALTER INDEX [NonClusteredIndex-20150916-152210] ON [dbo].[Table_2] DISABLE
GO

SET ANSI_PADDING ON

GO

/****** Object:  Index [NonClusteredIndex-20150916-152218]    Script Date: 9/16/2015 8:39:23 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152218] ON [dbo].[Table_2]
(
    [col3] 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) ON [PRIMARY]
GO

ALTER INDEX [NonClusteredIndex-20150916-152218] ON [dbo].[Table_2] DISABLE
GO



--SESSION 1

insert [dbo].[Table_1](col1, col3, col4)
select [ID], REPLICATE('A',80), 123456.789
from [dbo].[Numbers]


--SESSION 2
ALTER INDEX [NonClusteredIndex-20150916-152202] ON [dbo].[Table_2] DISABLE
ALTER INDEX [NonClusteredIndex-20150916-152210] ON [dbo].[Table_2] DISABLE
ALTER INDEX [NonClusteredIndex-20150916-152218] ON [dbo].[Table_2] DISABLE

insert [dbo].[Table_2](col1, col3, col4)
select [ID], REPLICATE('A',80), 123456.789
from [dbo].[Numbers]

ALTER INDEX [NonClusteredIndex-20150916-152202] ON [dbo].[Table_2] REBUILD
ALTER INDEX [NonClusteredIndex-20150916-152210] ON [dbo].[Table_2] REBUILD
ALTER INDEX [NonClusteredIndex-20150916-152218] ON [dbo].[Table_2] REBUILD