Início / dba / Perguntas / 34744
Accepted
paparazzo
Asked: 2013-02-14 13:21:18 +0800 CST

Fragmentação PK na coluna INT IDENTITY

  • 772

Tenha simples Int Identitycomo meu PK.

A tabela tem 2,2 milhões de linhas.

Zero linha excluída, nunca desativou a identidade.

As inserções eram via .NET, uma linha por vez, com cada inserção recuperando o arquivo SCOPE_IDENTITY().

20 algumas relações FK para ele.

Tenho fragmentação de 98%.

Alguma ideia do que pode ter causado isso?

Eu sei que a solução é reconstruir e farei isso esta noite.

Eu estou querendo saber como evitá-lo.

Eu adicionei 3 colunas desde que foi preenchida.
Algumas das colunas têm quase todos os valores revisados.
O comprimento das propriedades não mudou muito.

Definição de tabela 

/****** Object:  Table [dbo].[docSVsys]    Script Date: 02/13/2013 15:52:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[docSVsys](
    [sID] [int] IDENTITY(1,1) NOT NULL,
    [sParID] [int] NULL,
    [docID] [varchar](100) NOT NULL,
    [attBeg] [varchar](100) NULL,
    [attEnd] [varchar](100) NULL,
    [docDate] [smalldatetime] NULL,
    [addDate] [smalldatetime] NOT NULL,
    [mimeType] [varchar](40) NULL,
    [docIDpar] [varchar](50) NULL,
    [addBy] [smallint] NOT NULL,
    [IPROlink] [varchar](300) NULL,
    [textSize] [int] NULL,
    [textHash] [char](32) NULL,
    [FTSstatus] [tinyint] NOT NULL,
    [FTSdate] [smalldatetime] NULL,
    [nativeFileName] [varchar](200) NULL,
    [nativeFileSize] [bigint] NULL,
    [nativeMD5] [char](32) NULL,
    [nativeUNC] [varchar](600) NULL,
    [nativeDateCreate] [smalldatetime] NULL,
    [nativeDateModify] [smalldatetime] NULL,
    [nativeExtension] [varchar](20) NULL,
    [caseID] [char](1) NULL,
    [textUniqueWordCount] [int] NULL,
    [nativeUNCrendition] [varchar](300) NULL,
    [nativeXPS] [varchar](300) NULL,
    [FTSnumNearDup] [int] NULL,
    [FTSnearDupSIDcenter] [int] NULL,
    [FTSnearDupMatchToCenter] [decimal](8, 2) NULL,
    [FTSnearDupID] [int] NULL,
 CONSTRAINT [PK_docSVsys] PRIMARY KEY CLUSTERED 
(
    [sID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[docSVsys]  WITH CHECK ADD  CONSTRAINT [FK_docSVsys_docSVsys_sParID] FOREIGN KEY([sParID])
REFERENCES [dbo].[docSVsys] ([sID])
GO

ALTER TABLE [dbo].[docSVsys] CHECK CONSTRAINT [FK_docSVsys_docSVsys_sParID]
GO

ALTER TABLE [dbo].[docSVsys] ADD  CONSTRAINT [DF_docSVsys_addDate]  DEFAULT (getdate()) FOR [addDate]
GO


Table Name                                                                                                                       Index name                                                                                                                       alloc_unit_type_desc                                         index_depth index_level avg_fragmentation_in_percent
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ----------- ----------- ----------------------------
docSVsys                                                                                                                         PK_docSVsys                                                                                                                      IN_ROW_DATA                                                  4           0           0.01
docSVsys                                                                                                                         PK_docSVsys                                                                                                                      IN_ROW_DATA                                                  4           1           0
docSVsys                                                                                                                         PK_docSVsys                                                                                                                      IN_ROW_DATA                                                  4           2           0
docSVsys                                                                                                                         PK_docSVsys                                                                                                                      IN_ROW_DATA                                                  4           3           0
sql-server sql-server-2008

1 respostas

  1. Best Answer

    Uma chave primária em a INT IDENTITYdeve estar muito próxima do ideal e, como tal, não deve levar a uma fragmentação significativa do índice.

    No entanto: como seu PK é (por padrão e a menos que você o altere especificamente) também seu índice de clustering e o nível folha do índice de clustering são as páginas de dados reais.

    Se suas estruturas de dados mudaram significativamente ao longo do tempo, novas colunas foram adicionadas, outras possivelmente descartadas, o comprimento das colunas baseadas em string mudou - isso pode levar a uma fragmentação significativa do índice (no nível da folha), pois as páginas precisam ser reorganizadas para liberar espaço para novas colunas de dados.

    Além disso: se você tiver um bom número de colunas de comprimento variávelvarchar(x) ( ) e essas tiverem sido atualizadas, se o comprimento da varcharcoluna aumentar, isso pode levar a divisões de página. Isso é especialmente verdadeiro se você tiver FILLFACTOR100% em seu índice PK - nesse caso, mesmo um único caractere extra pode levar a uma divisão de página - uma página é dividida em duas, os dados distribuídos entre as duas novas páginas e isso contribui para indexar a fragmentação.

    Então, diante de tudo isso, considere:

    • mesmo ter um plano de manutenção para um índice perfeito não é uma má ideia INT IDENTITY
    • se você tiver muitas varcharcolunas e elas puderem crescer com o tempo (o texto fica cada vez mais longo) - considere um FILLFACTORvalor inferior a 100% (o padrão)

    PS: se você ainda tiver sua situação antes da reorganização - tente esta consulta (e coloque o nome da tabela como o segundo parâmetro para dm_db_index_physical_stats) - isso mostrará a fragmentação do índice no índice PK, em todos os níveis do índice:

    SELECT 
    'Table Name' = t.NAME,
    'Index name' = i.NAME ,
    ips.alloc_unit_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.PutYourTableNameHere'), NULL, NULL, 'DETAILED') ips
INNER JOIN  
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN  
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    i.index_id = 1
    • 8

relate perguntas