主页 / dba / 问题 / 34744
Accepted
paparazzo
Asked: 2013-02-14 13:21:18 +0800 CST

INT IDENTITY 列上的 PK 碎片

  • 772

有简单Int Identity的作为我的PK。

表有 220 万行。

零行已删除，从未禁用身份。

一次通过 .NET 插入一行，每个插入检索 .NET 文件SCOPE_IDENTITY()。

20 一些 FK 关系。

我有 98% 的碎片。

知道是什么原因造成的吗？

我知道解决办法是重建，今晚就会这样做。

我想知道如何避免它。

自填充以来，我添加了 3 列。
一些专栏几乎修改了每个值。
属性的长度没有太大变化。

表定义 

/****** 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 个回答

  1. Best Answer

    a 上的主键INT IDENTITY应该非常接近最优，因此不应导致明显的索引碎片。

    但是：由于您的 PK（默认情况下，除非您专门更改它）也是您的clustering index，并且 clustering index 的叶级是实际的数据页。

    如果您的数据结构随时间发生了显着变化，添加了新列，可能删除了其他列，基于字符串的列的长度发生了变化——这可能会导致严重的索引碎片（在叶级），因为需要重新排列页面以腾出空间对于新的数据列。

    另外：如果您有大量可变长度列 ( varchar(x)) 并且这些列已更新，如果varchar列的长度增加，这可能会导致页面拆分。如果您FILLFACTOR的 PK 索引为 100%，则尤其如此 - 在这种情况下，即使是一个额外的字符也可能导致页面拆分 - 一个页面被拆分为两个，数据分布在两个新页面之间，这有助于索引碎片。

    因此，鉴于这一切，请考虑：

    • 即使有一个完美 INT IDENTITY索引的维护计划也不是一个坏主意
    • 如果您有很多varchar列，并且它们很可能会随着时间的推移而增长（文本变得越来越长）- 考虑FILLFACTOR低于 100%（默认值）

    PS：如果您在重组之前仍然遇到这种情况- 试试这个查询（并将您的表名作为第二个参数dm_db_index_physical_stats） - 这将显示 PK 索引上的索引碎片，在索引的每个级别上：

    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

相关问题