|
|
Article complet: AUDIT DES INDEX
06/07/2010
, zinzineti [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
/*========================================================================================================================
-- Titre : Nombre d'index manquants par base de données
-- Desciption : Afficher le nombre d'indexes manquants par bases de données
-- Auteur : Etienne ZINZINDOHOUE
========================================================================================================================*/
SELECT
DB_NAME(database_id) AS [Base]
,count(*) AS [Nombre Indexes Manquants]
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY [Nombre Indexes Manquants] DESC;
/*========================================================================================================================
-- Titre : Quel est le bénéfice d'un index manquant ?
-- Desciption : Afficher les 20 premiers index manquants et leurs bénéfices
-- Auteur : Etienne ZINZINDOHOUE
=========================================================================================================================*/
SELECT TOP 20
ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)*0.01,1) AS [Bénéfice Cumulé (%)] --Bénéfice cumulé pour les requêtes utilisateurs
,avg_user_impact AS [Bénéfice Moyen (%)] --Bénéfice moyen (en pourcentage) dont les requêtes utilisateur pourraient tirer parti si ce groupe d'index manquants était implémenté. Cela signifie que le coût des requêtes diminuerait, en moyenne, de la valeur de ce pourcentage si ce groupe d'index manquants était implémenté.
, [statement] AS [Table]
, equality_columns AS [ListeColonnesEgalite] -- Liste de colonnes, séparées par des virgules, qui contribuent aux prédicats d'égalité au format :table.column = constant_value
, inequality_columns AS [ListeColonnesInegalite] -- Liste de colonnes, séparées par des virgules, qui contribuent aux prédicats d'inégalité, par exemple, les prédicats au format : table.column > constant_value
, included_columns AS [ListeColonnesCouverture] -- Liste de colonnes, séparées par des virgules, requises comme colonnes de couverture pour la requête
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Bénéfice Cumulé (%)] DESC;
/*========================================================================================================================
-- Titre : Coût des index non utilisés
-- Description : Afficher les 20 premiers index non utilisés et leur coût de maintenance. ces index non utilisés faut-il les supprimés ?
-- Auteur : Etienne ZINZINDOHOUE
========================================================================================================================*/
--Les variables
DECLARE @Nom_BaseEnCoursTraitement VARCHAR(50),@ID_BaseEnCoursTraitement VARCHAR(50), @SQL VARCHAR (max), @DEL VARCHAR (max)
--Créer et alimenter la table #LISTE_BASES avec le nom des bases (sauf les bases systèmes) de l'instance
SELECT database_id,name
INTO #LISTE_BASES
FROM sys.databases
WHERE name not in ('master','model','msdb','temp')
ORDER BY [database_id]
--Créer la table #TempIndexesInutilises des indexes non utilisés
SELECT TOP 1
DB_NAME() AS [Base]
,OBJECT_NAME(s.[object_id]) AS [Table]
,i.name AS [Index]
,user_updates AS [UserRequetesUpdates]
,system_updates AS [SystemRequetesUpdates]
INTO #TempIndexesInutilises
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
AND user_seeks = 0
AND user_scans = 0
AND user_lookups = 0
AND s.[object_id] = - 333 -- Numéro bidon, c'est juste pour recupérer la structure de la table.
;
-- Tant qu'il y a des bases à traitées
WHILE EXISTS (SELECT database_id FROM #LISTE_BASES )
BEGIN
--Se positionner sur la 1ère ligne de la table temporaire
SET @ID_BaseEnCoursTraitement = CAST((SELECT TOP 1 database_id FROM #LISTE_BASES)AS int)
SET @Nom_BaseEnCoursTraitement = (SELECT TOP 1 [name] FROM #LISTE_BASES)
-- Insert les infos de la base en cours de traitement dans la table de sortie
SET @SQL = N'USE '+ @Nom_BaseEnCoursTraitement + '; INSERT INTO #TempIndexesInutilises
SELECT TOP 20
DB_NAME()
,OBJECT_NAME(s.[object_id])
,i.name
,user_updates -- Nombre de mises à jour par requête utilisateur
,system_updates -- Nombre de mises à jour par requête système
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.database_id = ' + @ID_BaseEnCoursTraitement +'
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 -- objet créé lors de l''installation de SQL Server -- 0 = FAUX et 1 = Vrai
AND s.user_seeks = 0 -- Nombre de recherches résultant de requêtes utilisateur pour lesquelles l''index recommandé du groupe pourrait avoir été utilisé
AND s.user_scans = 0 -- Nombre d''analyses résultant de requêtes utilisateur pour lesquelles l''index recommandé du groupe pourrait avoir été utilisé
AND s.user_lookups = 0 -- Nombre de recherches de signets par les requêtes utilisateur
AND i.name IS NOT NULL
ORDER BY user_updates DESC ;'
EXEC (@SQL);
-- Supprimer la base traitée de la table temporaire
SET @DEL = 'DELETE FROM #LISTE_BASES WHERE database_id = '+ @ID_BaseEnCoursTraitement +';'
EXEC (@DEL)
END
-- Afficher les résultats
SELECT TOP 20 [Base]
,[Table]
,[Index]
,[UserRequetesUpdates] AS [MAJ index par requête utilisateur]
,[SystemRequetesUpdates] AS [MAJ index par requête système]
FROM #TempIndexesInutilises ORDER BY [UserRequetesUpdates] DESC
--Supprimer les tables temporaires
DROP TABLE #LISTE_BASES
DROP TABLE #TempIndexesInutilises
Remarques : /!\ Pas de précipitation ! Avant de supprimer un index qui n'est pas utilisé, il faut d'abord bien se renseigné pour savoir à quel moment cet index pourrait être utile. Attention donc !
/*========================================================================================================================
-- Titre : Mise en perspective de l'utilisation d'un index et son coût de maintenance
-- Description : S'il est vrai que les index (les bons) participent à l'amélioration des performances d'une base de donnée il ne faut pas perdre de vue que leurs maintenances a un coût et donc peuvent pénaliser les performances. Alors comment trouver, au profit de la performance, le juste milieu ?
-- Auteur : Etienne ZINZINDOHOUE
========================================================================================================================*/
DECLARE @Nom_BaseEnCoursTraitement VARCHAR(50),@ID_BaseEnCoursTraitement VARCHAR(50) , @SQL VARCHAR (max), @DEL VARCHAR (max)
--Créer la table #LISTE_BASES liste des bases
SELECT database_id,name
-- Alimenter la table liste des bases #LISTE_BASES
INTO #LISTE_BASES
FROM sys.databases
WHERE name not in ('master','model','msdb','temp')
ORDER BY [database_id]
--Créer la table #TempIndexesUtilises des indexes non utilisés
SELECT TOP 1
(user_updates + system_updates) AS [Coût Maintenance]
,(user_seeks + user_scans + user_lookups) AS [Fréquence Utilisation]
,DB_NAME() AS [Base]
,OBJECT_NAME(s.[object_id]) AS [Table]
,i.name AS [Index]
INTO #TempIndexesUtilises
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], 'IsMSShipped') = 0
AND (user_updates + system_updates) > 0 -- (Nombre de mises à jour par requête utilisateur + Nombre de mises à jour par requête système) >0
AND s.[object_id] = - 333 -- Juste pour recuperer la structure de la table.
;
-- Tant qu'il y a des bases à traitées
WHILE EXISTS (SELECT database_id FROM #LISTE_BASES )
BEGIN
--Se positionner sur la 1ère ligne de la table temporaire
SET @ID_BaseEnCoursTraitement = CAST((SELECT TOP 1 database_id FROM #LISTE_BASES)AS int)
SET @Nom_BaseEnCoursTraitement = (SELECT TOP 1 [name] FROM #LISTE_BASES)
-- Insert les infos de la table en cours de traitement dans la table de sortie
SET @SQL = N'USE '+ @Nom_BaseEnCoursTraitement + '; INSERT INTO #TempIndexesUtilises
SELECT TOP 20
(user_updates + system_updates) AS [Coût Maintenance] -- (Nombre de mises à jour par requête utilisateur + Nombre de mises à jour par requête système) >0
,(user_seeks + user_scans + user_lookups) AS [Fréquence Utilisation]-- Nombre de recherches résultant de : requêtes utilisateurs,requêtes d''analyses et de signets
,DB_NAME() AS [Base]
,OBJECT_NAME(s.[object_id]) AS [Table]
,i.name AS [Index]
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.database_id = ' + @ID_BaseEnCoursTraitement +'
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 -- objet créé lors de l''installation de SQL Server -- 0 = FAUX et 1 = Vrai
AND (user_updates + system_updates) > 0 -- (Nombre de mises à jour par requête utilisateur + Nombre de mises à jour par requête système) >0
ORDER BY user_updates DESC ;'
EXEC (@SQL);
-- Supprimer la base traitée de la table temporaire
SET @DEL = 'DELETE FROM #LISTE_BASES WHERE database_id = '+ @ID_BaseEnCoursTraitement +';'
EXEC (@DEL)
END
-- Mettre en perspective le coût de la maintenance et l'utilité
SELECT TOP 20 DENSE_RANK() OVER(ORDER BY [Coût Maintenance]DESC) AS [Classement Coût Maintenance]
,DENSE_RANK() OVER(ORDER BY [Fréquence Utilisation] DESC) AS [Classement Fréquence Utilisation]
,[Coût Maintenance]
,[Fréquence Utilisation],[Base],[Table],[Index]
FROM #TempIndexesUtilises
ORDER BY [Coût Maintenance] DESC
--Supprimer les tables temporaires
DROP TABLE #LISTE_BASES
DROP TABLE #TempIndexesUtilises
Maintenance des index
===============================================
Pour la maintenance des index SQLPro a fait un travail génial à ce sujet
Merci à SQLPro ;-)
Social Bookmarking:
Commentaires:
Je trouve cet article très intéressant, il m'a permis de découvrir un certain nombre de chose sur le thème "Pourquoi mon serveur rame".
J'aurais aimé avoir quelques détails sur la requête "Titre : Quel est le bénéfice d'un index manquant ?".
Il me sort effectivement la liste des index manquants avec un bénéfice moyen à 99% pour une bonne dizaine d'entre elle.
Ma question est simple, en fonction des champs fournis dans les 3 colonnes, quelles sont les actions à mener pour créer les bons index ?
Car j'ai effectivement des champs retournés par la requête sur lesquels j'ai déjà des index (mais peut être mal déclarés/paramétrés). Et étant relativement novice sur le sujet j'ai un peu de mal à voir quelles modifications sont à faire pour optimiser la chose.
Merci d'avance.
22/12/2010 @ 12:11 http://technet.microsoft.com/fr-fr/library/ms345405%28SQL.90%29.aspx
22/12/2010 @ 17:30 0.) relever initial des indicateurs de performances des requêtes à optimisées
1.) créer les index sur les bonnes colonnes
2.) tester,analyser, relever à nouveau les indicateurs de performances des requêtes
3.) approuver (ou non) l'efficacité des index créés
A+
04/01/2011 @ 10:23 Dans mon cas nous utilisons une suite logicielle achetée, donc je ne dispose que d'informations partielles sur les requêtes réellement utilisées par l'application. L'éditeur n'ayant pas de ressource DBA sur SqlServer, je me retrouve seul à gérer le problème des performances de mon serveur.
Avec la requête "Quel est le bénéfice d'un index manquant ?", j'ai pu identifier plus de 120 index manquants que j'ai rajouter en suivant l'article microsoft cité précédemment. Le gain c'est mesuré immédiatement en exploitation. Initialement, je me doutais bien que l'éditeur n'avait quasiment rien optimiser et qu'il me fallait rajouter des index, sans pour autant savoir comment m'y prendre.
J'ai cependant deux questions concernant les autres requêtes d'optimisation :
- Sur la requête "Coût des index non utilisés", j'ai du mal à cerner ce que représente la valeur "[MAJ index par requête utilisateur] ". (en ce qui me concerne seule cette colonne a une valeur, "[MAJ index par requête système]" est à zéro)
- Sur la requête de l'utilisation d'un index et son coût de maintenance, je me retrouve avec des résultats sur certaines tables, pour lesquelles aucun index n'est cité (Ex : 1,1,16107,121786,,,NULL).
N'étant pas DBA (mais ayant récupérer ce rôle), j'ai du mal à interpréter ce résultat.
09/02/2011 @ 11:45 ----------------
>> Pour savoir les requêtes exécutées par l'application, tu peux utiliser SQL Server Profiler afin de tracer tout ce qui se passe sur le serveur de base de données.
Tu peux aussi utiliser les DMVs/DMFs afin de cibler les requêtes en fonctions des indicateurs de performances que tu souhaites. J'ai écrit des requêtes qui te permettent de savoir par exemple :
- les requêtes les plus consommatrices de CPU
- les requêtes les + fréquemment exécutées
- les requêtes les + coûteuses en I/O logique (Lecture/Ecriture de données en mémoire)
-....
Tu peux trouver ces requêtes à l'Url : http://blog.developpez.com/zinzineti/p9075/sql-server-2005/informations-sur-les-requetes/
----------------
Avec la requête "Quel est le bénéfice d'un index manquant ?", j'ai pu identifier plus de 120 index manquants que j'ai rajouter en suivant l'article microsoft cité précédemment. Le gain c'est mesuré immédiatement en exploitation. Initialement, je me doutais bien que l'éditeur n'avait quasiment rien optimiser et qu'il me fallait rajouter des index, sans pour autant savoir comment m'y prendre.
----------------
>> Il faut mettre en place un plan de maintenance des index crées.
----------------
- Sur la requête "Coût des index non utilisés", j'ai du mal à cerner ce que représente la valeur "[MAJ index par requête utilisateur] ". (en ce qui me concerne seule cette colonne a une valeur, "[MAJ index par requête système]" est à zéro)
----------------
>> un index créé et qui n'est pas utilisé est coûteux pour les performances : occupation mémoire, disque,.... c'est un peu comme si vous recrutez quelqu'un pour faire un travail et que vous vous rendez compte du fait que cette personne recrutée ne fait pas l'affaire. Qu'allez vous faire dans ce cas ? je vous laisse prendre la décision qui vous semble être juste. ;-)
Noté qu'à chaque utilisation d'un index les statistiques, la date de dernière utilisation, .... sont mis à jour. Plus un index est MAJ celà veut qu'il est utilisé.
Si c'est ton application qui utilise l'index lors de l'exécution d'une requête =>"[MAJ index par requête utilisateur] "
Si des requêtes systèmes (procédures systèmes, fonctions systèmes, ...) utilisent en interne un index =>"[MAJ index par requête système]"
----------------
- Sur la requête de l'utilisation d'un index et son coût de maintenance, je me retrouve avec des résultats sur certaines tables, pour lesquelles aucun index n'est cité (Ex : 1,1,16107,121786,,,NULL).
----------------
>> Si aucun index n'est cité => ne correspond pas des index que tu as créé. Pas de souci ! les valeurs ,,,NULL affichées concernent les colonnes [Base],[Table],[Index] (données utilisateurs et non systèmes).
A+
12/02/2011 @ 16:58 Vous devez être identifié pour poster un commentaire.
Liste des blogs
SQL SERVER - Etienne ZINZINDOHOUE
Etienne ZINZINDOHOUE SQL SERVER
Etienne ZINZINDOHOUE
Catégories
Rechercher
| 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
- [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
- [SGBD][SQL Server] Audit cache de procédures
Quelques requêtes pour auditer le cache de procédures
]suite
- [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
- [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
- [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
- [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.
- [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
- [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
- [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
¤ DATETIMEOFFSETLes 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écisionExaminons en détails comment SQL SERVER gère les types DATETIME
]suite
- [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'archivageVoici des procédures stockées qui permettent de déplacer/copier/supprimer ces fichiers de données à l'aide du T-SQL.
- [SGBD][SQL Server] Identifier les login/password non sécurisés
Identifier les login/password non sécurisés
]suite
- [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
- [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
- [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
- [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
- [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
- [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
- [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
- [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 lundiCette 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
Divers
Syndiquez ce blog 
Articles :
Commentaires :
![[Valid RSS]](/img/valid-rss.png "Validate my RSS feed")
![[Valid Atom 1.0]](/img/valid-atom.png "Validate my Atom 1.0 feed")
Copyright © 2000-2012 - www.developpez.com