Início / dba / Perguntas / 171901
Accepted
Stijn Wynants
Asked: 2017-04-26 01:10:20 +0800 CST

Reconstrução de índice online e offline

  • 772

Tive um caso estranho hoje.

Dentro de um banco de dados, eu tinha cerca de 50 (mais de 1.000 páginas, a maioria com mais de 50.000 páginas) tabelas que tinham seus índices fragmentados acima de 70%, embora eu estivesse executando uma reconstrução noturna usando os scripts do Ola.

Ao investigar mais hoje, notei que quando reconstruí o índice com a online = onopção, o índice manteria a mesma quantidade de fragmentação.

No entanto, o índice é construído, pois posso ver meu sistema fazendo isso, mas quando ele é alternado, ainda tem a mesma quantidade de fragmentação.

Depois de pesquisar na internet, houve um possível motivo de eu não ter espaço suficiente no meu arquivo de dados, então estendi meu arquivo de dados sem nenhum resultado.

Então, no entanto, ao fazer uma reconstrução sem a opção online, minha fragmentação de índice desapareceu.

Isso significa que, no meu caso, uma reconstrução online não removerá a fragmentação, e a offline sim.

Ele está sendo executado na Enterprise Edition. Alguém tem ideia do porque esse comportamento ocorre?

Código da tabela

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

Inserindo um Registro de Amostra

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

Mais Informações

Este é o comportamento visto ao reconstruir o índice:

insira a descrição da imagem aqui

Eu então reconstruo o índice online:

insira a descrição da imagem aqui

Então me dá a seguinte fragmentação:

insira a descrição da imagem aqui

Quando eu reconstruo o índice com Maxdop=1:

insira a descrição da imagem aqui

Então a fragmentação se foi!

insira a descrição da imagem aqui

Se eu reconstruí-lo novamente com maxdop > 0:

insira a descrição da imagem aqui

A fragmentação está de volta novamente.

O fator de preenchimento 0 está sendo usado para esses índices. Estes são definidos pelo fornecedor. Não há tipos de LOB na tabela. Não há índice clusterizado na tabela. Mesmo quando eu faço uma reconstrução manual, ele ainda mantém a fragmentação no índice não clusterizado, apenas uma reconstrução offline a limpa.

sql-server sql-server-2012

2 respostas

  1. Best Answer

    O problema foi devido ao maxdop> 1 e oallow_page_locks = OFF

    A questão é comentada aqui (como originalmente mencionado por Shanky em um comentário):

    Como Funciona: Reconstrução de Índice Online – Pode Causar Maior Fragmentação por Bob Dorr – Engenheiro Principal de Escalação do SQL Server, Atendimento e Suporte ao Cliente Microsoft.

    ...e é chamado de efeito de salto de sapo.

    • 3

  2. Se você rolar para baixo no script Index Optimize do Ola para onde ele define @CurrentCommand02, você verá isso no código:

    Nozes

    O tipo de índice 0 é HEAPs.

    Mas há uma boa razão para não fazer isso. Ao reconstruir um HEAP, você também recria todos os índices não clusterizados. Compare a STATISTICS TIME, IOsaída:

    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

    Apenas o HEAP:

    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.

    Com índices:

    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.

    Claro, eu recomendo que você pare de ficar obcecado com a fragmentação de índice e corrija problemas reais com seus índices. Por exemplo, HEAPs podem causar buscas encaminhadas e podem não liberar páginas quando você exclui dados delas. Diversão!

    • 1

relate perguntas