Introduction
Cette fiche technique présente les commandes DBCC disponibles avec SQL
Server. Certaines de ces commandes ne sont pas documentées et sont présentées
ici. Les résultats de ces commandes DBCC apportent une aide précieuse pour le
tuning et l’optimisation des serveurs MS SQL Server.
DBCC CACHESTATS
La commande DBCC CACHESTATS affiche des informations sur les objets en
cache, parmi ces informations :
- Cache hit (pourcentage des objets sollicités et déjà en cache)
- Objets compilés en cache
- Plans compilés en cache
Exemple :
Object Type Hit Ratio Object Count Avg Cost Avg Pages LW Object LW Avg LW Avg LW Avg
Count Cost Stay (ms) Use Count
Proc 0.65 45 0.98 7.22 0 0.0 0.0 0.0
Prepared 1.0 10 1.0 1.0 0 0.0 0.0 0.0
Adhoc 0.81 25 1.0 1.24 0 0.0 0.0 0.0
ReplProc 0.0 0 0.0 0.0 0 0.0 0.0 0.0
Trigger 0.0 0 0.0 0.0 0 0.0 0.0 0.0
View 0.74 3 1.0 5.67 0 0.0 0.0 0.0
Default 0.0 0 0.0 0.0 0 0.0 0.0 0.0
UsrTab 0.0 0 0.0 0.0 0 0.0 0.0 0.0
SysTab 0.93 17 1.0 7.53 0 0.0 0.0 0.0
Check 0.0 0 0.0 0.0 0 0.0 0.0 0.0
Rule 0.0 0 0.0 0.0 0 0.0 0.0 0.0
Summary 0.56 100 0.99 5.11 0 0.0 0.0 0.0Les statistiques rapportées par cette commande indiquent notamment :
Hit ratio: pourcentage de fois où un objet particulier a été trouvé dans le cache de SQL Server. Plus ce pourcentage est élevé, plus les performances sont améliorées.Object Count: nombre total d’objets d’un type spécifié montés en cacheAvg. cost: valeur utilisée par SQL Server qui mesure le coût de compilation d’un plan.Avg. Pages: Nombre total de pages de 8K utilisées en moyenne pour la montée en cache.LW Object Count, LW Avg Cost, WL Avg Stay, LW Avg Use: ces colonnes indiquent les statistiques sur la suppression des objets en cache par le process Lazy Writer. Ces valeurs doivent être le plus faible possibles.
DBCC DROPCLEANBUFFERS
Cette commande DBCC permet de supprimer toutes les données dans le cache de
données de SQL Server. Toutefois il faut garder à l’esprit que cette
commande ne supprime que les buffers propres (clean buffers), non les dirty
buffers (pages modifiées en cache). Aussi avant le lancement de la commande
DBCC DROPCLEANBUFFERS, il est nécessaire d’exécuter au préalable la
commande CHECKPOINT qui lancera l’écriture des dirty buffers sur
disque.
Avec ce procédé, tous les buffers de données sont vidés.
Exemple :
checkpoint
go
dbcc dropcleanbuffers
go DBCC ERRORLOG
Si le service mssqlserver est rarement redémarré, le fichier de log du serveur devient volumineux.
DBCC ERRORLOG permet de créer un nouveau fichier de log dynamiquement avec
création d’archives. Il est d’usage de créer un job qui effectue
cette opération régulièrement (quotidiennement…).
DBCC FLUSHPROCINDB
La commande DBCC FLUSHPROCINDB permet d’effacer les entrées dans le
cache de procédures (plans d’exécutions, etc.) pour une base de
données. L’identifiant de la base de données doit être indiqué dans la
commande.
Cette commande permet de s’assurer que les précédents plans d’exécution des procédures stockées n’affectent pas les tests.
DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM
master.dbo.sysdatabases WHERE name = 'database_name')
DBCC FLUSHPROCINDB (@intDBID)DBCC INDEXDEFRAG (SQL Server 2000 uniquement)
Cette commande DBCC est introduite avec MS SQL Server 2000 pour réduire la
fragmentation à chaud. Lors de l’exécution de cette commande, la
fragmentation est réduite dans verrouiller la table, autorisant ainsi
l’accès à la table par des process. Malheureusement, les résultats de la
défragmentation ne sont pas optimaux par rapport aux résultats obtenus avec la
commande DBCC DBREINDEX qui en revanche verrouille la table, ce qui implique de
disposer de plages horaires d’inactivité.
DBCC INDEXDEFRAG (Database_Name, Table_Name, Index_Name) DBCC FREEPROCCACHE
Commande destinée à vider le cache de procédures stockées pour toutes les bases du serveur SQL Server.
DBCC FREEPROCCACHE DBCC OPENTRAN
Cette commande est utilisée pour identifier la transaction ouverte la plus ancienne dans une base de données spécifique, cette dernière transaction pouvant poser des verrous empêchant les autres utilisateurs d’accéder aux données dans cette base de données.
DBCC OPENTRAN('database_name') DBCC PAGE
Commande utilisée pour visualiser le contenu d’une page de données stockée dans SQL Server.
DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical]) dbid ou dbname |
identifiant ou nom de la base de données en question |
pagenum |
numéro de page à examiner |
print option (optionnel) |
0 (par défaut), 1 ou 2
|
cache (optionnel) |
1 ou 0
|
DBCC PINTABLE et DBCC UNPINTABLE
Par défaut, SQL Server monte automatiquement dans son cache de données les pages nécessaires à ses traitements. Ces pages de données demeurent dans le cache de données jusqu’à ce qu’il n’y ait plus de cache disponible et que ces dernières ne sont plus sollicitées auquel cas ces dernières sont redirigées vers le disque. Ultérieurement, si SQL Server nécessite à nouveau ces pages, une nouvelle montée en cache est effectuée à partir des disques. Si SQL Server peut monter toutes les pages de données en cache, cela favorise les performances en réduisant ainsi les I/Os sur les disques.
Le processus d’épinglage d’une table permet d’indiquer à SQL Server que les pages de données d’une table ne doivent en aucun cas être retirées du cache de données à l’issue de la première lecture. Ceci est particulièrement utile pour les tables de référence et réduire grandement les I/Os disque pour ces dernières qui sont constamment sollicitées dans la durée de vie d’une application.
Pour épingler une table dans le cache de données : DBCC PINTABLE
DBCC PINTABLE (dbid, table_id)Pour "desépingler" une table dans le cache de données : DBCC UNPINTABLE
DBCC UNPINTABLE (dbid, table_id)À l’issue du lancement de dbcc unpintable sur une table, le marqueur
pour cette table est annulé et les pages de données correspondantes peuvent
être vidées par le Lazy Writer lors de son processus classique de gestion du
cache.
DBCC PROCCACHE
Commande qui affiche l’utilisation du cache de procédures stockées.
DBCC PROCCACHEnum proc buffs num proc buffs num proc buffs proc cache size proc cache used proc cache used active active 380 380 -32 519 519 107
num proc buffs |
Nombre de procédures stockées possibles pouvant se trouver dans le cache de procédure. |
num proc buffs used |
Nombre d’emplacements de cache contenant des procédures stockées. |
num proc buffs active |
Nombre d’emplacements de cache contenant des procédures stockées actuellement en cours d’exécution. |
proc cache size |
Taille totale du cache de procédure. |
proc cache used |
Quantité de cache de procédure contenant des procédures stockées. |
proc cache active |
Quantité de cache de procédure contenant des procédures stockées actuellement en cours d’exécution. |
DBCC DBREINDEX
Périodiquement (hebdomadairement ou mensuellement), une réorganisation de tous les indexes de toutes les tables dans une base de données doit être réalisée ainsi les données ne sont plus fragmentées. Des données fragmentées engendrent des lectures de données non nécessaires, ralentissant les performances SQL Server.
Lors de la réorganisation d’une table avec un index clusterisé, tous les indexes non clusterisés de la même table sont également reconstruits.
DBCC DBREINDEX
(['base_de_données.propriétaire.nom_de_la_table' [,
nom_d’index [, facteur_de_remplissage ] ] ] ) [WITH NO_INFOMSGS]Lors de l’utilisation de la commande DBCC DBREINDEX pour reconstruire
les indexes, la table devient indisponible pour les utilisateurs.
Lorsqu’un index non clusterisé est reconstruit, un verrou partagé est
posé sur la table, empêchant tout exceptées les commandes SELECT.
Lorsqu’un index clusterisé est reconstruit, un verrou exclusif est posé, empêchant tout accès à la table.
DBCC SHOWCONTIG
La commande DBCC SHOWCONTIG permet de juger de l’état de fragmentation
de données et des indexes dans une table spécifique, la fragmentation
engendrant des I/Os supplémentaires pénalisant les performances. La
fragmentation se corrige aisément avec la commande dbcc dbreindex.
DBCC SHOWCONTIG (id_table [, id_index])Cette commande fait l’objet d’une documentation spécifique (SQLPAC - Monitorer la fragmentation SQL Server 7 : dbcc showcontig).
DBCC SHOW_STATISTICS
Commande pratique pour déterminer la sélectivité d’un index. Plus la
sélectivité est haute, plus l’index sera pertinent pour
l’optimiseur de requêtes. L’information sur la dernière mise à jour
des statistiques est également retournée par la commande dbcc
show_statistics.
DBCC SHOW_STATISTICS (table_name, index_name)Cette commande donne également les histogrammes des statistiques sur l’index.
DBCC SQLMGRSTATS
La commande DBCC SQLMGRSTATS est destinée plus spécifiquement aux commandes
SQL préparées (commande Transact SQL PREPARE) et aux requêtes ad-hoc traitées au
niveau du cache.
DBCC SQLMGRSTATSItem Status ------------------------- ----------- Memory Used (8k Pages) 5446 Number CSql Objects 29098 Number False Hits 425490
Memory Used (8k pages) |
Si la quantité de mémoire est très élevée, ceci peut indiquer qu’une connexion utilisateur prépare plusieurs commandes T-SQL sans relaxer. |
Number CSql Objects |
Mesure le nombre total de commandes T-SQL mis en cache |
Number False Hits |
Cette valeur rapporte le nombre de fois où le moteur SQL Server n’a pas été en mesure de trouver des commandes T-SQL déjà existantes dans le cache. Cette valeur doit être la plus faible possible. |
DBCC SQLPERF
Cette commande présente des options à la fois documentées et non documentées. Les options disponibles sont :
DBCC SQLPERF(LOGSPACE)
DBCC SQLPERF(LOGSPACE) fournit des statistiques concernant l’utilisation de
l’espace du journal des transactions dans toutes les bases de données.
Database Name Log Size (MB) Log Space Used (%) Status
------------------ ----------------- ------------------- -------------------
dba_db 9.9921875 21.955629 0Le journal des transactions accumule des informations relatives aux
modifications portées sur les données dans chaque base de données. Les
informations renvoyées par DBCC SQLPERF(LOGSPACE) peuvent être utilisées pour
surveiller la quantité d’espace utilisé et cela permet d’indiquer quand
sauvegarder ou tronquer le journal des transactions.
DBCC SQLPERF(UMSSTATS)
L’option UMSSTATS rapporte les statistiques sur le management des
threads SQL Server. Ces données sont retournées par CPU (Scheduler ID)
Statistic Value
-------------------------------- ------------------------
SchedulerID 0.0
num users 18.0
num runnable 0.0
num workers 13.0
idle workers 11.0
work queued 0.0
cntxt switches 2.2994396E+7
cntxt switches(idle) 1.7793976E+7
---------------------------------------------------------
Scheduler ID 1.0
num users 15.0
num runnable 0.0
num workers 13.0
idle workers 10.0
work queued 0.0
cntxt switches 2.4836728E+7
cntxt switches(idle) 1.6275707E+7DBCC SQLPERF(WAITSTATS)
DBCC SQLPERF(WAITSTATS) fournit des données sur les types d’attente
pour les ressources SQL Server, notamment les verrous, les écritures dans le
log (log writes), etc.
DBCC SQLPERF(IOSTATS)
DBCC SQLPERF(IOSTATS) fournit les statistiques sur les lectures et écritures
vers les disques.
Statistic Value
------------------ -----------------------------
Reads Outstanding 0.0
Writes Outstanding 0.0DBCC SQLPERF(THREADS)
La commande DBCC SQLPERF(THREADS) retourne des informations très utiles pour
chaque thread SQL Server : I/O, CPU et utilisation de la mémoire.
Spid Thread ID Status LoginName IO CPU MemUsage
1 sleeping NULL 0 0 15
2 background NULL 0 0 10
3 background NULL 0 0 0
4 sleeping NULL 0 0 0
5 sleeping NULL 0 0 0
6 background NULL 91 0 3
7 3 sleeping CGC\Admin 512 0 277
8 3 runnable CGC\Admin 12 0 2DBCC UPDATEUSAGE
Consigne et corrige les imprécisions dans la table sysindexes, ce qui peut
avoir comme résultat des rapports d’utilisation d’espace incorrecte par la
procédure stockée système sp_spaceused.
Cette commande corrige également les pages de données non réclamées par SQL Server.
DBCC UPDATEUSAGE ({'database_name' | 0} [, 'table_name' [, id_index ]])
[WITH [COUNT_ROWS] [, NO_INFOMSGS ] Un lancement périodique de cette commande est recommandée mais cette dernière peut s’avérer longue en temps d’exécution.