sql-server - 更改现有表上的聚簇键 - SQL Server 2008

Question

我接到了一项提高表查询性能的任务。

主键是由应用程序代码创建的 GUID，因此不是顺序的，表上没有单独的顺序集群键。

我的感觉是，选择非顺序 GUID 作为主键和集群键是导致性能不佳的主要原因。我打算在 GUID 上删除聚簇索引并添加一个 INT IDENTITY 作为聚簇键。

该表中有大约 300 万行。

是尝试更改表，还是创建一个新表，将现有数据复制到其中，删除旧表，然后重命名新表更好？

编辑:复制 300 万行需要非常的时间。删除索引会更快吗？

编辑 2:决定用硬件解决复制速度慢的问题，并将 4 个内核改为 20 个内核。现在速度快了很多，但仍然比我预期的要慢得多。我估计复制 300 万行需要 30 分钟。

虽然这只是一个测试，但我仍然希望有一个解决方案，我仍然必须在生产服务器上进行，我宁愿不要让它停机超过必要的时间。

有关信息，根据@ughai 的建议，我的自动增长设置现在为 500Mb。

这种事情真的不是我的专长，所以我希望能提供一些关于什么是解决这个问题的最佳方法的建议。

如果相关，则在此表上发生的大部分查询都没有连接。

编辑:原始表模式

 CREATE TABLE [dbo].[IODBTaskHistory](
    [Id] [uniqueidentifier] NOT NULL,
    [Tag] [nvarchar](250) NULL,
    [Type] [int] NOT NULL,
    [SourceFilePath] [nvarchar](max) NOT NULL,
    [DestinationFilePath] [nvarchar](max) NULL,
    [Priority] [int] NOT NULL,
    [State] [int] NOT NULL,
    [SubState] [int] NOT NULL,
    [StateDescription] [nvarchar](max) NULL,
    [Progress] [decimal](5, 2) NOT NULL,
    [Date_Created] [datetime] NOT NULL,
    [Date_Queued] [datetime] NULL,
    [Date_Started] [datetime] NULL,
    [Date_Finished] [datetime] NULL,
    [Date_LastUpdated] [datetime] NULL,
    [Optional_ParentDependancyTaskId] [uniqueidentifier] NULL,
    [Optional_isParentSuccessRequired] [bit] NULL,
    [Transfer_ProgressBytes] [float] NULL,
    [Transfer_SpeedCurrentBps] [float] NULL,
    [Transfer_SpeedIntervals] [nvarchar](max) NULL,
    [IODrone_Id] [uniqueidentifier] NULL,
    [IODrone_Version] [nvarchar](max) NULL,
    [Action] [int] NOT NULL,
    [Date_TransferStarted] [datetime] NULL,
    [Optional_NotificationEmails] [nvarchar](max) NULL,
    [MaxRetryCount] [int] NULL,
    [CurrentRetryCount] [int] NULL,
    [Impersonation_Username] [nvarchar](200) NOT NULL,
    [Impersonation_Password] [nvarchar](max) NOT NULL,
    [AllowRewrite] [bit] NOT NULL CONSTRAINT [DF_IODBTaskHistory_AllowRewrite]  DEFAULT ((0)),
    [SubTag] [nvarchar](255) NULL,
    [SourceLengthBytes] [bigint] NULL CONSTRAINT [DF_IODBTaskHistory_SourceLengthBytes2]  DEFAULT ((0)),
    [IODrone_Thread] [int] NULL,
    [Date_FileSizeFetched] [datetime] NULL,
    [Date_StornextTapeRetrievalStarted] [datetime] NULL,
    [Date_StornextTapeRetrievalFinished] [datetime] NULL,
    [IOServiceAddress] [nvarchar](20) NULL,
    [LogString] [nvarchar](max) NULL,
    [NotesString] [nvarchar](max) NULL,
    [TX_Date] [datetime] NULL,
    [SlowDownUpload] [bit] NULL CONSTRAINT [DF_IODBTaskHistory_SlowDownUpload]  DEFAULT ((0)),
 CONSTRAINT [PK_IODBTaskHistory] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

目标表架构

CREATE TABLE [dbo].[IODBTaskHistoryNew](
    [Id] [uniqueidentifier] NOT NULL,
    [ClusterKey] [int] IDENTITY(1,1) NOT NULL,
    [Tag] [nvarchar](250) NULL,
    [Type] [int] NOT NULL,
    [SourceFilePath] [nvarchar](max) NOT NULL,
    [DestinationFilePath] [nvarchar](max) NULL,
    [Priority] [int] NOT NULL,
    [State] [int] NOT NULL,
    [SubState] [int] NOT NULL,
    [StateDescription] [nvarchar](max) NULL,
    [Progress] [decimal](5, 2) NOT NULL,
    [Date_Created] [datetime] NOT NULL,
    [Date_Queued] [datetime] NULL,
    [Date_Started] [datetime] NULL,
    [Date_Finished] [datetime] NULL,
    [Date_LastUpdated] [datetime] NULL,
    [Optional_ParentDependancyTaskId] [uniqueidentifier] NULL,
    [Optional_isParentSuccessRequired] [bit] NULL,
    [Transfer_ProgressBytes] [float] NULL,
    [Transfer_SpeedCurrentBps] [float] NULL,
    [Transfer_SpeedIntervals] [nvarchar](max) NULL,
    [IODrone_Id] [uniqueidentifier] NULL,
    [IODrone_Version] [nvarchar](max) NULL,
    [Action] [int] NOT NULL,
    [Date_TransferStarted] [datetime] NULL,
    [Optional_NotificationEmails] [nvarchar](max) NULL,
    [MaxRetryCount] [int] NULL,
    [CurrentRetryCount] [int] NULL,
    [Impersonation_Username] [nvarchar](200) NOT NULL,
    [Impersonation_Password] [nvarchar](max) NOT NULL,
    [AllowRewrite] [bit] NOT NULL,
    [SubTag] [nvarchar](255) NULL,
    [SourceLengthBytes] [bigint] NULL,
    [IODrone_Thread] [int] NULL,
    [Date_FileSizeFetched] [datetime] NULL,
    [Date_StornextTapeRetrievalStarted] [datetime] NULL,
    [Date_StornextTapeRetrievalFinished] [datetime] NULL,
    [IOServiceAddress] [nvarchar](20) NULL,
    [LogString] [nvarchar](max) NULL,
    [NotesString] [nvarchar](max) NULL,
    [TX_Date] [datetime] NULL,
    [SlowDownUpload] [bit] NULL,
PRIMARY KEY NONCLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE CLUSTERED 
(
    [ClusterKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[IODBTaskHistoryNew] ADD  CONSTRAINT [DF_IODBTaskHistory_AllowRewriteNew]  DEFAULT ((0)) FOR [AllowRewrite]
GO

ALTER TABLE [dbo].[IODBTaskHistoryNew] ADD  CONSTRAINT [DF_IODBTaskHistory_SourceLengthBytes2New]  DEFAULT ((0)) FOR [SourceLengthBytes]
GO

ALTER TABLE [dbo].[IODBTaskHistoryNew] ADD  CONSTRAINT [DF_IODBTaskHistory_SlowDownUploadNew]  DEFAULT ((0)) FOR [SlowDownUpload]
GO

我的复制查询

    INSERT INTO [dbo].[IODBTaskHistoryNew]
           ([Id]
           ,[Tag]
           ,[Type]
           ,[SourceFilePath]
           ,[DestinationFilePath]
           ,[Priority]
           ,[State]
           ,[SubState]
           ,[StateDescription]
           ,[Progress]
           ,[Date_Created]
           ,[Date_Queued]
           ,[Date_Started]
           ,[Date_Finished]
           ,[Date_LastUpdated]
           ,[Optional_ParentDependancyTaskId]
           ,[Optional_isParentSuccessRequired]
           ,[Transfer_ProgressBytes]
           ,[Transfer_SpeedCurrentBps]
           ,[Transfer_SpeedIntervals]
           ,[IODrone_Id]
           ,[IODrone_Version]
           ,[Action]
           ,[Date_TransferStarted]
           ,[Optional_NotificationEmails]
           ,[MaxRetryCount]
           ,[CurrentRetryCount]
           ,[Impersonation_Username]
           ,[Impersonation_Password]
           ,[AllowRewrite]
           ,[SubTag]
           ,[SourceLengthBytes]
           ,[IODrone_Thread]
           ,[Date_FileSizeFetched]
           ,[Date_StornextTapeRetrievalStarted]
           ,[Date_StornextTapeRetrievalFinished]
           ,[IOServiceAddress]
           ,[LogString]
           ,[NotesString]
           ,[TX_Date]
           ,[SlowDownUpload])
    SELECT [Id]
      ,[Tag]
      ,[Type]
      ,[SourceFilePath]
      ,[DestinationFilePath]
      ,[Priority]
      ,[State]
      ,[SubState]
      ,[StateDescription]
      ,[Progress]
      ,[Date_Created]
      ,[Date_Queued]
      ,[Date_Started]
      ,[Date_Finished]
      ,[Date_LastUpdated]
      ,[Optional_ParentDependancyTaskId]
      ,[Optional_isParentSuccessRequired]
      ,[Transfer_ProgressBytes]
      ,[Transfer_SpeedCurrentBps]
      ,[Transfer_SpeedIntervals]
      ,[IODrone_Id]
      ,[IODrone_Version]
      ,[Action]
      ,[Date_TransferStarted]
      ,[Optional_NotificationEmails]
      ,[MaxRetryCount]
      ,[CurrentRetryCount]
      ,[Impersonation_Username]
      ,[Impersonation_Password]
      ,[AllowRewrite]
      ,[SubTag]
      ,[SourceLengthBytes]
      ,[IODrone_Thread]
      ,[Date_FileSizeFetched]
      ,[Date_StornextTapeRetrievalStarted]
      ,[Date_StornextTapeRetrievalFinished]
      ,[IOServiceAddress]
      ,[LogString]
      ,[NotesString]
      ,[TX_Date]
      ,[SlowDownUpload]
  FROM [dbo].[IODBTaskHistory]

执行计划

如果从图片上看不清楚 99% 的计划都花在了新标识列上的聚簇索引插入上

Answer 1

根据我的经验，最快的方法是删除现有的聚集索引

drop index index_name on tablename;

然后重新创建聚簇索引:

create clustered index indexname on tablename(columnname1, columnanme2);

如果要复制数据，则复制到不包含任何索引的目标表中。完成所有插入后，首先创建聚集索引，然后创建所有其他索引。如果您在插入之前创建索引，您可能会有很多很多页面拆分，这可能需要很长时间。