我被分配了一个任务,需要提高表格查询性能。
主键是由应用程序代码创建的GUID,因此不是顺序的,并且表格上没有单独的顺序聚集键。
我认为选择非顺序的GUID作为主键和聚集键是性能不佳的主要原因。我打算删除GUID上的聚集索引,并添加INT IDENTITY作为聚集键。
该表格中有约3百万行数据。
更改表格还是创建新表格、复制现有数据、删除旧表格并重命名新表格,哪种方法更好?
编辑:复制3百万行数据需要很长时间。删除索引会更快吗?
编辑2:决定通过硬件解决缓慢的复制问题,将20个核心投入其中,而不是4个。现在速度快得多,但仍比我预期的慢得多。我估计复制3百万行需要30分钟。
尽管这只是一个测试,但我仍然需要在生产服务器上完成它,我不希望它停机时间太长。
有关信息,根据@ughai的建议,我的自动增长设置现在为500Mb。
这种事情并不是我的专长,因此希望得到一些建议,以便最佳解决方法。
如果相关的话,对此表格进行的大部分查询都没有连接。
编辑:原始表格模式。
主键是由应用程序代码创建的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%的计划用于在新标识列上聚集索引插入操作
INSERT INTO SELECT
吗? - ughaiSELECT
进行排序并且没有自动增长,那么INSERT INTO SELECT
应该更快。这个表中是否有任何DML查询在后台运行?请确认没有阻塞。 - ughaiINSERT INTO SELECT
中有ORDER BY
语句?你能否发布你正在使用的查询? - ughai