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.

internet_citing

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
    

    Variable_Table

    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

 

J’attends vos mythes !!!

 

4 Comments

  • Hello,

    « SQL Server ne gère par la compatibilité ascendante. »
    Tu devais vouloir dire compatibilité descendante je pense.

    Du reste, bon article.

  • danny presse Says

    La commande Truncate Table ne log aucune donnée dans le transaction log. Elle log seulement la « libération’ de l’espace disque utilisé par la table.

    Si jamais vous deviez ramener un full backup ainsi que les transactions log backup qui ont suivi, si l’ESPACE DISQUE jadis utilisé par la table trunqué a déjà été RÉ-UTILISÉ À D’AUTRE FIN PAR SQL, le transaction log ne pourra pas ramener les données de votre table trunqué car les données ont été « ÉCRASÉES » et elle n’existe ni sur disque ni dans le transaction log.

    Dans ce cas, vous auriez du faire Delete * from la table. Dans ce cas le transaction log contiendra toutes les données de la table et le retore du transaction log sera en mesure de ramener les données de votre table

  • danny presse Says

    rectification a mon commentaire précédent

    La commande Truncate Table ne log aucune donnée dans le transaction log. Elle log seulement la « libération’ de l’espace disque utilisé par la table.

    – Si plusieurs jours se sont écoulés depuis le truncate table,
    – Si SQL a déjà ré-utilisé l’espace disque ainsi libéré

    Pour récupérer les données de la table trunquée, vous allez devoir ramener le full backup et seulement les transactions logs qui ont précédés le « truncate table », perdant ainsi plusieurs jours.

    Dans le cas du « delete * from ….. », le transaction log contiendra toutes les données détruites de la table.

    – Si plusieurs jours se sont écoulés depuis le Delete * from ….,
    – Si SQL a déjà ré-utilisé l’espace disque ainsi libéré

    À l’aide d’outil existant sur le marché, vous pourriez « VOIR » et SÉLECTIONNER, dans le transaction log, les données détuites à ramener dans votre BD en production ne perdant ainsi aucune journée.

  • danny presse Says

    Pour récupérer les données de la table trunquée, vous allez devoir ramener le full backup et seulement les transactions logs qui ont précédés le “truncate table”, puis copier la table ainsi récupérée dans votre BD de production.

Comments are closed.

En savoir plus sur Pulsweb - Romain Casteres

Abonnez-vous pour poursuivre la lecture et avoir accès à l’ensemble des archives.

Continue reading