Aimeriez vous multiplier par 3 à 10 la vitesse de mise à jour de vos données dans SQL Server ? C’est d’un simplicité enfantine… Il suffit de créer des fichiers de taille fixe, ayant une dimension suffisante pour stocker 3 à 5 années d’exploitation…
Démonstration !
Pour vous convaincre, veuillez lancer le script ci dessous. Il créé deux bases de données. L’une avec des fichiers taillées au minimum et un pas de croissance mini. L’autre suffisamment dimensionnée pour absorber toutes les données.
Les insertion de données aléatoires qui y sont faites sont les mêmes. Le temps est relevé au début et en fin d’insertion. Il est édifiant…
USE master
GO
IF EXISTS(SELECT *
FROM sys.DATABASES
WHERE name = 'TEST_FICHIER_VAR')
DROP DATABASE TEST_FICHIER_VAR;
GO
CREATE DATABASE TEST_FICHIER_VAR
ON PRIMARY ( NAME = DATA,
FILENAME = 'C:\SQLDB\DATA',
SIZE = 3MB,
FILEGROWTH = 1MB)
LOG ON
(NAME = JT,
FILENAME = 'C:\SQLDB\JT',
SIZE = 1MB,
FILEGROWTH = 1MB);
GO
USE TEST_FICHIER_VAR
GO
CREATE TABLE T (LIGNE VARCHAR(500))
GO
DECLARE @T1 DATETIME, @T2 DATETIME, @I INT;
SET @T1 = CURRENT_TIMESTAMP ;
SET @I =1;
INSERT INTO T SELECT REPLICATE('A', 500);
WHILE @I > 100
BEGIN
INSERT INTO T SELECT TOP 3000 * FROM T;
SET @I = @I + 1;
END
CHECKPOINT
SET @T2 = CURRENT_TIMESTAMP
SELECT CAST((@T2 - @T1) AS FLOAT) * 86400.0 AS SECONDE
USE master;
GO
IF EXISTS(SELECT * FROM sys.DATABASES WHERE name = 'TEST_FICHIER_VAR')
DROP DATABASE TEST_FICHIER_VAR
GO
CREATE DATABASE TEST_FICHIER_FIX
ON PRIMARY ( NAME = DATA,
FILENAME ='C:\SQLDB\DATA2',
SIZE = 2GB,
FILEGROWTH = 10%)
LOG ON
(NAME = JT,
FILENAME = 'C:\SQLDB\JT2',
SIZE = 2GB,
FILEGROWTH = 10%);
GO
USE TEST_FICHIER_FIX;
GO
CREATE TABLE T(LIGNE VARCHAR(500));
GO
DECLARE @T1 DATETIME, @T2 DATETIME, @I INT ;
SET @T1 = CURRENT_TIMESTAMP ;
SET @I =1 ;
INSERT INTO T SELECT REPLICATE('A', 500);
WHILE @I > 100
BEGIN
INSERT INTO T SELECT TOP 3000 * FROM T;
SET @I = @I + 1;
END
CHECKPOINT;
SET @T2 = CURRENT_TIMESTAMP ;
SELECT CAST((@T2 - @T1) AS FLOAT) * 86400.0 AS SECONDE ;
USE master;
GO
DROP DATABASE TEST_FICHIER_FIX;
Testé sur mon portable (2 Go de RAM, Dual Core), c’est édifiant :
Base à fichier variable = 40,14 secondes
Base à fichier fixe = 13,34 secondes
Conclusion
En principe les fichiers d’une base de données ne devrait JAMAIS croitre. Ils devraient avoir une taille conçue pour le volume de la base à terme. Par exemple 3 années d’exploitation.
Si vous voulez des performances il vaudrait mieux que votre base ait été créée avec des fichiers de taille fixe. Cela éviterait toute fragmentation physique des fichiers système et en accélérerait les mises à jour (INSERT, UPDATE notamment) de façon importante.
Lorsque vous créez des fichiers de taille fixe SQL Server recherche les meilleurs emplacements sur le disque. Si votre disque est vierge il utilisera les bords externes de tous les plateaux du disque pour créer son fichier. En effet les bord externes sont plus dense en information que le centre du disque. Et il est suffisamment intelligent pour le répartir sur tous les plateaux du disque afin de gagner encore en temps d’accès en limitant le trajet de la tête de lecture…
Attention cependant à ne pas vous amuser à défragmenter à l’aide de l’utilitaire windows, vos fichiers de bases de données SQL Server. Outre le risque de les perdre, le défragmenteur de windows croit naïvement qu’un fichier répartit sur les trois plateaux d’un disque est horriblement fragmenté !
Mais pourquoi est-ce si important de faire des fichiers de taille fixe ?
Tout simplement parce que SQL Server (comme bien d’autres SGBDR haut de gamme) est capable de regrouper par contiguïtés géographiques les écritures de données à effectuées en sessions (toutes les minutes). De ce fait, en retardant sciemment l’écriture des données, il optimise encore le trajet des têtes de lecture. Un phénomène mis en évidence fin des années 70 par Michaël Stonebraker le père de la physique des serveurs SQL !
Des fichiers de taille fixe, sur des disques neufs ou récemment formatés, qui plus est répartis sur de multiples disques permet de diviser de 3 à 20 les temps de réponse en matière d’écriture de fichier.
Dernier point important : attention aux SAN… Il est indispensable que les unités logiques (LUN) soient en correspondance avec des disques physique (Évitez donc le « zoning » ). Sinon cela ne sert à rien. Pire encore les gros SAN dans lesquelles on mélange des écritures venant de tout un tas de services (filers en particulier) perturbent énormément SQL Server. Il faut donc prévoir un SAN hautement administrable. Certains constructeurs (HP, IBM…) proposent d’ailleurs des gammes de serveur et SAN spécialement conçus pour les SGBDR !
Récemment on m’a demandé de faire encore plus gore… Utiliser un NAS !
En effet un internaute était désireux de placer sa base de données sur un fichier distant à l’aide d’un NAS (Network Attached Storage). On ne peut faire pire que cela si l’on veut à tout prix des performances catastrophique. Hé bien sachez que cela est possible. Pour ce faire il faut contourner le fonctionnement natif de l’OS SQL Server (qui s’appelle d’ailleurs SOS !). Le flag 1807 doit être positionné à ON :
DBCC TRACEON (1807)
Pour le laisser en permanence, placez le dans les paramètres du service de l’instance SQL Server.
Dès lors vous pourrez placer vos bases de données sur des ressources distantes, quelles qu’elles soient, mais à condition de respecter la convention de nommage UNC (\\monserveur\monpath\monfichier).
Et maintenant bienvenu à des performances dégradées d’un facteur 100 à 1000 !
Pourquoi ? Parce que par nature un réseau TCP/IP sur Ethernet n’offrant aucune garantie de bon acheminement des trames et ayant la mauvaise habitude de générer de multiples collisions de trames (oui, je sais, on peut contourner cela avec des switches), chaque écriture demandée par SQL Server, attendra bien sagement le retour d’information pour savoir si la demande d’écrire a bien été accomplie. Et comme l’on sait, le journal des transactions devant être écrit le plus rapidement possible et en permanence pour toute transaction, cela rendra le système erratique, engendrant des temps de réponse de plus en plus longs, avec au mieux des time out et des blocages et au pire des interblocages, verrous mortels ou étreintes fatales (appelez-les comme vous voulez…).
Pire ! C’est possible…
Obnubilé par la taille des fichiers d’une base de données, certains développeurs débutant et certains pseudo DBA (ceux qui n’ont généralement jamais suivi une formation de DBA – administrer des bases de données, c’est un vrai métier…) veulent à tout prix, réduire la taille des fichiers… Ils mettent en Å“uvre pour cela un plan de maintenance avec réduction de la taille des fichiers (SHKRINFILE, SHRINKDATABASE, AUTO_SHRINK…) ce qui fait que non seulement ils perdent du temps à faire croitre les fichiers, mais aussi à les faire décroitre et recroitre encore… Bref, un vrai accordéon (ces pseudo DBA devrait se faire musiciens, ils ont plus à y gagner visiblement…).
Posons une analogie. Imaginons que vous construisez un parking. Il est plein, mais il y a encore de la surface non structurée. Une voiture arrive. Vous vous mettez à lisser le béton, peindre le sol et délimiter la place. La voiture repart, vous prenez votre marteau piqueur afin de détruire votre travail… Au moins avec une telle stratégie, c’est le secteur du bâtiment qui va y gagner !!!
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
Bonjour,
Je me permet de réagir sur cet ancien post afin de savoir si cette méthode présente toujours un avantage lorsque l’on utilise des disques flash ?
Je comprend bien l’intérêt de minimiser les déplacements de têtes de lectures et que cela n’est plus le cas sur des disques flash, mais y aurait-il d’autre avantages à fixer la taille des fichiers pour éviter au maximum les agrandissements qui serait toujours valable sur les disques flash ?
Merci
Oui, sur les SSD en attachement direct, mais pas pour les mêmes raisons. L’écriture des SSD génère de la chaleur qui détruit petit à petit le support. L’idée est alors de dispatcher les écritures sur des éléments « lointains » de manière à ne pas créer des points chauds trop destructif, ce que l’on aurait avec des écritures de pages contiguës…
A +
Ma base de données n ‘ a pas de taille fixe, je voudrais changer mes fichiers en taille fixe.
Apres avoir effectuer les changements
Est ce que je dois lancer une action (SQL SERVER) pour réorganiser les données a l’intérieur de ces fichiers ?
Gauthier
Tout cela se calcule de différentes manières… En voici 2 :
1) A PRIORI : à partir du MPD il suffit de calculer la taille des lignes des principales tables et de connaître le nombre de lignes de ces dernières. En multipliant par un coefficient 1,5 cela donne la taille de chaque table. On somme le tout et on rapplique un autre coefficient pour les aléa, qui peut varier de 1,5 à 2,5.
2) A POSTERIORI : après avoir créé la base, on la peuple de centaines de lignes dans chaque table. On demande le volume occupé pour chacune de ces tables et avec un règle de trois, on peut calculer le volume à terme. Prendre aussi un coefficient d’incertitude.
A +
merci,
mais comment calculer la taille de ma dB pour les « 3 à 5 années d’exploitation… »
bav,
Meta
Merci beaucoup pour ces précisions.
Les informations sur la fragmentation et la capacité de SQL Server à placer ces données en bout de plateau est très intéressante.
Merci