Datafly.pro

Depuis la version 2005, les vues de management dynamique permettent au DBA d'obtenir de nombreuses informations sur la vie de son serveur et ses performances : statistiques d'attentes (Wait States), requêtes coûteuses, utilisation des index ou index manquants, etc... Toutefois, ces informations sont fugitives : SQL Server fait le vide lors du démarrage de l'instance, d'autres informations n'ont comme durée de vie que la présence de la requête en cache. D'autre part, pour obtenir quelques métriques, une collecte de données de compteurs de performance doit être faite avec l'analyseur de performances Windows : un deuxième outil. Il sera donc difficile de corréler les deux informations.

Depuis SQL Server 2008, Microsoft nous propose donc d'utiliser un mécanisme de collecte automatisée des données, avec stockage dans une base. Le Data Collector récolte ainsi les résultats de DMV et de compteurs de performance, les historise sur plusieurs jours ou plusieurs semaines et nous offre toute une série de rapports permettant de les exploiter :

(source blog msdn Bill Ramos)

L'infrastructure de collecte du Data Collector s'appuie sur SSIS pour l'importation de données collectées, elle est planifiée via des jobs de l'agent SQL Server.

Source : Books Online

La base de données

Concernant la base de données, elle est créée dès la configuration avec l'assistant. Une base de données centraliser peut être utilisée pour stocker la collecte de plusieurs serveurs (base de donnée centralisée). La même base de données peut être utilisée pour l'UCP (Utility Control Point, une fonctionnalité de surveillance de l’état de santé centralisée pour plusieurs serveurs SQL). Dans ce cas, si vous configurez la base du Data Collector avant de configurer l’UCP, nommez la base sysutility_mdw. Attention à la volumétrie de la base : vous attendrez ou dépasserez facilement 600 Mo/jour/instance surveillée avec les paramètres de rétention par défaut. La rétention des données est configurable pour chaque collecteur. Par défaut 14 jours pour les statistiques sur les requêtes / 730 jours pour la surveillance des fichiers disque. Un job de purge est créé lors de la configuration : mdw_purge_data(sysutility_mdw). Vous pouvez aussi utiliser la procédure stockée core.sp_purge_data (avec le paramètre @retention_days)

Les collecteurs de données

Il existe 4 types de collecteurs de données (voir msdb.dbo.syscollector_collector_types)

• Generic T-SQL Query Collector Type
• Generic SQL Trace Collector Type
• Query Activity Collector Type
• Performance Counters Collector Type

3 collectes de données sont proposées par défaut :

• L'activité du serveur : s'appuie sur les collecteurs Perf Counters et Generic T-SQL, la collecte est mise en cache sur disque avant d'être importée dans la base
• Statistiques sur les requêtes : s'appuie sur le collecteur Query activity, importation directe
• Utilisation du disque : s'appuie sur le collecteur Generic T-SQL

Mais l'infrastructure est extensible : elle permet de créer et d'y ajouter ses propres collections de données.

Exemple : créer un collecteur personnalisé de type Generic T-SQL.

Ce collecteur va nous permettre de collecter régulièrement des résultats de requête, comme l’interrogation d’une DMV. Pour l’exemple, nous allons collecter ici les informations de fragmentation des index. Ces données seront stockées dans une table de la base de collecte, cette table est créée automatiquement lors de la configuration du collecteur (mais elle ne sera pas détruite si vous supprimez ce collecteur). Elle est créée dans le schéma custom_snapshots et porte le nom du collecteur créé. Une façon de créeer un nouveau collecteur est de générer le script d’un collecteur existant afin de s’inspirer du code.

Voici les différentes étapes :

1ère étape : obtenir le GUID du type de collecteur

DECLARE @collector_type uniqueidentifier
SELECT @collector_type = collector_type_uid
FROM [msdb].[dbo].[syscollector_collector_types]
WHERE name = N'Generic T-SQL Query Collector Type'

2ème étape : définir/obtenir la planification

DECLARE @schedule uniqueidentifier 

SELECT @schedule =schedule_uid FROM msdb.dbo.sysschedules 
WHERE name = N'CollectorSchedule_Every_30min' 

3ème étape : créer le collection set avec la procédure stockée msdb.dbo.sp_syscollector_create_collection_set

DECLARE @collectionsetid int 
DECLARE @collectionsetuid uniqueidentifier 
EXEC msdb.dbo.sp_syscollector_create_collection_set 
@name=N'Index Fragmentation Usage Report', 
@collection_mode=1, -- Non-cached mode 
@description=N'Records fragmentation of indexes over 100 pages', 
@days_until_expiration=400, -- A year and a month 
@schedule_uid=@schedule, 
@collection_set_id=@collectionsetid OUTPUT, 
@collection_set_uid=@collectionsetuid OUTPUT 

4^ème étape : ajouter des éléments à la collection pour définir ce qui va être capturé, avec [msdb].[dbo].[sp_syscollector_create_collection_item].

Syntaxe : sp_syscollector_create_collection_item       [ @collection_set_id = ] collection_set_id     , [ @collector_type_uid = ] 'collector_type_uid'     , [ @name = ] 'name'     , [ [ @frequency = ] frequency ]     , [ @parameters = ] 'parameters'     , [ @collection_item_id = ] collection_item_id OUTPUT

La requête qui extraira les données est passée dans le paramètre @parameter, qui doit être fourni en XML conforme à un schéma spécifique :

Pour notre exemple, l’ajout d’élément sera fait avec la requête suivante : on constitue d'abord la variable @parameters de type XML, puis on ajoute l'item à la collection.:

DECLARE @parameters XML
Set @parameters=CONVERT(XML,
N'<ns:TSQLQueryCollector xmlns:ns="DataCollectorType"><Query><Value>
Select db_name([database_id])+''.''+sch.[Name]+''.''+T.[Name]+''.''+indexes.[Name]
as [IndexName],S.[FragmentationPercentage]
FROM (Select [database_id], [object_id], [index_id], 
Sum(avg_fragmentation_in_percent * page_count)/Sum(page_count)
as [FragmentationPercentage]
FROM
sys.dm_db_index_physical_stats( db_id(), NULL, NULL, NULL,''DETAILED'')
Where
index_type_desc IN (''CLUSTERED INDEX'',''NONCLUSTERED INDEX'')
AND alloc_unit_type_desc=''IN_ROW_DATA''
Group by
[database_id],[object_id],[index_id]
HAVING
Sum(Page_count) &gt; 100 and Sum(avg_fragmentation_in_percent * page_count)
&gt; 0 ) S Join
sys.objects T On S.[object_id] = T.[object_id]
Join sys.schemas sch On sch.[schema_id] = T.[schema_id]
Join sys.indexes On indexes.[object_id] = S.[object_id] 
And indexes.[index_id] = S.[index_id]
</Value>
<OutputTable>IndexFragmentation</OutputTable>
</Query><Databases UseUserDatabases="true"/>
</ns:TSQLQueryCollector>')

SELECT @parameters

DECLARE @collectionitem INT

EXEC [msdb].[dbo].[sp_syscollector_create_collection_item]
@name=N'Index Fragmentation',
@parameters=@parameters,
@collection_item_id=@collectionitem OUTPUT,
@collection_set_id=@collectionsetid,
@collector_type_uid=@collector_type

Pour compléter tout ceci, télécharger la présentation à ce sujet ici :  data_collector.pdf .

Allez-voir aussi à cette adresse, qui présente un collecteur de performance et un ensemble de rapports basés sur les Query Hash Statistics

Bonne collecte de données !