Cette publication ne fait pas partie de la bibliothèque YouScribe
Elle est disponible uniquement à l'achat (la librairie de YouScribe)
Achetez pour : 19,90 € Lire un extrait

Lecture en ligne + Téléchargement

Format(s) : PDF

sans DRM

SQL Server 2008 R2 -2012 : Administration et développement Les bonnes pratiques

De
377 pages
Dominique Verrière est un consultant spécialisé sur les technologies SQL Server depuis la version SQL Server 2000.
Il intervient dans les entreprises pour des missions d'audit, de suivi de performances, d'administration de bases de données.
Les bases de données dont il s'occupe peuvent contenir plusieurs milliards de lignes et atteindre des Téra octets; son expérience du terrain est donc significative.
C'est pourquoi il a décidé de mettre cette expérience à votre profit dans cet ouvrage.
Par ailleurs formateur sur ces domaines depuis un grand nombre d'années, son expérience de la pédagogie vous permettra d'aborder facilement les sujets les plus complexes.
Cet ouvrage est basé sur la version Rc0 de SQL Server 2012 mais, le produit étant assez stable dans cette partie, les utilisateurs de SQL Server 2008R2 trouveront toute satisfaction à lire cet ouvrage.
Afin de mieux montrer les évolutions, des indicateurs SQL Server 2012 annoncent les sections spécifiques.
Par ailleurs, l'ouvrage est truffé de bonnes pratiques, c'est à dire des méthodes opérationnelles de travail que Dominique s'est forgé au fil des années.
Ce livre va vous permettre d'acquérir ou de complémenter les fondamentaux nécessaires à la mise en œuvre d'une base de données SQL Server.
Voir plus Voir moins

Vous aimerez aussi



SQL Server
2008 R2 -
2012
Administration &
développement
Les bonnes pratiques
Dominique Verrière
Dialogue Formation Informatique
SQL Server 2008 R2 -2012 Sommaire
Table des matières
I : Installation de SQL Server 2012 ................................................................................. 15
Choix de la machine de l'installation : ............................................ 16
Version 32 ou 64 bits ? ................................... 16
Quelle édition choisir ? .................................................................................................. 16
Quel mode de licence choisir ? ....................................................... 17
Déroulé de l'installation . 18
Pourquoi utiliser plusieurs instances ? ........................................................................................................... 19
II : Stockage des données .............................. 24
Quels sont les fichiers d'une base de données SQL Server 2012 ? ................................................................... 25
Groupes de fichiers ........................................................................ 26
Ai-je besoin de plusieurs groupes ? ............................................................................ 26
Comment calculer la taille de la base de données?......................... 29
Journaux de transactions ............................................................................................................................... 29
Que contient le journal ? ............................ 29
Comment le journal se vide t-il ? ................................................................................................................ 31
Que contient une base SQL Server 2012 ......... 32
Concepts de clés ............................................ 33
Définition de clé primaire : ......................................................................................................................... 33
Définition de clé étrangère 35
Autre méthode de création des clés étrangères ......................... 39
Conclusion sur les clés primaires et étrangères .......................................................................................... 40
Les types de données de SQL Server 2012 ...................................... 40
Types alphanumériques ............................................................. 40
Taille fixe ou variable ? ............................................................................................... 41
Types de données entières ......................... 41
Types booléens .......................................................................................................................................... 42
Types de données flottants ........................ 43
Types de données décimaux ...................................................................................................................... 43
Types monétaires ....................................................................................................................................... 44
Types dates et heures 45
Conclusion sur les types dates et heures .... 48
Types binaires ............................................................................................................................................ 49

SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 5 Sommaire

Types particuliers ....................................................................................................................................... 49
Compression des données ............................. 61
Méthodes de compression par ligne........... 62
Méthodes de compression par page .......................................................................................................... 68
Incidence de la compression en termes de CPU .......................................................................................... 71
Partionnement des tables .............................................................. 72
Glissement dynamique de partitions .......................................................................................................... 77
III : Indexation................................................ 80
Pourquoi indexer ? ......... 81
Que faut-il indexer ? ...................................................................................................... 81
Comment un index est-il organisé ? ............... 81
Un des premiers critères est le choix de la ou des colonnes : ...... 82
Sélectivité des index : ..................................................................................................................................... 85
Quels sont les types d’index de SQL Server 2012 ? ..................................................................................... 86
Que contiennent les index clustered ? ........ 87
Cas des index non clustered seuls: ............................................................................................................. 89
Cas de l’empilement des index non clustered et clustered :........ 91
Index filtrants : ............................................... 92
Index à colonnes incluses : ............................................................................................................................. 94
Index de type XML : ....................................... 96
Pourquoi indexer les types de données XML ? ............................ 96
Principes d'indexation ................................................................................................ 97
Les index full text : ....................................... 101
Liste des mots vides : ............................... 103
Indexation géo spatiale : .............................................................................................................................. 103
Conclusion ................................................... 107
Index de type column store .......................... 108
Pourquoi les index se fragmentent-ils ? ........................................................................................................ 113
Comment mesurer cette fragmentation ? .... 113
Comment régler cette fragmentation ? ........ 116
Créer les index : ........................................................................................................................................... 117
Remplir les pages de données et d'index : 117
Reconstruire les index : ................................................................................................................................ 119

SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 6 Sommaire
Rebuild ou reorganize ? ............................................................................................................................ 120
IV : Transact SQL .......... 123
Où utiliser le Transact SQL ? ......................... 124
Qu'est ce qu'un lot Transact SQL ? ............................................................................................................... 124
Structures de codage : ................................. 125
Fonctions : ............... 126
Fonctions scalaires ................................................................................................... 126
Fonctions de type chaine : ........................................................ 126
Fonctions de type dates et heures : .......... 128
Fonctions de type numérique : ................................................................................. 131
Fonctions de conversion de type : ............................................ 133
Extraire les données : ................................................................................................... 135
Select mono table : ................................. 135
Clauses Where sur des chaines de caractères : ......................................................... 136
Bien gérer les valeurs NULL : .................................................................................... 136
Bien gérer les dates et heures : ................................................ 137
Recherche de plages de valeurs : .............. 138
Recherche par bornes d'intervalles :......................................................................... 138
Construire des colonnes dérivées : ........................................... 139
Trier les données : .................................... 139
Limiter le nombre de lignes retournées .................................................................... 140
Introduire une conditionnelle dans une requête :..................................................................................... 141
Choisir dans une liste avec choose ........... 142
Prendre une valeur parmi deux ................................................ 143
Grouper les données ................................................................................................................................ 143
Ecarter des données après groupement : . 145
Utiliser les fonctions de ranking :.............................................................................................................. 145
Fonctions statistiques .............................................................................................................................. 146
Fonctions de cube et rollup : .................... 150
Select multi tables........................................................................................................................................ 152
Sous requêtes .......... 153
Jointures .................................................................................................................................................. 154
Jointures internes et externes: ................. 156
Recherche de non correspondance : ........................................................................................................ 157

SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 7 Sommaire

Chainage de jointures externes : .............................................................................................................. 158
Jointure externe complète ....................... 159
Opérateurs ensemblistes ............................................................................................................................. 160
Union ....................................................... 160
Intersection.............................................................................................................. 161
Différence ................ 162
Pivot ........................................................................................................................................................ 162
CTE et tables dérivées .................................. 166
Imbrication de CTE ................................................................................................... 167
CTE et récursivité ..... 167
Insert ........................................................................................................................................................... 171
Insert mono ligne ..................................... 171
Insert multi lignes .................................................................................................... 171
Insert ensembliste .... 171
Insert et clause Output ............................. 172
Merge .......................................................................................................................................................... 174
Update......................... 175
Update avec clause where ....................... 175
Update ensembliste ................................................................................................................................. 175
Update et clause Output .......................... 176
Delete ................................ 176
Delete avec clause where ......................................................................................................................... 176
Delete ensembliste .................................. 177
Clause output sur delete .......................... 177
Fonctions personnalisées ............................................................................................................................. 178
Fonctions scalaires ................................... 178
Fonctions de table en ligne....................... 179
Fonctions de table multi instructions........................................................................ 180
Procédures stockées .................................................................... 183
Capture des erreurs et signalement par RAISERROR : ............... 184
Capture des erreurs et signalement par throw ......................................................................................... 185
Itérer sur les lignes de tables ........................................................ 186
Itération avec curseur .............................. 186

SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 8 Sommaire
Itération sans utiliser de curseur .............................................................................................................. 187
Déclencheurs ............................................... 188
Surveillance des mises à jour de tables......... 188
Surveiller les altérations de schéma ............................................................................................................. 190
Utilisation d'un déclencheur répressif : .... 191
Utilisation d'un déclencheur de trace ....... 192
Visualiser le catalogue des procédures et fonctions ..................................................................................... 194
Conclusion ................................................................................... 195
V Intégration du CLR .................................... 196
Que peut-on faire avec le CLR ?.................................................... 197
Création de fonction en C# ....................................................... 198
Limites concernant la sécurité .................................................. 199
Qu'en est-il des performances ? ............................................................................... 204
Fonctions d'agrégations ............................................................................................................................... 207
Intégration de types personnalisés ............................................... 210
Propriétés privées .... 211
Parsing des entrées .................................................................................................................................. 212
Propriétés publiques 214
Méthodes de restitution .......................................................................................................................... 214
Intégration du type dans la base .............. 216
Utilisation du type dans les agrégats ........................................................................................................ 216
Stabilité du type ....................................... 218
VI Utilisation du XML ................................... 219
Pourquoi utiliser du XML avec une base relationnelle ? ................................................ 220
Produire du XML à partir de structures relationnelles .................. 220
Choisir des attributs plutôt que des éléments .......................................................................................... 222
Production de colonnes en tant qu'éléments ........................... 222
Imbrication d'éléments ............................................................ 223
Imbrication de types par jointure ............................................................................................................. 223
Importer du XML.......................................... 226
Importation de n niveaux ......................................................................................................................... 229
Importer directement depuis un fichier .................................... 230
XML typé et non typé ............................... 230

SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 9 Sommaire

Stocker et requêter du XML ......................................................................................................................... 232
Recherches d'éléments au format XML .................................... 233
Récupération d'un élément d'un indice donné : ....................... 234
Récupération conditionnelle d'éléments .................................................................. 234
Recherche de valeurs d'attributs .............................................. 234
Vérification de l'existence d'attributs ...... 235
Requête sur un XML typé ......................................................................................................................... 236
Un cas pratique d'utilisation de xQuery .................................... 236
VII L'optimiseur de requêtes ....................................................................................... 239
Que fait un optimiseur de requêtes ? ........................................... 240
Quels sont les éléments de travail de l'optimiseur ? ..................................................... 240
Comment sont calculées par défaut ces statistiques ? .............. 241
Exemple de recalcul automatique de statistiques : ................................................... 243
Désactivation des statistiques au niveau table : ........................................................ 244
Recalcul de toutes les statistiques ............................................ 244
Détail des statistiques .............................................................................................. 245
Contrôle des plans d'exécution .................................................... 246
Malgré des index j'ai toujours des analyses d'index clustered ? ................................ 249
Différentes stratégies ................................................................................................... 250
Jointures et fusions d'index ...................... 250
Index couvrants........................................ 251
Utilisation des index pour les group by ..................................................................... 253
Stratégies de jointures ............................................................. 255
Index manquants ......................................... 261
Résumé des principaux opérateurs de l'optimiseur ...................................................... 262
Parallélisme ................................................................................................................. 263
Utilisation des tables temporaires et variables ............................. 264
Forcer les plans d'exécution ......................................................................................................................... 269
Forcer la jointure : .................................... 269
Indicateurs de tables pour les stratégies d'accès ...................... 272
Indicateurs de table pour la gestion transactionnelle ............................................................................... 277
Repères de plan ........................................................................... 281
Autres étendues de repère de plan .......... 283

SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 10 Sommaire
Surveillance de l'utilisation des repères de plan ....................................................................................... 283
VIII Architecture de sécurité ........................................................................................ 286
Introduction à la sécurité ............................................................. 287
Modes d'authentification 288
Authentification Windows ........................................................................................ 288
Authentification SQL ................................................................ 288
Voir le mode d'authentification du serveur .............................. 288
Définition des Logins .................................................................... 289
Login de type groupe ............................................................... 292
Privilèges au niveau serveur ......................................................................................... 293
Association des Logins aux Users.................................................. 298
Privilèges au niveau base de données .......................................................................... 300
Que sont les schémas ? ............................................................................................ 300
Création d'un utilisateur ........................................................... 302
Rôles standards et personnalisés.............................................................................. 303
Privilèges au niveau base de données....................................... 305
Privilèges au niveau du schéma ................ 306
Privilèges au niveau des objets ................................................................................................................. 307
Résumé des privilèges au niveau base ...... 308
Pourquoi les utilisateurs peuvent-ils être orphelins ? ................... 309
Bases de données embarquant la sécurité ................................................................................................... 312
Création d'une base de données avec sécurité embarquée ...... 312
Connexion de cet utilisateur à sa base de données ................................................................................... 314
Avantages de ces bases ............................................................ 315
Prise d'identité ............................................................................. 316
Identification et proxies ............................................................... 317
Audits des actions de serveur ou de base ..................................................................... 320
IX Sauvegardes et restaurations .................. 325
Pourquoi faut-il sauvegarder les bases de données ? ................................................................................... 326
Que faut-il sauvegarder ? ............................................................. 326
Quels sont les types de sauvegardes ?.......................................................................................................... 327
Sauvegarder depuis Management Studio ..... 327
Sauvegarde en stripping ........................................................................................................................... 329

SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 11 Sommaire

Sauvegardes en mirroring ........................................................................................................................ 329
Qu'est ce que le sommaire ? .................... 329
Compression de sauvegardes ................... 331
Sauvegardes par script ................................................................................................................................. 331
Gestion des jeux de sauvegardes .............. 333
Sauvegardes de copie ............................... 335
Restauration des sauvegardes ...................................................................................................................... 335
Autre techniques : .................................... 340
Utilisation des plans de maintenance ........................................................................... 341
Modification du plan pour ajouter les backups log ................................................... 344
X Exploitation automatique ........................ 347
L'agent SQL Server ....................................................................................................................................... 348
Définition de job .......... 349
Historique des jobs .. 350
Moniteur d'activité des jobs ..................................................................................................................... 351
Planification des jobs ................................... 351
Le système de messagerie ............................ 352
Configuration du système d'alerte de l'agent ........................................................................................... 356
Surveillance du serveur au moyen des alertes .......................... 358
Aller plus loin avec les alertes ................................................................................................................... 361
Utilisation de PowerShell ............................. 363
Fondamentaux du langage ....................... 363
Navigation dans les hiérarchies ................................................................................................................ 364
Travail sur les répertoires ......................... 366
Utilisation de PowerShell à l'intérieur d'une étape de job ............ 369
Maintenance du serveur au moyen de PowerShell ................................................................................... 370




SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 12 Indexation

select * from Personne where cv like '%SQL%Consultant%'

select * from personne where contains (cv,'SQL and Consultant')

On constate que la 1ère requête ne sort aucune ligne... ce qui démontre que la stratégie des
like a ses limites; la 2ème consommant ceci :
Table 'Personne'. Nombre d'analyses 0, lectures logiques 2, lectures physiques 0, lectures
anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de
données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

Soit 17 fois moins d'ios que la 1ère requête...
Liste des mots vides :
Vous aurez tous compris que des recherches sur des mots tels que 'le', 'la' sont peu intéressantes;
c'est pourquoi des listes de mots vides (noisy words en Anglais) sont disponibles.
Il est possible de créer ces listes à partir des valeurs par défaut :
CREATE FULLTEXT STOPLIST [France]
FROM SYSTEM STOPLIST
AUTHORIZATION [dbo];

Voici comment on ajoute des mots à cette liste :

ALTER FULLTEXT STOPLIST [France] ADD 'voici' LANGUAGE 'French';
ALTER FULLTEXT STOPLIST [France] ADD 'voilà' LANGUAGE 'French';

Et comment on les en retire :
ALTER FULLTEXT STOPLIST [France] DROP 'voilà' LANGUAGE 'French';


Conclusion sur le full text :

Chaque fois que des mots dans des phrases sont à rechercher dans un volume de données
important,, le full text sera toujours plus performant que l'utilisation de like...
Indexation géo spatiale :

Ce type de donnée apparu en 2008 est bien pratique pour stocker des points, des parcours, des
surfaces... tout cela est bien sympathique, mais qu'en est-il des accès aux tables ?
Prenons un petit exemple pour illustrer le tout : nous avons défini un parcours d'avion inter
continental et nous voulons savoir si notre maison est survolée par ce parcours ?

SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 103 Indexation
if exists (select * from INFORMATION_SCHEMA.tableS
where table_NAME = 'Avion')
drop table Avion
go
create table Avion(IdAvion int identity(1,1) primary key,Donnees
char(200),Localisation geography);

-- (latitude, longitude)
declare @Latitude decimal(7,2), @Longitude decimal(7,2)
set @Longitude = 1.20
set @Latitude = 0.00 -- équateur
set nocount on
while @Latitude <= 50.0
begin
insert into Avion (Localisation,Donnees)
values (geography::Point (@Latitude, @Longitude,4326),
STR(@Latitude,8,3))
set @Latitude = @Latitude + 0.01
end
go

select COUNT (*) from Avion

select IdAvion,Donnees,Localisation.ToString() from Avion
order by Donnees desc


A noter que j'ai du mettre les coordonnées en type décimal, à défaut des problèmes d'arrondis du
float n'auraient pas donné les bons résultats.
Faisons maintenant des recherches pour voir si notre maison est survolée par un avion :

set statistics io on

select IdAvion,Donnees from Avion
where Localisation.STIntersects(geography::Point(45.0,1.20, 4326)) = 1


La requête est bien sûr positive au vu du remplissage; qu'en est-il des accès disque ?


SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 104 Indexation


Visiblement un scan complet, ce que confirme le plan d'exécution :

Nous devons donc indexer ce type pour éviter le scan du clustered index (c'est à dire de la table).
Un index géo spatial est structuré en 4 niveaux qui contiennent des cellules (un peu à l'image de la
couverture par la téléphonie mobile) :

On peut jouer sur le nombre de cellules de chaque niveau, mais aussi sur le nombre de cellules par
objet :
Plus ce nombre augmente, plus l'index est gros et plus il est précis; nous retrouvons là le choix
habituel entre vitesse et volume.
Dans cet exemple notre objet est couvert par deux cellules :

SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 105 Indexation

Construisons un index par défaut sur le type géographique :

-- Index par défaut
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Avion]')
AND name = N'X1_Avion')
DROP INDEX [X1_Avion] ON [dbo].[Avion]
GO
create SPATIAL INDEX X1_Avion ON Avion (Localisation);

select IdAvion,Donnees from Avion
where Localisation.STIntersects(geography::Point(45.0,1.20, 4326)) = 1


Nous arrivons cette fois à 19 ios soit un gain important par rapport aux 457 d'origine.


On peut jouer sur le remplissage des niveaux d'index afin de mieux l'adapter au besoin :
-- un index très plein avec une seule cellule par objet
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Avion]')
AND name = N'X1_Avion')
DROP INDEX [X1_Avion] ON [dbo].[Avion]
GO
create SPATIAL INDEX X1_Avion ON Avion (Localisation)
USING GEOGRAPHY_GRID
WITH (GRIDS = (HIGH, HIGH, HIGH, HIGH ),
CELLS_PER_OBJECT = 1);

select IdAvion,Donnees from Avion
where Localisation.STIntersects(geography::Point(45.0,1.20, 4326)) = 1


Donne 13 ios.
-- un index plein avec 16 cellules par objet
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Avion]')
AND name = N'X1_Avion')

SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 106 Indexation

DROP INDEX [X1_Avion] ON [dbo].[Avion]
create SPATIAL INDEX X1_Avion ON Avion (Localisation)
USING GEOGRAPHY_GRID
WITH (GRIDS = (HIGH, HIGH, HIGH, HIGH ),
CELLS_PER_OBJECT = 16);

select IdAvion,Donnees from Avion
where Localisation.STIntersects(geography::Point(45.0,1.20, 4326)) = 1
go
Donne aussi 13 ios.
A noter que, dans ce cas , la taille minuscule de l'objet (un point) ne joue pas en faveur de cette
stratégie.

Conclusion
Il sera assez difficile de trouver la bonne stratégie d'indexation de ce type de données car elle
dépend beaucoup de la taille des objets géographiques que l'on stocke dans la base.
Ce qui est certain c'est que comme d'habitude, le rapport Lignes cherchées / Total des lignes aura
toute son influence .


SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 107 Indexation
Index de type column store

SQL Server 2012
Cette technologie est issue de Veripack déjà mis en œuvre avec PowerPivot.
Le principe général est d'indexer mais en séparant par colonnes et non par lignes comme on le fait
habituellement.
C'est intéressant car le logiciel va pouvoir jouer sur la redondance et compresser les données.
Parlons tout de suite des limites de cette technologie pour que vous puissiez en faire un choix :
1. Certains types de données ne peuvent pas faire partie de ces index :
binary,varbinary,ntext,text,image,varchar(max),nvarchar(max),uniqueidentifier,rowversion,s
ql_variant,decimal si la précision est supérieure à 18 chiffres, datetimeoffset avec plus de 2
décimales,CLR,XML.
2. Il y a un seul index Column store par table.
3. La table ne peut pas être mise à jour.
4. La consommation mémoire est plus importante que pour les indes classiques.
La limite 1 est logique compte-tenu de la taille de ces données, pour lesquelles de toute façon, il
n'est pas recommandé de créer un index.
La limite 2 vous amène rapidement à la conclusion : indexer le plus possible de colonnes dans le
Column Store : en effet, on peut voir celui-ci comme un magasin compressé de données.
La limite 3 destine clairement cette stratégie aux datawarehouses qui, par nature, sont alimentés et
lus plusieurs fois.
La limite 4 est plus gênante car elle fige la configuration matérielle à une structure de données ; vous
aurez donc quelquefois ce message d'erreur :


Afin de faire quelques tests, j'ai créé deux tables presque identiques :
create table CoureurClassique (NoCoureur int primary key, Nom varchar(80), Prenom
varchar(80), Sexe char(1),NoClub int,DNaissance date,CodePostal varchar(10),Ville r(80))
go
Et cette autre table :
create table CoureurCStore (NoCoureur int , Nom varchar(80), Prenom varchar(80), Sexe
char(1),NoClub int,DNaissance date,CodePostal varchar(10),Ville varchar(80))
go


SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 108 Indexation

Vous aurez noté que je n'ai pas mis de cluster index sur la table CoureurCStore : en effet on va
considérer l'index comme un magasin de données avec l'ensemble des données.
La plupart des articles annoncent des gains importants, mais par rapport à des tables non indexées...
c'est ce que je vais faire dans un 1er temps :
Je charge ces 2 tables avec 300000 lignes puis je crée un index du nouveau type :
create columnstore index xCoureurCStore on
CoureurCStore(Nom,Prenom,NoCoureur,Sexe,NoClub)
go

Examinons maintenant l'espace utilisé par ces deux tables :
exec sp_spaceused 'CoureurClassique'
go
exec sp_spaceused 'CoureurCStore'
go



Sans surprise, les données sont strictement identiques et les index classiques plus petits (un seul
cluster sur un int).
Comparons deux requêtes en termes de plan d'exécution :
select Nom, Prenom,Sexe
from CoureurCStore
where Nom like 'D%'


select Nom, Prenom,Sexe
from CoureurClassique
where Nom like 'D%'

Vous pourrez noter que toutes les colonnes que j'ai utilisées sont stockées dans le nouvel index : on
peut donc dire qu'il est couvrant pour cette requête.

SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 109 Indexation


L'avantage va clairement au nouveau type d'index : le scan de cet index est plus rapide que le
balayage de la table.
Mais qu'en est-il en termes de consommation :

Les temps d'exécution sont comparables mais la 1ère requête a considérablement moins utilisé de
lectures (258 contre 2130).
Donc pour cette lecture avantage net au ColumnStore.
Tentons maintenant une 2ème requête en prenant cette fois des colonnes hors de l'index
ColumnStore :
select Nom, Prenom,Sexe,DNaissance
from CoureurCStore
where Nom like 'D%'


select Nom, Prenom,Sexe,DNaissance
from CoureurClassique
where Nom like 'D%'

On se doute bien que l'index ne pourra pas être intégralement utilisé :

SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 110 Indexation


Effectivement, cette fois les deux requêtes font jeu égal.
Nous pouvons peut-être ajouter un index classique :
create index xCoureurCStore_DNaissance on CoureurCStore(DNaissance)
go

select Nom, Prenom,Sexe,DNaissance
from CoureurCStore
where Nom like 'D%'


Et non ! Pas d'espoir de ce côté...
Bonne pratique : quand on crée un index ColumnStore y ajouter le
maximum de colonnes.

Procédons maintenant à des requêtes d'agrégation :
select Nom, Prenom, count(*) as Nombre
from CoureurCStore
where Nom like 'D%'
group by Nom, Prenom

select Nom, Prenom, count(*) as Nombre
from CoureurClassique
where Nom like 'D%'
group by Nom, Prenom


SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 111 Indexation

De nouveau, léger avantage par rapport à la table non indexée.
Conclusion :
Ce type d'index me parait bien adapté aux bases de données de style DataWareHouse (destinées à la
BI) car :
 On peut indexer beaucoup de colonnes ce qui sera nécessaire quand les tables sont dé
normalisées.
 Les tables doivent être alimentées par période et non en temps réel.
 On dispose en général de pas mal de RAM sur ce type de serveur.



SQL Server 2008R2-2012 : les bonnes pratiques par Dominique Verrière 112 Au sujet de cet ouvrage



Dominique Verrière est un consultant spécialisé sur les technologies SQL Server depuis la version SQL Server
2000.

Il intervient dans les entreprises pour des missions d'audit, de suivi de performances, d'administration de
bases de données.
Les bases de données dont il s'occupe peuvent contenir plusieurs milliards de lignes et atteindre des Téra
octets; son expérience du terrain est donc significative.
C'est pourquoi il a décidé de mettre cette expérience à votre profit dans cet ouvrage.
Par ailleurs formateur sur ces domaines depuis un grand nombre d'années, son expérience de la pédagogie
vous permettra d'aborder facilement les sujets les plus complexes.

Cet ouvrage est basé sur la version Rc0 de SQL Server 2012 mais, le produit étant assez stable dans cette
partie, les utilisateurs de SQL Server 2008R2 trouveront toute satisfaction à lire cet ouvrage.
Afin de mieux montrer les évolutions, des indicateurs SQL Server 2012 annoncent les sections spécifiques.
Par ailleurs, l'ouvrage est truffé de bonnes pratiques, c'est à dire des méthodes opérationnelles de travail
que Dominique s'est forgé au fil des années.

Ce livre va vous permettre d'acquérir ou de complémenter les fondamentaux nécessaires à la mise en œuvre
d'une base de données SQL Server :
 Installer le serveur
 Créer et allouer l'espace disque
 Bien utiliser les types de données disponibles
 Mettre en place une bonne stratégie d'indexation
 Utiliser toutes les possibilités du langage Transact SQL; y compris les nouveautés de SQL Server 2012
 Comment intégrer le CLR dans vos bases de données
 Bien utiliser le XML au sein de SQL Server
 Que fait l'optimiseur de requêtes
 L'architecture de sécurité, avec ses nouvelles bases à sécurité embarquée
 Sauvegarder et restaurer avec les meilleurs automatismes
 Mettre en œuvre une exploitation automatique du serveur
 Surveiller un serveur de façon systématique

Afin de compléter cet ouvrage, un blog www.dominiqueverriere.com est régulièrement mis à jour avec des
articles sur les nouveautés ou expériences nouvelles de l'auteur.



Edition du 28 février 2012.

SQL Server 2008R2-2012 : Administration et développement les bonnes pratiques par Dominique Verrière

Un pour Un
Permettre à tous d'accéder à la lecture
Pour chaque accès à la bibliothèque, YouScribe donne un accès à une personne dans le besoin