5 Définition de la taille d`une base de données

ALSI S25
STS2 IG
SQL Avancé et administration d’un SGBD Relationnel
Application à SQL Server 6.5
Le 17/04/17
page 1
ALSI / S25 SGBD R Support de Cours 2TSIG D:\840909851.doc
SOMMAIRE
1PRESENTATION DE LA SEQUENCE 2
1.1DESCRIPTION DES SAVOIRS 2
1.2COMPETENCES ATTENDUES 2
2MODE DE STOCKAGE DES DONNEES 2
2.1FICHIER DE DONNEES 2
2.2EXTENSION 3
2.3 PAGE 3
3LE JOURNAL DES TRANSACTIONS 4
3.1RAPPEL SUR LES TRANSACTIONS 4
3.2LE JOURNAL DES TRANSACTIONS 4
3.3FONCTIONNEMENT DU JOURNAL DES TRANSACTIONS 4
4CREATION D’UNE BASE DE DONNEES 5
4.1COMMANDE SQL DE CREATION 5
4.2EXTENSION OU REDUCTION D’UNE BASE 5
4.3LES OPTIONS D’UNE BASE DE DONNEES 6
4.4GESTION D’UNE BASE DE DONNEES SUR PLUSIEURS DISQUES 7
5DEFINITION DE LA TAILLE D’UNE BASE DE DONNEES 8
5.1ESTIMATION DE LA TAILLE D’UNE TABLE 8
5.1.1Remarques préliminaires 8
5.1.2Etape 1 : Calcul de la taille d’une ligne de données 8
5.1.3Etape 2 : Calcul du nombre de pages de données 8
5.1.4Etape 3 : Calcul de la taille des lignes d'un index ordonné 9
5.1.5Etape 4 : Calcul du nombre de pages d'un index ordonné 9
5.1.6Etape 5 : Calcul du nombre total de pages 9
5.2CALCUL DE LA TAILLE D'UNE TABLE DE 9 MILLIONS DE LIGNES DOTEE D'UN INDEX ORDONNE 9

ALSI S25
STS2 IG
SQL Avancé et administration d’un SGBD Relationnel
Application à SQL Server 6.5
Le 17/04/17
page 2
ALSI / S25 SGBD R Support de Cours 2TSIG D:\840909851.doc
1 PRESENTATION DE LA SEQUENCE
1.1 Description des savoirs
Il s’agit dans ce chapitre de comprendre comment une base de données est structurée pour répondre à la
nécessité de fiabilité. On voit aussi comment estimer l’espace disque nécessaire à une BD.
256 Administration de la base de données
1.2 Compétences attendues
 administrer une BdD non répartie et en assurer la sécurité
2 MODE DE STOCKAGE DES DONNEES
La première étape à réaliser lors de la mise en place d’une base de données consiste à réserver un espace
disque destiné au stockage de toutes les données de la base.
Avec SQL Server, il est nécessaire de pré-allouer l’espace de stockage c’est à dire qu’il faut calculer et
réserver un espace disque suffisant pour y stocker la base. SQL Server utilise l’architecture suivante :
2.1 Fichier de Données
C’est l’entité de base créée par le système d’exploitation (instruction Disk Init).Un fichier physique est
alors créé, il possède un nom logique d’unité, un nom physique (de fichier), une taille et un numéro. Un fichier de
données peut contenir :
 une ou plusieurs bases de données,
 un ou plusieurs journaux de transactions.

ALSI S25
STS2 IG
SQL Avancé et administration d’un SGBD Relationnel
Application à SQL Server 6.5
Le 17/04/17
page 3
ALSI / S25 SGBD R Support de Cours 2TSIG D:\840909851.doc
A l’installation, SQL Server génère trois fichiers de données: MASTER.DAT, MSDBDATA.DAT,
MSDBLOG.DAT
2.2 Extension
Une extension prend 64 Ko.
Chaque table ou index est affecté d’une extension lors de sa création : l’extension initiale. A mesure que la
table augmente de volume, le système lui attribue de nouvelles extensions : les extensions supplémentaires.
C’est l’unité de stockage minimum d’une table ou d’un index. Il ne peut y avoir qu’une table ou un index
par extension.
2.3 Page
Chaque extension contient 8 pages de données, c’est dans cet espace que sont stockées les lignes des
tables et index. Une ligne ne peut pas s’étendre sur plusieurs pages.
La taille d’une page est de 8 Ko octets (dont 8192 octets utilisables), cependant la longueur maxi d’un en-
registrement (hors champs text et image) est 8060 octets. Il est important de bien calculer la longueur des enregistre-
ments lors de la conception.
Exemple : si un enregistrement occupe 5 Ko, cette longueur ne permet pas de stocker 2 enregistrements
par page (2*5 Ko = 10 Ko), de ce fait chaque enregistrement occupera une page, ce qui représente un gaspillage de
près de 50% de l’espace.
La procédure sp_estspace permet de calculer la taille d’une table en fonction de sa structure et du nombre
d’enregistrements estimés.

ALSI S25
STS2 IG
SQL Avancé et administration d’un SGBD Relationnel
Application à SQL Server 6.5
Le 17/04/17
page 4
ALSI / S25 SGBD R Support de Cours 2TSIG D:\840909851.doc
3 LE JOURNAL DES TRANSACTIONS
3.1 Rappel sur les transactions
Une transaction est un ensemble d’instructions SQL, traitées comme une unité unique de travail et de récu-
pération. L’unité doit être exécutée en totalité ou pas du tout.
Il existe deux sortes de transactions :
 Une transaction implicite dans laquelle chaque instruction UPDATE, INSERT ou DELETE s ‘exécute
comme une transaction.
 Une transaction explicite définie par l’utilisateur qui regroupe les instructions encadrées par BEGIN
TRANSACTION et COMMIT TRANSACTION.
3.2 Le journal des transactions
Chaque transaction est enregistrée dans un journal des transactions pour maintenir la cohérence de la BD et
permettre sa récupération en cas de défaillance du système.
Le journal est une zone de stockage qui opère un suivi de toutes les modifications apportées à une base de
données. Les modifications sont enregistrées dans le journal avant d’être écrites dans la BD.
3.3 Fonctionnement du journal des transactions

ALSI S25
STS2 IG
SQL Avancé et administration d’un SGBD Relationnel
Application à SQL Server 6.5
Le 17/04/17
page 5
ALSI / S25 SGBD R Support de Cours 2TSIG D:\840909851.doc
4 CREATION D’UNE BASE DE DONNEES
4.1 Commande SQL de création
4.2 Extension ou réduction d’une Base
La taille de la base peut être réduite ou étendue par la suite au fur et à mesure des besoins. On utilise alors
- l’interface graphique
- l’instruction DBCC
- l’instruction ALTER DATABASE MaBase MODIFY FILE …
Une autre façon d’étendre une base de données est d’ajouter des fichiers de données ou de journal par
- l’interface graphique
- l’instruction ALTER DATABASE MaBase ADD FILE …
CREATE DATABASE biblio
ON
PRIMARY ( NAME=données_biblio,
FILENAME='c:\mssql7\data\biblio.mdf',
SIZE=10MB,
MAXSIZE=15MB,
FILEGROWTH=20%)
LOG ON
( NAME=journ_biblio,
FILENAME='c:\mssql7\data\biblio.ldf',
SIZE=3MB,
MAXSIZE=5MB,
FILEGROWTH=1MB)
CREATE DATABASE biblio
ON
PRIMARY ( NAME=données_biblio,
FILENAME='c:\mssql7\data\biblio.mdf',
SIZE=10MB,
MAXSIZE=15MB,
FILEGROWTH=20%)
LOG ON
( NAME=journ_biblio,
FILENAME='c:\mssql7\data\biblio.ldf',
SIZE=3MB,
MAXSIZE=5MB,
FILEGROWTH=1MB)
Pour créer une base de données, vous devez
définir les éléments suivants :
nom de la base de données
taille de la base de données
fichiers utilisés pour la stocker