Les mythes sur SQL Server

Le mythe, au sens courant et populaire, renvoie à une croyance erronée. Dans cet article (qui évoluera par la suite) je vais citer et réfuter quelques unes des fausses idées rencontrées lors de missions ou d’entretien technique. Quelques unes de ces idées sont parfois tellement ancrées dans les idées de chacun qu’il en est même difficile de les en dépersuader.

Parce que l’on trouve de tout sur internet, il ne faut rien prendre pour acquis mais garder sont esprit critique ! En effet n’importe qui peut écrire un post de blog ou un article (même moi :-p) et le fait d’avoir de l’expérience, être expert ou encore MVP ne signifie pas que l’information énoncée est correcte. D’autant plus que certaines informations véridiques peuvent se contredire : legorafi.fr :-p

Aussi afin de réfuter au mieux ces mythes je tacherais de les justifier par des requêtes, des exemples, … que vous pourrez bien entendu reproduire.

Je n’ai pas la prétention de tout connaitre et d’avoir tout vu sur SQL Server (loin de la !). N’hésitez donc pas à me faire part de fausses idées que vous auriez rencontrées.

Performance

• L’Instant File Initialization n’est possible qu’avec l’édition entreprise de SQL Server :
  Toutes les versions des SQL Server permettent l’Instant File Initialization, pour l’activer il faut que le compte de service SQL Server soit présent dans la tâche « Perform Volume Maintenance » (Local Security Policy).

• Le Shrink des données n’affecte pas les performances :
  Bien au contraire, pendant le Shrink des verrous exclusifs sont postés sur les pages et les Shrink introduisent de la fragmentation dans les index (il faudra de ce fait les reconstruire).

• La commande Truncate Table ne log aucune donnée :

  Toutes les opérations utilisateur sont loggées dans SQL Server ! En effet la commande « Truncate Table » log peu mais log quand même (tout comme la commande DROP Table !)

  ALTER DATABASE [Test] SET RECOVERY SIMPLE;
  
  USE [Test]
  GO
  
  CREATE TABLE [dbo].[Test](
  	[ID] [int] IDENTITY(1,1) NOT NULL,
  	[Name] CHAR (4000) DEFAULT 'abc'
  ) ON [PRIMARY];
  
  SET NOCOUNT ON;
  
  INSERT INTO [Test] DEFAULT VALUES;
  GO 10000
  
  BEGIN TRAN;
  	TRUNCATE TABLE [Test];
  	SELECT COUNT (*) AS N'Ligne' FROM [Test];
  ROLLBACK TRAN;
   
  SELECT COUNT (*) AS N'Ligne' FROM [Test];
  

  Le script précédent insert 10000 lignes dans la table « Test » de la base de données « Test ». Après avoir effectué la commande Truncate sur la table « Test », le nombre de lignes est de 0. La commande Rollback annule la transaction et le nombre de lignes est de nouveau 10000 ! Il est clair que l’opération Truncate log sinon l’opération Rollback ne fonctionnerais pas.

• Un GUID est une bonne clé de clusteur :

  Les GUID sont généralement aléatoires et créent de la fragmentation d’index. De plus les GUID occupent 16 bytes contrairement au Int de 4 bytes et au BigInt de 8 bytes.

Objets temporaires

• Les variables de type table résident en mémoire :

  Vous trouverez cet information sur nombre de forums, certains pensent même que c’est la principale différence entre les tables temporaires et les variables de types tables !

  Je vais dans un premier temps récupérer le nombre d’objets de ma session, puis regarder le nombre de pages allouées à celle ci. Le résultat prouve que le nombre de pages allouées pour une table temporaire et une variable de type table est la même.

  -- Get Current Session ID objects
  SELECT session_id, user_objects_alloc_page_count
  FROM sys.dm_db_session_space_usage
  WHERE session_id = (SELECT @@SPID )
  GO
  
  CREATE TABLE #TempTable (ID INT)
  INSERT INTO #TempTable (ID)
  SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
  FROM sys.all_objects a CROSS JOIN sys.all_objects b
  GO
  
  SELECT session_id, user_objects_alloc_page_count
  FROM sys.dm_db_session_space_usage 
  WHERE session_id = (SELECT @@SPID )
  GO
  
  -- Create Memory Table and insert one hundred thousand rows
  DECLARE @TableVariable TABLE(ID INT )
  INSERT INTO @TableVariable (ID)
  SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID
  FROM sys.all_objects a CROSS JOIN sys.all_objects b
  GO
  
  SELECT session_id, user_objects_alloc_page_count
  FROM sys.dm_db_session_space_usage
  WHERE session_id = (SELECT @@SPID )
  GO
  

  Autre méthode pour prouver que les 2 tables résident dans TempDB :

  -- Get Current Datetime
  DECLARE @CurrentDateTime DATETIME
  SELECT @CurrentDateTime = GETDATE()
  SELECT @CurrentDateTime CurrentTime
  
  CREATE TABLE #TempTable (ID INT)
  DECLARE @TableVariable TABLE(ID INT)
  
  -- Check the space usage in page files
  SELECT * FROM tempdb.sys.objects
  WHERE type = 'U' AND create_date >= @CurrentDateTime
  GO
  

  

  Le résultat prouve que les deux tables sont belles et bien présentent dans la base de données TempDB. Un bon article sur le sujet : Temp Table Vs Table Variable

• Les variables de type table ne peuvent pas avoir d’index :

  C’est l’une de mes dernières trouvailles, depuis peut je pensais qu’il était impossible d’avoir un index sur une variable de type table. En effet, la requête suivante renvoie une erreur :

  DECLARE @TableVariable TABLE (ID INT NOT NULL, C1 VARCHAR(10));
  ALTER TABLE @TableVariable
  ADD CONSTRAINT [PK_TableVariable] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY];
  

  Mais pas celle ci 🙂

  DECLARE @TableVariable TABLE (ID INT PRIMARY KEY CLUSTERED, C1 VARCHAR(10));
  

Backup

• En mode de recouvrement simple le fichier de log ne grossit pas :

  J’ai traité quelques mythes sur les logs dans cet article : Logs SQL Server

• Le journal de transactions devrait être aussi faible que possible :

  Le journal doit être aussi grand qu’il doit être, après réduction de celui ci (Shrink) il devra regrossir de nouveau. De plus le journal de transactions ne peut pas utiliser l’Instant File Initialization contrairement au fichier de données ce qui rallonge le temps d’initialisation. Attention à la restauration de base de données ayant des fichiers de logs conséquents : Le fichier de log doit être créé et initialisé à zéro avant la restauration ce qui peut être long !

• Une sauvegarde de base de données peut être restaurée à une version antérieure de SQL Server :

  SQL Server ne gère par la compatibilité ascendante. Par exemple une base de données SQL Server 2012 ne peut pas être restaurée sur SQL Server 2008 R2.

Bonus

• D’autres mythes et fausses idées en anglais par Paul Randal : Misconceptions about everything