SQL Server 2012 présente de nombreuses nouvelles fonctionnalités. En ce qui concerne les performances et la haute disponibilité, voici une amélioration assez intéressante : l’ajout de colonnes NOT NULL en tant qu’opération en ligne. Elle peut, dans certains cas, rendre quasiment instantanné l’ajout de nouvelles colonnes même si la table contient un très grand nombre d’enregistrements.

Pour rappel, SQL Server stocke les données des tables sous forme de pages de 8Ko. Chaque page contient des lignes complètes (une ligne n’est pas à cheval sur deux pages). Le contenu des lignes dépendant du type d’index : toutes les colonnes de la table pour les index clustered ou les segments, ou bien uniquement un sous-ensemble dans le cas des index non clustered.

Considérons une table avec un index clustered. Les pages de cet index contiennent toutes les colonnes de la table. Mais si l’on cherche à ajouter une colonne à la table, il convient de reprendre toutes les lignes, et donc toutes les pages, pour ajouter cette nouvelle colonne. Lorsque la table contient un grand nombre d’enregistrements, l’opération peut s’avérer très coûteuse.

Prenons l’exemple ci-dessous, sous SQL Server 2008 R2. D’abord, la création de la table et son remplissage.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableVolumineuse]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TableVolumineuse](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Chaine1] [char](2000) NOT NULL,
 CONSTRAINT [PK_TableVolumineuse] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)) ON [PRIMARY]
END
GO
set nocount on
go
insert into TableVolumineuse(Chaine1) values ('')
GO 1000000

Cette table contient un volume de données non négligeable.

Si l’on doit maintenant ajouter une autre colonne, toutes ces pages devront être retouchées, et les ressources disque consommées par cette opération seront très importantes.

alter table TableVolumineuse add DateHeure datetime NOT NULL DEFAULT '20121225'

La trace du profiler donne les valeurs suivantes :

Non seulement on voit 250000 lectures de pages et autant d’écritures, mais aussi de nombreuses secondes (de l’ordre de 3 minutes sur la machine sur laquelle j’ai lancé ce test) d’exécution du processus. Et pendant tout ce processus, la structure de la table est verrouillée car en cours de modification, ce qui signifie qu’aucune requête ne peut être exécutée sur cette table.

Dans un contexte de données critiques avec de nombreuses connexions et un SLA exigeant, un tel blocage est très difficile à faire passer…

SQL 2012 présente donc dans son édition Enterprise (édition dédiée à ce type d’environnements exigeants) un comportement beaucoup plus intéressant. L’ajout d’une colonne valuée avec une constante d’exécution (c’est-à-dire une valeur ne dépendant pas de la ligne) s’effectue uniquement sous forme de métadata. Plus tard, lorsqu’une des pages aura besoin d’être écrite (lors de la mise à jour d’enregistrements, ou lors de la reconstruction d’index), le système en profitera pour intégrer la nouvelle colonne. Et en attendant, les métadatas suffiront pour que les instructions de type SELECT se comportent comme si la colonne avait effectivement été pleinement insérée. (plus d’informations comme d’habitude sur l’aide en ligne).

La démonstration est très parlante, en relançant la même requête d’ajout de colonne que ci-dessus, mais cette fois-ci en environnement SQL Server 2012.

alter table TableVolumineuse add DateHeure datetime NOT NULL DEFAULT '20121225'

D’une durée de 3 minutes, nous sommes passés en dessous du dixième de seconde, ce qui ne manquera pas de satisfaire les clients exigeants…

Cette fonctionnalité ne nécessite pas d’action particulière pour être activée, elle l’est automatiquement dans l’édition Enterprise de SQL Server 2012. Et tout DBA en conviendra, des modifications de structure si rapides montrent pleinement les efforts qui ont été apportés sur les performances et la haute disponibilté…