MS SQL Server
  1. MS SQL Server
  3. Modification de la key de cluster sur une table existante – SQL Server 2008
Intereting Posts
La command SQL INSERT fonctionne mais datatables n'apparaissent pas dans la table Groupe de tables par – Tsql – Suppression d'un groupe de tables dans SQL Server sys view équivalent de la requête INFORMATION_SCHEMA BACKUP DATABASE WITH COMPRESSION n'est pas supporté sur Web Edition (64 bits) Comment utiliser la procédure stockée dans une sous-requête pour get une valeur dans la clause where avec l'utilisation des données de la requête principale en tant que parameters Verrou exclusif tout ou rien sur 2 tables SQL erreur lors de la conversion de varchar en float PHP PDO se connecte à MS SQLServer Express en utilisant SSL Ai-je besoin de droits SQL Server db_ddladmin pour une approche Entity Framework Code-First et est-ce un problème de security? Insérer XML dans la colonne XML Table SQL ASP.NET MVC 3 – Impossible de find le fournisseur de données .Net Framework demandé Contrôle de visionneuse de rapports SSRS 2016 Paramètres de la clause EXISTS dans une procédure stockée Pourquoi la conversion d'une string vide à une date dans SQL Server aboutit à 1900-01-01?

Modification de la key de cluster sur une table existante – SQL Server 2008

J'ai reçu une tâche pour améliorer les performances des requêtes sur une table.

La key primaire est un GUID créé par le code de l'application, donc non séquentiel, et il n'y a pas de key séparée de mise en cluster séquentielle sur la table.

Mon sentiment est que c'est le choix d'un GUID non-séquentiel comme key primaire et de clustering qui est le principal responsable d'une mauvaise performance. J'ai l'intention de supprimer l'index cluster sur le GUID et d'append une INT IDENTITY comme key de clustering.

La table contient ~ 3 millions de lignes.

Est-il préférable d'essayer de modifier la table ou de créer une nouvelle table, de copyr datatables existantes, de supprimer l'ancienne table et de renommer la nouvelle table?

EDIT: Copier 3 millions de lignes prend beaucoup de time. Est-ce que laisser tomber l'index serait plus rapide?

EDIT 2: Décidé de résoudre le problème de la copy lente avec le matériel, et jeté 20 cœurs au lieu de 4. Il est beaucoup plus rapide maintenant, mais encore beaucoup plus lent que je m'attendais. J'estime qu'il faudra 30 minutes pour copyr les 3 millions de lignes.

J'apprécierais toujours une solution bien que ce soit juste un test, je dois toujours le faire sur le server de production, je préférerais ne pas l'avoir pour plus longtime que nécessaire.

Pour info, basé sur le conseil de @ughai, mes parameters de croissance automatique sont maintenant à 500Mb.

Ce genre de chose n'est pas vraiment ma spécialité, donc j'apprécierais quelques conseils quant à la meilleure façon de contourner cela.

Si c'est pertinent, la majeure partie des requêtes qui ont lieu sur cette table n'ont pas de jointures.

EDIT: schéma de table d'origine 

  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_StornextTapeResortingevalStarted] [datetime] NULL, [Date_StornextTapeResortingevalFinished] [datetime] NULL, [IOServiceAddress] [nvarchar](20) NULL, [LogSsortingng] [nvarchar](max) NULL, [NotesSsortingng] [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]

Schéma de table de destination 

 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_StornextTapeResortingevalStarted] [datetime] NULL, [Date_StornextTapeResortingevalFinished] [datetime] NULL, [IOServiceAddress] [nvarchar](20) NULL, [LogSsortingng] [nvarchar](max) NULL, [NotesSsortingng] [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

Ma requête de copy 

  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_StornextTapeResortingevalStarted] ,[Date_StornextTapeResortingevalFinished] ,[IOServiceAddress] ,[LogSsortingng] ,[NotesSsortingng] ,[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_StornextTapeResortingevalStarted] ,[Date_StornextTapeResortingevalFinished] ,[IOServiceAddress] ,[LogSsortingng] ,[NotesSsortingng] ,[TX_Date] ,[SlowDownUpload] FROM [dbo].[IODBTaskHistory]

Plan d'exécution

entrez la description de l'image ici

Si l'image n'est pas claire, 99% du plan est passé sur l'insertion d'index clusterisée dans la nouvelle colonne Identité

D'après mon expérience, le moyen le plus rapide est de supprimer l'index clusterisé existant 

 drop index index_name on tablename;

Recréez ensuite l'index cluster: 

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

Si vous souhaitez copyr datatables, copyz-les dans une table cible qui ne contient aucun index. Une fois toutes les insertions effectuées, créez d'abord l'index cluster puis tous les autres index. Si vous créez les index avant les insertions, vous aurez probablement beaucoup, et beaucoup, de divisions de page qui peuvent prendre beaucoup de time.