logo标签列表

更改现有表的聚集键 - SQL Server 2008

sql-serversql-server-2008guidclustering-key
3
我被分配了一个任务，需要提高表格查询性能。
主键是由应用程序代码创建的GUID，因此不是顺序的，并且表格上没有单独的顺序聚集键。
我认为选择非顺序的GUID作为主键和聚集键是性能不佳的主要原因。我打算删除GUID上的聚集索引，并添加INT IDENTITY作为聚集键。
该表格中有约3百万行数据。
更改表格还是创建新表格、复制现有数据、删除旧表格并重命名新表格，哪种方法更好？
编辑：复制3百万行数据需要很长时间。删除索引会更快吗？
编辑2：决定通过硬件解决缓慢的复制问题，将20个核心投入其中，而不是4个。现在速度快得多，但仍比我预期的慢得多。我估计复制3百万行需要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%的计划用于在新标识列上聚集索引插入操作

1
你是如何复制数据的？使用 INSERT INTO SELECT 吗？ - ughai
1
我认为如果你没有对SELECT进行排序并且没有自动增长，那么INSERT INTO SELECT应该更快。这个表中是否有任何DML查询在后台运行？请确认没有阻塞。 - ughai
1
我的意思是，如果你的INSERT INTO SELECT中有ORDER BY语句？你能否发布你正在使用的查询？ - ughai
1
这是在我的本地机器上运行以进行测试。目前没有做其他任何事情。选择语句中没有排序。源表有18GB的数据，所以我希望它需要一段时间，但不会持续一整天。自动增长设置为每次增加1MB，无限制。使用SELECT INTO FROM会更快吗？ - MrBliz
1
300万行对于SQL Server来说相对较小。你应该尝试直接从SSMS运行那些需要很长时间的查询，并查看查询计划。这将为你提供一些起点数据。 - Zohar Peled
显示剩余10条评论
1个回答
0
根据我的经验，最快的方法是删除现有的聚集索引。
drop index index_name on tablename;

然后重新创建聚集索引：

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

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

由于聚集索引位于标识列上，因此页面分割不会成为问题。这也意味着在插入数据后创建聚集索引是没有意义的，最好先创建聚集索引，然后只需写入数据两次即可。 - GarethD
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接