[SQL Server] DMV/DMF : présentation basique

Ce billet présente brièvement les DMV (Dynamic Management Views) et les DMF (Dynamic Management Functions).

Présentation des DMV et des DMF

 Notions de DMV et de DMF

Les DMV sont des vues systèmes dynamiques permettant de faire un nombre divers de tâches d’administration au sein de SQL Server (2005 minimum), à savoir : récolte d’informations (dont beaucoup sont volatiles) sur l’état d’une instance et/ou de ses objets (bases de données, vues, tables,…), diagnostic sur les performances… Ces vues dynamiques ont été créées afin de remplacer les vues systèmes des versions antérieures à SQL Server 2005 jugées trop limitées.

Les DMF, quant à elle, sont des fonctions systèmes dynamiques qui ont le même usage que les DMV.

On peut dénombrer principalement 5 orientations des DMV/DMF :

  • DMV/DMF orientées serveur/instance, qui fournissent notamment des informations sur l’état des ressources systèmes (mémoire, I/O,…) de SQL Server, les ordonnanceurs,…
  • Les DMV/DMF orientées base de données, qui donnent des informations telles que la volumétrie de la base, ses fichiers, ses objets et leurs dépendances,…
  • Les DMV/DMF orientées index, qui récolte des informations telles que les statistiques des indexes, leur volumétrie, leur niveau de fragmentation, leur fréquence d’utilisation,…
  • Les DMV/DMF orientées traitements/exécutions, qui offrent des informations sur les requêtes en cours d’exécution, les requêtes les plus (ou moins) coûteuses, les plans d’exécution, les sessions connectées à SQL Server,…
  • Les DMV/DMF orientées haute-disponibilité (log shipping, failover clustering, AlwaysOn,…).

Les DMV/DMF sont stockées au sein du schéma sys de :

  • La base de données master, pour celles qui fournissent des informations au niveau de SQL Server.
  • Une base de données, pour celles qui fournissent des informations au niveau base de données.
Il est intéressant de souligner que les Performance Dashboard Reports utilisent les DMV afin de capturer et générer, sous forme graphique, des informations spécifiques d’une instance ou d’une base de données sur ses activités, ses performances,…

De ce fait, il est possible de capturer le code source T-SQL DMV de chaque rapport de performances à partir du SQL Profiler (avec notamment l’événement SP :StmtCompleted).

De plus, les DMV peuvent être utilisées pour capturer les informations du perfmon, via sys.dm_os_performance_counters

Différence entre les DMV et les DMF

La différence entre une DMV et une DMF est que cette dernière est une fonction qui accepte des paramètres.

Utilisation des DMV/DMF

Pour l’utilisation des DMV/DMF, il est nécessaire de disposer des privilèges :

  • SELECT sur VIEW SERVER STATE pour l’utilisation de requêtes DMV au niveau instance. Syntaxe simple :
GRANT VIEW SERVER STATE TO <Login>
GO
  • SELECT sur VIEW DATABASE STATE pour une utilisation limitée à une base de données :
GRANT VIEW DATABASE STATE TO <Login>
GO

Comme évoqué dans la section précédente, toutes les DMV/DMF sont stockées au sein d’un schéma sys et leur nom est précédé par le mot-clé « dm_ ». De ce fait, lorsque l’on souhaite faire appel à une DMV/DMF, il est nécessaire de préfixer le nom de ladite DMV/DMF avec sys.

Ci-dessous, voici un exemple de DMV utilisée (récupération des informations relatives à la volumétrie de chaque fichier de données d’une base) :

SELECT *
FROM sys.dm_db_file_space_usage
GO

Et ci-dessous, voici un exemple de DMF utilisée :

SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(N'MaBase'),OBJECT_ID(N'dbo.MaTable'),NULL,NULL,'DETAILED')
GO

La DMF, utilisée en guise d’exemple, sys.dm_db_index_physical_stats permet, ici, de récupérer les informations détaillées relatives aux indexes de la table MaTable de la base MaTable.

Pour avoir la liste de toutes les DMV/DMF, vous pouvez executer la requête suivante :

SELECT *
FROM sys.all_objects
WHERE name LIKE 'dm_%'
ORDER BY name
GO

Et pour connaître leur définition :

SELECT OBJECT_DEFINITION(OBJECT_ID('<DMV/DMF>'))
GO

<DMV/DMF> est une DMV ou une DMF. Par exemple, pour la DMV sys.dm_exec_query_stats :

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.dm_exec_query_stats'))
GO

Pour aller plus loin…

Dans ce blog seront abordé différentes façons de récupérer des informations spécifiques pour l’audit de SQL Server, d’une base de données, d’une requête,…

En outre, si vous souhaitez approfondir un peu plus le sujet, vous pouvez jeter un coup d’œil ici : http://msdn.microsoft.com/en-us/library/ms188754.aspx.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s