Изменение ключа кластеризации в существующей таблице - SQL Server 2008

Мне передали задачу улучшить производительность запросов для таблицы.

Первичный ключ является GUID он создается кодом приложения, поэтому не является последовательным, и в таблице нет отдельного ключа последовательной кластеризации.

Я чувствую, что это выбор непоследовательного GUID в качестве основного и кластерного ключа, который является основным виновником низкой производительности. Я намерен сбросить кластерный индекс на GUID и добавить INT IDENTITY в качестве ключа кластеризации.

В таблице ~3 миллиона строк.

Лучше попытаться изменить таблицу или создать новую таблицу, скопировать в нее существующие данные, удалить старую таблицу и переименовать новую таблицу?

РЕДАКТИРОВАТЬ: Копирование 3 миллионов строк занимает очень много времени. Будет ли падение индекса быстрее?

РЕДАКТИРОВАТЬ 2: решил решить проблему медленного копирования с аппаратным обеспечением, и бросил на него 20 ядер вместо 4. Теперь это намного быстрее, хотя все еще намного медленнее, чем я ожидал. Я предполагаю, что это займет 30 минут, чтобы скопировать 3 миллиона строк.

Тем не менее, я бы оценил решение, так как это всего лишь тест, мне все равно придется делать это на рабочем сервере, я бы предпочел не отключать его дольше, чем необходимо.

Для информации, основанной на совете @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 ответ

По моему опыту, самый быстрый способ - удалить существующий кластерный индекс.

drop index index_name on tablename;

Затем воссоздайте кластерный индекс:

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

Если вы собираетесь копировать данные, скопируйте их в целевую таблицу, которая не содержит индексов. После того, как все вставки выполнены, сначала создайте кластерный индекс, а затем все остальные индексы. Если вы создадите индексы перед вставками, у вас, вероятно, будет много и много разделений страниц, которые могут занять много времени.

Источник
Другие вопросы по тегам