Définir ses propres compteurs de performances

SQL Server donne la possibilité de définir ses propres compteurs de performances en complément des compteurs installés par défaut. Il est possible de définir jusqu'à 10 compteurs de performances personnalisés. Il est ainsi possible de définir ses propres critères de performances. Les compteurs sont valorisés par l'intermédiaire des procédures stockées sp_user_counter1 à sp_user_counter10.

Afin de donner un certains dynamisme à ces compteurs, il est nécessaire de planifier un travail (sous la responsabilité de SQL Server Agent) qui va exécuter la mise à jour de la valeur du compteur.

Par exemple si l'on souhaite que le compteur numéro 1 représente la taille utilisé par le groupe de fichier PRIMARY de la base test la requête suivante peut être exécutée:

select sum(df.size)
  from sys.database_files df inner join sys.data_spaces ds
  on ds.data_space_id=df.data_space_id
  where ds.name='PRIMARY';		
			

Le résultat est alors exprimé en nombre de pages. Comme chaque page représente 8Ko d'informations la taille en kilo-octets sera donc donnée à l'aide de la requête suivante:

select sum(df.size)*8
  from sys.database_files df inner join sys.data_spaces ds
  on ds.data_space_id=df.data_space_id
  where ds.name='PRIMARY';
			

Le compteur numero 1 doit représenter cette valeur pour cela il faut stocker le résultat de cette requête dans une variable puis passer cette variable en paramètre de la procédure sp_user_counter1, ce qui peut être défini avec le script suivant:

declare @taille int 
select @taille=sum(df.size)*8
  from sys.database_files df inner join sys.data_spaces ds
  on ds.data_space_id=df.data_space_id
  where ds.name='PRIMARY';
exec sp_user_counter1 @taille			
			

Maintenant il semble difficile de laisser cela sous forme de script, il est donc nécessaire de planifier l'exécution de cette tâche de façon régulière. Pour cela après avoir démarré le service SQL Server Agent, un nouveau travail va être défini de la façon suivante. La planification de ce travail est définie pour une exécution toutes les heures entre 6h00 et 22h00 du lundi au dimanche.

declare @jobID Uniqueidentifier
exec msdb.dbo.sp_add_job 'Les compteurs de performances', @job_ID=@jobID OUTPUT

exec msdb.dbo.sp_add_jobstep @job_id=@jobID, @step_name='Compteur1',@subsystem=N'TSQL',
@command='declare @taille int ;select @taille=sum(df.size)*8  from sys.database_files df inner join sys.data_spaces ds  on ds.data_space_id=df.data_space_id  where ds.name=''PRIMARY'';exec sp_user_counter1 @taille'
-- Associer le travail au server local
exec msdb.dbo.sp_add_jobserver @jobID, @server_name=N'(LOCAL)'
-- definir une planification toutes les heures
exec msdb.dbo.sp_add_jobschedule @job_id=@jobID,@name='Planification horaire',
	@enabled=1, /* Actif */
	@freq_type=4, /* Quotidien */
	@freq_interval=1, /* Tous les jours de la semaine */
	@freq_subday_type=0x8,	/*unité = heure */
	@freq_subday_interval=1,	/* Toute les heures*/
	@active_start_time=060000,	/* début à 6h00 */
	@active_end_time=220000		/* fin à 22h00 */