Commandes DBCC SQL Server pour le tuning

Logo

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.0

Les 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 cache
  • Avg. 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
  • 0 : affiche uniquement les informations sur l’entête de page
  • 1 : affiche les informations sur l’entête de page, chaque ligne et l’offset de table pour la page, les lignes sont séparées l’une de l’autre.
  • 2 : mêmes informations que l’option 1 mais les lignes ne sont pas séparées et sont affichées dans un bloc unique.
cache (optionnel) 1 ou 0
  • 0 : dbcc page extrait préférentiellement le numéro de page à partir du disque même si cette dernière est en cache
  • 1 : dbcc page extrait préférentiellement le numéro de page à partir du cache si cette dernière y est présente

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 PROCCACHE
num 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 SQLMGRSTATS
Item                                  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           0

Le 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+7

DBCC 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.0

DBCC 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        2

DBCC 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.