主页 / dba / 问题 / 171901
Accepted
Stijn Wynants
Asked: 2017-04-26 01:10:20 +0800 CST

线上线下索引重建

  • 772

我今天有一个奇怪的案例。

在一个数据库中，我有大约 50 个（1000 多页，其中大多数 50000 多页）表，即使我正在使用 Ola 的脚本运行每晚重建，它们的索引碎片化率超过 70%。

今天进一步调查时，我注意到当我使用online = on选项重建索引时，索引将保持相同数量的碎片。

然而，索引确实建立了，因为我可以看到我的系统正在这样做，但是当它被切换时，它仍然有相同数量的碎片。

在网上搜索了一下，可能是我的数据文件空间不够，所以我扩展了我的数据文件，但没有结果。

然而，当在没有在线选项的情况下进行重建时，我的索引碎片消失了。

这意味着在我的情况下，在线重建不会消除碎片，而离线重建会。

它在企业版上运行。有谁知道为什么会发生这种行为？

表格代码

CREATE TABLE [dbo].[TheTable](
    [BLA1] [decimal](22, 6) NULL,
    [BLA2] [smallint] NULL,
    [BLA3] [datetime] NULL,
    [BLA4] [varchar](5) NULL,
    [BLA5] [int] NULL,
    [BLA6] [varchar](50) NULL,
    [BLA7] [varchar](15) NULL,
    [BLA8] [decimal](22, 6) NULL,
    [BLA9] [decimal](22, 6) NULL,
    [BLA10] [decimal](22, 6) NULL,
    [BLA11] [int] NULL,
    [BLA12] [decimal](22, 6) NULL,
    [BLA13] [decimal](22, 6) NULL,
    [BLA14] [varchar](15) NULL,
    [BLA15] [varchar](12) NULL,
    [BLA16] [varchar](5) NULL,
    [BLA17] [varchar](6) NULL,
    [BLA18] [int] NULL,
    [BLA19] [decimal](22, 6) NULL,
    [BLA20] [varchar](1) NULL,
    [BLA21] [int] IDENTITY(1,1) NOT NULL,
    [BLA22] [varchar](100) NULL,
    [BLA23] [smallint] NULL,
    [BLA24] [datetime] NULL,
    [BLA25] [varchar](8) NULL,
    [BLA26] [varchar](6) NULL,
    [BLA27] [varchar](12) NULL,
    [BLA28] [varchar](3) NULL,
    [BLA29] [int] NULL,
    [BLA30] [int] NULL,
    [BLA31] [int] NULL,
    [BLA32] [varchar](11) NULL,
    [BLA33] [smallint] NULL,
    [BLA34] [smallint] NULL,
    [BLA35] [varchar](12) NULL,
    [BLA36] [smallint] NULL,
    [BLA37] [smallint] NULL,
    [BLA38] [varchar](10) NULL,
    [BLA39] [varchar](30) NULL,
    [BLA40] [varchar](25) NULL,
    [BLA41] [smallint] NULL,
    [BLA42] [smallint] NULL,
    [BLA43] [varchar](6) NULL,
    [BLA44] [varchar](15) NULL,
    [BLA45] [int] NULL,
    [BLA46] [decimal](22, 6) NULL,
    [BLA47] [uniqueidentifier] NULL,
    [BLA48] [uniqueidentifier] NULL,
    [BLA49] [nvarchar](50) NULL,
    [BLA50] [nvarchar](20) NULL
) ON [PRIMARY]


GO
CREATE NONCLUSTERED INDEX [XKBLA] ON [dbo].[TheTable]
(
    [bla4] ASC,
    [bla5] ASC,
    [bla16] ASC
)
INCLUDE (   [bla7],
    [bla9],
    [bla24]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
GO

插入示例记录

INSERT INTO [dbo].[TheTable]
           ([BLA1],[BLA2],[BLA3],[BLA4],[BLA5],[BLA6],[BLA7],[BLA8],[BLA9],[BLA10],[BLA11],[BLA12],[BLA13],[BLA14],[BLA15],[BLA16],[BLA17]
            ,[BLA18],[BLA19],[BLA20],[BLA22],[BLA23],[BLA24],[BLA25],[BLA26],[BLA27],[BLA28],[BLA29],[BLA30],[BLA31],[BLA32],[BLA33],[BLA34]
            ,[BLA35],[BLA36],[BLA37],[BLA38],[BLA39],[BLA40],[BLA41],[BLA42],[BLA43],[BLA44],[BLA45],[BLA46],[BLA47],[BLA48],[BLA49],[BLA50])
     VALUES
           (71002614000.000000,62,'2005-12-14 16:40:46.763','Bla',708512,'Bla','BLA',15.000000,-3.000000,12.000000,1,0.851000,0.060000,'BLA',0122863484,
            00003,02182,NULL,NULL,'E',1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
            NULL,NULL,NULL,NULL,NULL)
GO

更多信息

这是重建索引时看到的行为：

在此处输入图像描述

然后我在线重建索引：

在此处输入图像描述

然后它给了我以下碎片：

在此处输入图像描述

当我重建索引时Maxdop=1：

在此处输入图像描述

然后碎片就没有了！

在此处输入图像描述

如果我然后用 maxdop > 0 再次重建它：

在此处输入图像描述

碎片又回来了。

填充因子 0 用于这些索引。这些是供应商定义的。表中没有 LOB 类型。表上没有聚簇索引。即使当我进行手动重建时，它仍然在非聚集索引上存在碎片，只有离线重建才能清除它。

sql-server sql-server-2012

2 个回答

  1. Best Answer

    问题是由于maxdop> 1 和allow_page_locks = OFF

    这个问题是关于这里的博客（正如Shanky在评论中最初提到的那样）：

    工作原理：联机索引重建 – 可能导致碎片增加Bob Dorr – 首席 SQL Server 升级工程师，Microsoft 客户服务和支持。

    ...并且被称为蛙跳效应。

    • 3

  2. 如果您在 Ola 的 Index Optimize 脚本中向下滚动到他设置的位置@CurrentCommand02，您将在代码中看到：

    坚果

    索引类型 0 是 HEAP。

    但是有充分的理由不这样做。重建 HEAP 时，也会重建所有非聚集索引。比较STATISTICS TIME, IO输出：

    CREATE TABLE #t1 (c1 UNIQUEIDENTIFIER DEFAULT NEWID())

INSERT #t1 ( c1 )
DEFAULT VALUES
GO 10000

SET STATISTICS TIME, IO ON

ALTER TABLE #t1 REBUILD

CREATE NONCLUSTERED INDEX ix_t1_1 ON #t1 (c1)

CREATE NONCLUSTERED INDEX ix_t1_2 ON #t1 (c1)

CREATE NONCLUSTERED INDEX ix_t1_3 ON #t1 (c1)

CREATE NONCLUSTERED INDEX ix_t1_4 ON #t1 (c1)

CREATE NONCLUSTERED INDEX ix_t1_5 ON #t1 (c1)

ALTER TABLE #t1 REBUILD

    只是堆：

    Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.

    有索引：

    Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#t1'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 42 ms.

    当然，我会敦促您停止对索引碎片的痴迷，并解决索引的实际问题。例如，HEAP 可能导致转发提取，并且在您从中删除数据时可能不会释放页面。乐趣！

    • 1

相关问题