Syndication : Atom 1.0  RSS 2.0
Blogs des développeurs   »   SQL SERVER - Etienne ZINZINDOHOUE

Article complet: Index non-cluster : choix de l'optimiseur

17/09/2010

Permalink 00:00:00, Catégories: Récapitulatif SGBD, SQL Server, SQL SERVER 2005, SQL SERVER 2008, 1194 mots   French (FR) , zinzineti

[SGBD][SQL Server] Index non-cluster : choix de l'optimiseur

L'idée est d'examiner l'utilisation des index non-cluster par l'optimiseur de requête.
Afin de mettre en évidence le choix du moteur de base données vis à vis des index non-cluster couvrants avec colonnes incluses ou non

[Suite:]

--> Création de la table
SET NOCOUNT ON  
IF OBJECT_ID('dbo.T_TEST') IS NOT NULL  
DROP TABLE dbo.T_TEST  
CREATE TABLE dbo.T_TEST  
(  
  id int identity(1,1),  
  val varchar(10),  
  creation_date datetime  
) 
 
--> Chargement d'1 million de lignes dans la table  
DECLARE @counter int;  
SET @counter = 1;  
WHILE @counter <= 1000000  
BEGIN  
INSERT INTO T_TEST(val,creation_date) VALUES(convert(varchar(10),(LEFT(convert(bigint,RAND()*10000000),6))),getdate());  
SET @counter = @counter + 1  
END;  

--> Plaçons 1% de la colonne 'val' sur la valeur '0' : objectif avoir une forte (haute) sélectivité pour la requête de test
UPDATE dbo.T_TEST SET val = '0' WHERE dbo.T_TEST.id <=10000

--> Activons les options IO afin de mesurer les pages logiques lues
SET STATISTICS IO ON
------------------------------------------------------
-->1. Exécutons la requête de test
------------------------------------------------------
SELECT id,val,creation_date 
FROM T_TEST 
WHERE val = '0'

Résultat => TABLE SCAN, lectures logiques 4017

------------------------------------------------------
--> 2. Créons un index non-cluster sur la colonne val
------------------------------------------------------
CREATE INDEX IXNC_val ON T_TEST (val)  
-- Test  
SELECT id,val,creation_date 
FROM T_TEST 
WHERE val = '0'

Résultat => TABLE SCAN , lectures logiques 4017

--Forçons maintenant l'optimiseur à utiliser l'index IXNC_val
SELECT id,val,creation_date 
FROM T_TEST WITH (INDEX(IXNC_val))  
WHERE val = '0'

Résultat => INDEX SEEK, lectures logiques 10027
--Interpretation : ici on a un index seek mais le nombre de pages lues est double du nombre de pages dans le cas d'un
-- TABLE SCAN. Donc forcer l'otimiseur à utiliser l'index IXNC_val n'est pas la meilleure solution

------------------------------------------------------
--> 3.1 Créons un index non-cluster sur les trois colonnes id,val,creation_date dans cet ordre
------------------------------------------------------
CREATE INDEX IXNC_id_val_creationdate ON T_TEST (id,val,creation_date)  
-- Test  
SELECT id,val,creation_date 
FROM T_TEST 
WHERE val = '0' 

Résultat => TABLE SCAN , lectures logiques 4017

--Forçons l'optimiseur à utiliser l'index IXNC_id_val_creationdate
SELECT id,val,creation_date 
FROM T_TEST WITH (INDEX(IXNC_id_val_creationdate))  
WHERE val = '0'

Résultat => INDEX SCAN, lectures logiques 4479
--Interpretation : Dans ce cas aussi le nombre de pages lues est supérieur 4017, ce n'est donc pas le meilleur index

------------------------------------------------------
--> 3.2 Créons un index non-cluster sur les trois colonnes val,id,creation_date dans cet ordre
------------------------------------------------------
CREATE INDEX IXNC_val_id_creationdate ON T_TEST (val,id,creation_date)
-- Test
SELECT id,val,creation_date 
FROM T_TEST WITH (INDEX(IXNC_val_id_creationdate))  
WHERE val = '0'

Résultat => INDEX SEEK sur IXNC_val_id_creationdate lectures logiques 42
--Interpretation : l'ordre des colonnes est très importante, étant donné que la clause WHERE concerne la colonne val .

------------------------------------------------------
--> 3.3 Créons un index non-cluster sur les trois colonnes val,creation_date et id dans cet ordre
------------------------------------------------------
CREATE INDEX IXNC_val_creationdate_id ON T_TEST (val,creation_date,id)
-- Test
SELECT id,val,creation_date 
FROM T_TEST WITH (INDEX(IXNC_val_creationdate_id))  
WHERE val = '0'

Résultat => INDEX SEEK sur IXNC_val_creationdate_id lectures logiques 42

--Quel index l'optimiseur va choisir ?
SELECT id,val,creation_date 
FROM T_TEST  
WHERE val = '0'

Résultat => INDEX SEEK sur IXNC_val_id_creationdate lectures logiques 42
--Interpretation : l'optimiseur choisit IXNC_val_id_creationdate au lieu de IXNC_val_creationdate_id. Pourquoi ?

------------------------------------------------------
--> 4.1 Créons un index non-cluster sur la colone val avec INCLUDE sur les colonnes respectives id et creation_date
------------------------------------------------------
CREATE INDEX IXNC_val_INCLUDE_id_creationdate ON T_TEST (val) INCLUDE (id,creation_date)  
-- Test  
SELECT id,val,creation_date 
FROM T_TEST WITH (INDEX (IXNC_val_INCLUDE_id_creationdate)) 
WHERE val = '0'

Résultat => INDEX SEEK sur IXNC_val_INCLUDE_id_creationdate, lectures logiques 42

------------------------------------------------------
--> 4.2 Créons un index non-cluster sur la colone val avec INCLUDE sur les colonnes respectives creation_date et id
------------------------------------------------------
CREATE INDEX IXNC_val_INCLUDE_creationdate_id ON T_TEST (val) INCLUDE (creation_date,id)  
-- Test  
SELECT id,val,creation_date 
FROM T_TEST WITH (INDEX (IXNC_val_INCLUDE_creationdate_id)) 
WHERE val = '0' 

Résultat => INDEX SEEK, utilisation de l'index IXNC_val_INCLUDE_creationdate_id. lectures logiques 42

Finalement qu'est ce que l'optimiseur va choisir ?
SELECT id,val,creation_date 
FROM T_TEST  
WHERE val = '0' 

Résultat => INDEX SEEK sur IXNC_val_INCLUDE_id_creationdate lectures logiques 42

------------------------------------------------------
Question : Sur quoi l'otimiseur se base pour choisir l'index IXNC_val_INCLUDE_id_creationdate au lieu de IXNC_val_INCLUDE_creationdate_id ? ou IXNC_val_id_creationdate ? ou IXNC_val_creationdate_id ?
------------------------------------------------------

--Visualisons les statistiques des index
DBCC SHOW_STATISTICS ('T_TEST', IXNC_val_id_creationdate );
DBCC SHOW_STATISTICS ('T_TEST', IXNC_val_creationdate_id);
DBCC SHOW_STATISTICS ('T_TEST', IXNC_val_INCLUDE_id_creationdate);
DBCC SHOW_STATISTICS ('T_TEST', IXNC_val_INCLUDE_creationdate_id);

Questions :
====================
Alors comment expliquer le choix de l'optimiseur ?

Qu'est ce qui permet à l'optimiseur de faire un choix parmi ces quatre possibilités ?

Réponse:
====================

J'ai posté ces questions sur le forum et j'ai eu les réponses des experts sur le sujet.

Dabord mikedavem a remarqué que pour les index couvrants à colonnes INCLUSES l'ordre de création des index influence le choix du moteur de base de données :

pour IXNC_val_INCLUDE_id_creationdate et IXNC_val_INCLUDE_creationdate_id l'optimiseur utilise l'index ayant l'ID le plus petit (l'index utilisé est donc l'index créé en premier).

Ensuite, elsuket , affirme que l'optimiseur choisit l'index avec INCLUDE parce que la clé de l'index est plus petite : le nombre de pages à lire étant plus faible, le parcours est plus rapide.

elsuket a donc reproduit le scénario chez lui et est arrivé à la même conclusion que mikedavem :
la quantité de données à ramener par l'index couvrant à colonnes INCLUSES est moins importante que pour l'index couvrant avec clé contenant toutes les colonnes impliquées dans la requête.

Ce qui nous conduit à tirer ces conclusions dans le cadre de cet exemple :

1. un index couvrant rend une requête plus performante qu'un index non couvrant basé UNIQUEMENT sur les colonnes de la clause WHERE

2. un index couvrant avec colonnes INCLUSES (INCLUDE) est plus performant qu'un index couvrant à colonnes NON INCLUSES

3. Pour un index couvrant à colonnes NON INCLUSES, l'ordre d'enchainement des colonnes dans la clé de l'index est très important !

4. S'il existe des index couvrants à colonne INCLUSE (INCLUDE) en doublon (en triplon, quadruplon,...N-ultiplon), l'optimiseur utilise
l'index ayant le plus petit ID (index_id) dont la valeur est égale à stats_id.

Voici le lien vers la discussion sur ce sujet

Merci à tous ;-)

------------------------------------------------------------------------
Auteur : Etienne ZINZINDOHOUE
------------------------------------------------------------------------

Permalien Laisser un commentaire

Social Bookmarking:

                                     

Commentaires:

Connectez-vous pour vous abonner à cet article:

Flux de commentaires pour cet article : Atom 1.0  RSS 2.0

Cet article n'a pas de Commentaires pour le moment...

Vous devez être identifié pour poster un commentaire.

Liste des blogs

SQL SERVER - Etienne ZINZINDOHOUE

Etienne ZINZINDOHOUE SQL SERVER

Etienne ZINZINDOHOUE

Catégories


Rechercher






<  Mai 2012  >
Lun Mar Mer Jeu Ven Sam Dim
  1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31      

Liens

SQL SERVER 2005

  • [SGBD][SQL Server] Active Directory - SQL SERVER

    Il est possible d'utiliser l'Active Directory (AD) comme source de données, pour mettre à jour des tables d'une base de données SQL SERVER. Les tables en question peuvent contenir des informations relatives aux :
    ¤ employés
    ¤ ordinateurs d'une entreprise (ou d'une organisation)
    ¤ ...
    Dans ce billet nous allons voir comment utiliser l'AD comme source pour mettre à jour une table de la base de données.
    ]suite

    Permalien
  • [SGBD][SQL Server] Afficher les principales options de configuration des bases d'une instance

    Avant toute opération d'optimisation d'une base de données, il est indispensable de vérifier rapidement les options automatiques de configuration de la base. Les incontournables :

    --> AUTO_SHRINK = OFF
    --> AUTO_CREATE_STATISTICS = ON
    --> AUTO_UPDATE_STATISTICS = ON
    --> AUTO_UPDATE_STATISTICS_ASYNC = OFF
    ]suite

    Permalien
  • [SGBD][SQL Server] Audit cache de procédures

    Quelques requêtes pour auditer le cache de procédures
    ]suite

    Permalien
  • [SGBD][SQL Server] AUDIT DES INDEX

    S'il est vrai que les index (les bons) participent à l'amélioration des performances d'une base de données,il ne faut pas perdre de vue que sa maintenance à un coût et donc pénalisant pour la performance. Alors comment trouver, au profit de la performance, le juste milieu ?. Voici une série de requêtes qui permettent de :
    --> Afficher le nombre d'index manquants par base sur une instance
    --> Afficher les index manquants et leur bénéfice
    --> Afficher le coût des index non utilisés pour décider de leur suppression ou pas.
    --> Mettre en perspective les index utilisés et leur coût de maintenance
    ]suite

    Permalien
  • [SGBD][SQL Server] BASCULER TOUTES LES BASES UTILISATEURS EN MODE DE RECUPERATION FULL

    Si en toute connaissance de cause vous décidez de basculer toutes les bases de données utilisateurs d'une instance SQL Server (2005 ou 2008) en mode de récupération FULL alors ce script peut vous éviter de perdre du temps à faire une tâche répétitive...
    ]suite

    Permalien
  • [SGBD][SQL Server] CHAR ou VARCHAR ?

    Pour écconomiser de l'espace de stockage des données des tables, on est souvent confronté au choix du type CHAR ou VARCHAR.
    Pour une colonne dont la longueur est fixe, il n'y a pas de doute sur le choix du type de colonne : c'est CHAR qu'il faut utiliser. Mais lorsque la longueur de la colonne est variable, il faut se poser la question du choix du type de colonne. Ce n'est pas parce que les données dans la colonne varie qu'il faut systématiquement choisir le type VARCHAR.
    Si la longueur de la colonne varie et est majoritairement supérieure à 4 caractères, il faut choisir le type VARCHAR.
    Autrement dit même si la colonne est variable et si la longueur des données est majoritairement inférieure ou égale à 4 caractères il faut choisir le type CHAR. ]suite

    Permalien
  • [SGBD][SQL Server] Clean And Check PhoneNumber

    Dans les "Call Centers", une phase importante de la préparation d'une campagne d'appels sortant (Outbound calls) consiste à passer la table d'appels et plus précisément la colonne "Numéro de téléphone" dans une moulinette afin d'avoir des numéros de téléphones valides. Si la table d'appels contient un nombre important de faux numéros c'est sûr que la campagne ne sera pas fructueuse.
    Voici une fonction SQL qui permet de nettoyer et de valider les numéros de téléphones.

    ]suite

    Permalien
  • [SGBD][SQL Server] Commutateurs /3GB et /PAE

    L'un des objectifs principaux de tous les SGBD est de réduire les entrées/sorties (E/S) disque car les lectures et écritures sur le disque font partie des opérations les plus consommatrices de ressources. SQL Server pour atteindre cet objectif crée un pool de mémoires tampons pour garder les pages de la base de données lues en mémoire.
    De façon standard les systèmes d'exploitation Microsoft Windows 32 bits peuvent adresser une mémoire maximale de 4 Go.
    Dans ce cas, 2Go sont reservés pour l'OS et 2Go pour les applications. La mémoire étant un élément fondamental pour le bon fonctionnement du SGBD, comment permettre au moteur SQL de disposer d'une quantité de mémoire supérieure à 2Go sur un OS 32 bits ? Deux commutateurs permettent de résoudre ce problème afin d'allouer plus de 2Go de mémoire à SQL SERVER, il s'agit de /3GB et /PAE.
    ]suite

    Permalien
  • [SGBD][SQL Server] Configuration CPU - SQL SERVER

    Le nombre et le type de processeur influencent la performance d'un serveur. Une fois le serveur SQL mis en place, il est généralement difficile de décider après de changer de type de CPU ou d'en augmenter le nombre; comme on peut le faire avec les autres composants matériel de la machine : mémoire ou disque dur. En cas de dégradation des performances liées à l'utilisation de CPU que faut-il faire ?
    Quatre approches de solution :

    ¤ Checkup des options avancées relatives à la configuration du CPU.
    ¤ Identifier et optimiser les requêtes les plus consommatrices de CPU : Reécriture des requêtes, création des bons index,..(En général, pour une base de données en production il n'est plus possible de modifier le model de données)

    ¤ Tester l'effet (ON/OFF) de l'Hyperthreading. L'option ON/OFF de l'Hyperthreading se modifie dans le BIOS.
    ¤ Envisager la mise en place du "gouverneur de ressources" (introduit depuis SQL SERVER 2008) pour répartir des ressources mémoires et CPU selon la charge de travail.

    ]suite

    Permalien
  • [SGBD][SQL Server] DATETIME : What You See Is Not What Is Stored

    SQL SERVER 2008 dispose de six types pour stocker les données date/heure :
    ¤ DATETIME
    ¤ SMALLDATETIME
    ¤ DATE
    ¤ TIME
    ¤ DATETIME2
    ¤ DATETIMEOFFSET

    Les types DATETIME,SMALLDATETIME sont disponibles depuis les premières versions de SQL SERVER.
    Par contre les types DATE,TIME,DATETIME2,DATETIMEOFFSET sont disponibles qu'à partir de SQL SERVER 2008.

    ¤ Pour le type DATETIME (stocké sur 8 octets) la date (date de référence 1er janvier 1900) est stockée sur 4 octets
    et l'heure (en clock-ticks) est stockée sur 4 octets.

    ¤ Pour le type SMALLDATETIME (stocké sur 4 octets) la date (date de référence 1er janvier 1900) est stockée sur 2 octets
    et l'heure (en minute pas en clock-ticks comme dans datetime) est stockée sur 2 octets.

    ¤ Pour le type DATETIME2 les choses ne sont pas simples, car :
    • la date de référence est le 1 janvier 0001
    • l'heure (en secondes) est stockée sur un nombre d'octets variable selon la précision

    Examinons en détails comment SQL SERVER gère les types DATETIME
    ]suite

    Permalien
  • [SGBD][SQL Server] déplacer/copier/supprimer un fichier de données

    Lorsqu'on met en place un automate d'import/export de fichier (fichier de campagne d'appels sortants par exemple), on a besoin de :
    --> tester l'existence du fichier d'import/export
    --> faire l'opération d'import/export (pour ça j'utilise l'utilitaire BCP)
    --> déplacer/copier/supprimer/renommer le fichier dans le repertoire d'archivage

    Voici des procédures stockées qui permettent de déplacer/copier/supprimer ces fichiers de données à l'aide du T-SQL.

    ]suite

    Permalien
  • [SGBD][SQL Server] Identifier les login/password non sécurisés

    Identifier les login/password non sécurisés
    ]suite

    Permalien
  • [SGBD][SQL Server] Index non-cluster : choix de l'optimiseur

    L'idée est d'examiner l'utilisation des index non-cluster par l'optimiseur de requête.
    Afin de mettre en évidence le choix du moteur de base données vis à vis des index non-cluster couvrants avec colonnes incluses ou non
    ]suite

    Permalien
  • [SGBD][SQL Server] Index non-cluster : Tipping point ou Zone de basculement

    Le "Tipping point" qu'on peut traduire littérallement par point de basculement, correspond à la situation où un index non cluster n'est pas utilisé par l'optimiseur dans le cas d'une requête SARGable. Je préfère utiliser le terme Zone (ou ligne) de basculement que le terme "point de basculement" (Tipping point) parce qu'il me semble que la notion de point n'existe pas dans une base de données !
    En mathématique, le point de basculement existe. Par exemple lorsqu'on étudie une fonction de second dégré qui est sous la forme de f(x) = ax² + bx + c, on se rend compte que cette fonction possède un point particulier appelé EXTREMUM. Ce dernier apparaît lorsque la dérivée première de la fonction s'annule et change de signe. Là il s'agit bien d'un POINT de basculement!
    Le même phénomène peut être mis en évidence en Physique, lorsqu'on s'interesse à l'étude des trajectoires paraboliques. L'exemple le plus simple dans ce cas est l'étude de la trajectoire d'une balle de basket lorsqu'un joueur fait un tir à 3 points. La trajectoire décrite par la balle est telle que lors du lancement, elle monte, atteint une hauteur maximale puis redescend dans le panier (si le tir est bon !). L'instant où la balle atteint une hauteur maximale (encore appelé flêche) est un POINT de basculement.
    Mais en base de données il n'existe pas de POINT ! il existe des lignes (suite de colonnes).

    Pour revenir à la zone de basculement dans la cas d'une base de données, Kimberly L. Tripp a publié un article sur le sujet.
    Elle a présenté une méthode théorique d'ESTIMATION de cette zone rouge. Disons-le tout de suite, ce n'est qu'une estimation car beaucoup de paramètres peuvent influencer cette zone. La seule façon de savoir si l'optimiseur n'utilsera pas un index non cluster c'est de faire le test et analyser le plan d'exécution.

    Voici néanmoins une requête qui permet d'obtenir pour chaque table d'une base de données les limites théoriques de la zone de basculement
    ]suite

    Permalien
  • [SGBD][SQL Server] Index sur colonne calculée ou vue indexée

    Quels sont les conditions pour créer un index sur une colonne calculée ou une vue ?
    Voici un scénario pour mettre en évidence les pré-réquis.
    ]suite

    Permalien
  • [SGBD][SQL Server] index sur une colonne non déterministe ?

    Peut-on créer un index sur une colonne non déterministe ?
    Qu'est ce qu'une colonne déterministe ou non ? comment les identifier ?
    ]suite

    Permalien
  • [SGBD][SQL Server] Indicateurs de performances : Temps CPU (moins fiable) et le nombre de pages logiques lues

    Généralement le temps CPU et le nombre de pages logiques lues sont les indicateurs de performance d'une requête.
    Pour obtenir les valeurs de ces indicateurs SQL Server met à disposition les commandes :
    SET STATISTICS IO ON  
    SET STATISTICS TIME ON
    Ces commandes fournissent beaucoup d'informations et donc engendrent des fois une perte de temps. Par exemple si on travaille sous SSMS (SQL Server Management Studio), les résultats de la requête sont présentés dans l'onglet "Résultats" et les indicateurs de performances dans l'onglet "Messages". Et il y en a un paquet alors que la plupart du temps c'est le nombre de pages logiques lues qui est l'indicateur le plus fiable car le temps CPU varie de façon aléatoire pour une même requête et pour une même base dont l'état n'a pas changé !
    ]suite

    Permalien
  • [SGBD][SQL Server] Informations sur les requêtes

    Quelles sont les requêtes les plus consommatrices de CPU sur une instance SQL Server?

    Quelles sont les dernières requêtes exécutées sur une instance SQL Server ?

    Quelles sont les requêtes les + fréquemment exécutées sur une instance SQL Server ?
    ]suite

    Permalien
  • [SGBD][SQL Server] Intégrité référentielle - Performance des requêtes

    L'intégrité référentielle ou contrainte de clé étrangère (CONSTRAINT FOREIGN KEY) permet de contrôler la validité, la cohérence et la consistance des données dans une base. Ce mécanisme assure donc une meilleure qualité des données. Elle met également en exergue les liens entre les tables et permet ainsi d'avoir une bonne visibilité de la structure de la base de données. À travers des exemples simples [mais pas choisit au hazard ;-)], nous allons examiner l'impact de l'intégrité référentielle sur la performance des requêtes. Cette analyse va nous conduire à mettre en évidence les situations où on peut être amené à désactiver/activer la contrainte de clé étrangère afin d'optimiser la performance des requêtes.
    ]suite

    Permalien
  • [SGBD][SQL Server] ISO_WEEK

    L'option ISO_WEEK pour la fonction DATEPART n'existe pas sous SQL SERVER 2005. Sous SQL SERVER 2008 et > Cette option retourne le numéro de semaine pour une date, telle que définie par la norme ISO 8601.
    La norme ISO pour la numérotation des semaines impose que :
    --> la semaine numéro 1 d'une année correspond à la semaine où tombe le premier jeudi de l'année.
    --> la semaine commence le lundi

    Cette norme ISO est conforme aux calendriers actuels utilisés dans la plupart des pays européens et africains (certains africains attachés aux rapports directs avec la nature continuent de prendre comme référence du début du mois, l'apparition du croissant de lune ...)

    Voyons concrètement le mécanisme de numérotation ISO pour la semaine et son impact sous SQL SERVER]suite

    Permalien

Divers

Syndiquez ce blog XML

Articles :

Commentaires :

 
 
Developpez.com

Nous contacter

Participez

Informations légales

 
Services

Forum Blogs

Blogs

Hébergement

 
Partenaires

Hébergement Web