Autogrowth ?

 

Petit article du jour : Quelles bases de données et qui génèrent des Autogrowth sur la TempDB ?

 

Le contexte : Dans un environnement mutualisé, avec donc plusieurs projets sur une même instance la TempDB grossit tous les jours de 20 Go 100 Go ! Qui en est la cause, quand les Autogrowth ont-ils lieux, combien de temps ont-ils duré …

Je ne rentrerai pas dans les détails de la gestion des logs dans cet article, pour plus d’informations je vous invite à lire le suivant : Gestion des logs SQL Server

 

Une mauvaise gestion de la taille des fichiers entraine une fragmentation, voici une liste non exhaustive de bonnes pratiques :
• Si vous importez des grandes quantités de données qui ont tendance à être de taille fixe sur une base hebdomadaire, augmenter la base de données d’une taille fixe pour tenir compte des nouvelles données.
• Lors de la création de base, initialiser la taille des fichiers
• Gérer de manière proactive la croissance des données et des fichiers journaux
• Éviter l’utilisation des commandes SHRINK

Par défaut, les fichiers de SQL Server (MDF/NDF/LDF) sont initialisés de sorte qu’ils puissent écraser, sur le disque, toutes les données des fichiers précédemment supprimés en « remplissant » leur taille à 0. Lorsque les fichiers sont volumineux les temps d’initialisation peuvent être conséquents. L’initialisation de fichiers de données intervient généralement durant ses différents cas de figure :
• Création d’une base de données
• Ajout de fichiers (ou filegroups) à une base de données
• Augmentation de la taille d’un fichier (i.e., Autogrow)
• Restauration d’une base de données ou d’un groupe de fichiers

Pour accélérer l’initialisation des fichiers, il suffit de configurer correctement les paramètres de sécurité locale du compte de service utilisé par l’instance SQL Server concernée. Pour ce faire, il faut aller dans le SecPol (en tapant « SecPol.msc » dans Démarrage->Exécuter, par exemple) et y ajouter le compte de service SQL Server au sein de l’autorisation « Perform Volume Maintenance Tasks » sous Users Rights Assignements. Cette opération activera ce qu’on appelle l’Instant File.

 

TempDB après un Restart :

 

Quelques scripts T-SQL

Des informations sur les bases de données :

SELECT
    DB_NAME(database_id) AS db
   ,name
   ,type_desc
   ,physical_name
   ,size
   ,max_size
   ,growth
   ,is_percent_growth
FROM
    sys.master_files

EXEC sp_MSforeachdb @command1 = 'use [?]; EXEC sp_helpfile';

EXEC sp_MSforeachdb @command1 = 'use [?]; EXEC sp_spaceused';

Quelle sera la prochaine taille du fichier après le prochain Autogrowth :

SELECT 
    DB_NAME(files.database_id) AS database_name, 
    files.name logical_name, 
    CONVERT (numeric (15,2) , (convert(numeric, size) * 8192)/1048576) AS [file_size (MB)],
    [next_auto_growth_size (MB)] = CASE is_percent_growth
        WHEN 1 THEN CONVERT(numeric(18,2), (((convert(numeric, size)*growth)/100)*8)/1024)
        WHEN 0 THEN CONVERT(numeric(18,2), (convert(numeric, growth)*8)/1024)
    END,
    is_read_only = CASE is_read_only 
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
    END,   
    is_percent_growth = CASE is_percent_growth 
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
    END,  
    physical_name
FROM sys.master_files files
WHERE 
    files.type in (0,1)
    AND files.growth != 0

Et voici la plus intéressante des requêtes :

-- Auto Growth History
DECLARE @filename NVARCHAR(1000);
DECLARE @bc INT;
DECLARE @ec INT;
DECLARE @bfn VARCHAR(1000);
DECLARE @efn VARCHAR(10);

-- Get the name of the current default trace
SELECT
    @filename = CAST(value AS NVARCHAR(1000))
FROM
    ::
    FN_TRACE_GETINFO(DEFAULT)
WHERE
    traceid = 1
    AND property = 2;

-- Rip apart file name into pieces
SET @filename = REVERSE(@filename);
SET @bc = CHARINDEX('.', @filename);
SET @ec = CHARINDEX('_', @filename) + 1;
SET @efn = REVERSE(SUBSTRING(@filename, 1, @bc));
SET @bfn = REVERSE(SUBSTRING(@filename, @ec, LEN(@filename)));

-- Set filename without rollover number
SET @filename = @bfn + @efn

-- Process all trace files
SELECT
    ftg.StartTime
   ,te.name AS EventName
   ,DB_NAME(ftg.databaseid) AS DatabaseName
   ,ftg.Filename
   ,( ftg.IntegerData * 8 ) / 1024.0 AS GrowthMB
   ,( ftg.duration / 1000 ) AS DurMS
FROM
    ::
    FN_TRACE_GETTABLE(@filename, DEFAULT) AS ftg
    INNER JOIN sys.trace_events AS te ON ftg.EventClass = te.trace_event_id
WHERE
    ( ftg.EventClass = 92  -- Date File Auto-grow
      OR ftg.EventClass = 93
    ) -- Log File Auto-grow
ORDER BY
    ftg.StartTime

Remarque : Je n’ai pas écrit moi-même ces scripts T-SQL, je ne me rappelle malheureusement plus où je les avais trouvés mais j’espère qu’ils vous serviront ! Au moins je saurais ou les retrouver 😉