Monitoring d'un serveur à base de service broker

Bonjour à tous,

Changement de service + vacances = presque 2 mois sans posts.... :-(

Pour ma première tâche en tant que DBA d'étude chez mon client préféré, j'ai eu l'occasion de retoucher des scripts qui permettent de monitorer un serveur SQL, et ceci en essayant d'impacter un minimum les performances de notre serveur...
En nous focalisant sur les problèmes de LOCK que peut rencontrer notre serveur, nous allons voir une méthode assez efficace et qui, personnellement, m'a permis de voir une première véritable utilisation du service broker de SQL Server 2005...Alors c'est parti :

Il nous faut tout d'abord créer une base de données qui contiendra un filegroup dédié au stockage de la file d'attente service broker...


1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE DATABASE [$(DatabaseMonitoring)] ON  PRIMARY 
( NAME = N'DBMonitoring' , FILENAME=@DefaultData+N'\DBMonitoring_1.mdf' ,SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 20%),
FILEGROUP [QueueStorage]
( NAME = N'DBMonitoring_2', FILENAME=@DefaultData+'\DBMonitoring_2.ndf' ,SIZE = 3072KB , MAXSIZE = 2048GB, FILEGROWTH = 20%)
LOG ON
( NAME = N'DBMonitoring_log', FILENAME=@DefaultLog+'\DBMonitoring.ldf' ,SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
)

ALTER DATABASE [$(DatabaseMonitoring)]
    SET TRUSTWORTHY ON

ALTER DATABASE [$(DatabaseMonitoring)]
    SET RECOVERY SIMPLE

Ensuite, il nous faut activer le service broker sur cette base :

1
2
3
4
5
6
IF NOT EXISTS (SELECT * FROM sys.databases
WHERE name = 'DBMonitoring'
AND IS_BROKER_ENABLED = 1)
BEGIN
ALTER DATABASE $(DatabaseMonitoring) SET ENABLE_BROKER ;
END

Nous créons ensuite les composants essentiels du broker : le service, la queue (sur le bon filegroup)

1
2
3
4
5
6
7
8
9
10
CREATE QUEUE NotifyQueue
ON QueueStorage;
GO

CREATE SERVICE NotifyService
ON QUEUE NotifyQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO

Deux tables nous permettront de stocker les notifications au format brut (notification XML) [DLHistoric] et au format relationnel (découpé en champs) [DLEventsLog].
Sans décrire entièrement la procédure utilisée par le broker, nous allons en décrire les parties intéréssantes :
L'attente des messages :

1
2
3
4
5
6
7
 WAITFOR (
RECEIVE TOP(1)
@message_type_name=message_type_name,
@message_body=message_body,
@dialog = conversation_handle
FROM NotifyQueue
), TIMEOUT 2000

L'information intéréssante :

1
SET @EventType = @message_body.value('(//EventType)[1]','varchar(50)')


Nous capturons les évenements liés aux LOCK en XML :

1
2
IF (@EventType='DEADLOCK_GRAPH ' OR @EventType='LOCK_DEADLOCK' OR @EventType='LOCK_DEADLOCK_CHAIN' OR @EventType='LOCK_ESCALATION') 
    INSERT [DLHistoric] VALUES(getdate(),@EventType,@message_body)

Puis en relationnel :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
IF @EventType='BLOCKED_PROCESS_REPORT'
    BEGIN
      INSERT INTO [dbo].[DLEventsLog]
         ([Session_ID]
         ,[EventTime]
         ,[Blocking_Spid]
         ,[Query_Statement]
         ,[WaitingForRessource]
         ,[Transaction_count]
         ,[ClientApplication]
         ,[Hostname]
         ,[LoginName]
         ,[WaitTime_ms]
         ,[LastBatchStartedTime]
           ,[lastBatchCompletedTime]
         ,[UpdateTime]
         ,[DB_Name]
         ,[lock_mode_request]
         ,[Isolationlevel]
         ,[Status]
         ,[PlanExec]
         ,[Transaction_id]
           ,[xactid]
           ,[BlockedProcesses]
           ,[UserConnections]
          )

    select   @message_body.value('(//blocking-process/process/@spid)[1]', 'int') as Session_ID
        ,@message_body.value('(//PostTime)[1]', 'datetime') as EventTime
        ,0 as Blocking_Spid
        ,@message_body.value('(//blocking-process/process/inputbuf)[1]', 'varchar(max)') as Query_Statement
        ,@message_body.value('(//blocked-process/process/@waitresource)[1]', 'varchar(50)') as WaitingForRessource
        ,@message_body.value('(//blocking-process/process/@transcount)[1]', 'int') as Transaction_count
        ,@message_body.value('(//blocking-process/process/@clientapp)[1]', 'varchar(50)') as ClientApplication
        ,@message_body.value('(//blocking-process/process/@hostname)[1]', 'varchar(50)') as Hostname
        ,@message_body.value('(//blocking-process/process/@loginname)[1]', 'varchar(50)') as LoginName
        ,@message_body.value('(//blocked-process/process/@waittime)[1]', 'int') as WaitTime_ms
        ,@message_body.value('(//blocking-process/process/@lastbatchstarted)[1]', 'datetime') as LastBatchStartedTime
        ,@message_body.value('(//blocking-process/process/@lastbatchcompleted)[1]', 'datetime') as LastBatchCompletedTime
        ,[UpdateTime]=getdate()
        ,db_name(@message_body.value('(//DatabaseID)[1]','int')) as [DB_Name]
        ,@message_body.value('(//blocked-process/process/@lockMode)[1]', 'varchar(50)') as Lock_Mode_Request
        ,@message_body.value('(//blocking-process/process/@isolationlevel)[1]', 'varchar(max)') as IsolationLevel
        ,@message_body.value('(//blocking-process/process/@status)[1]', 'varchar(50)') as [Status]
        ,(select qt.query_plan
          FROM sys.dm_exec_requests qs
          cross apply sys.dm_exec_query_plan(qs.plan_handle) as qt
          where qs.session_id=@message_body.value('(//blocking-process/process/@spid)[1]', 'int'))as PlanExec
        ,@message_body.value('(//TransactionID)[1]', 'int') as [Transaction_ID]
        ,@message_body.value('(//blocking-process/process/@xactid)[1]', 'int') as [xactid]
        ,(select top 1 cntr_value from sys.sysperfinfo where (object_name='SQLServer:General Statistics' OR object_name= (Select('MSSQL$'+convert(varchar,SERVERProperty ('InstanceName'))+':General Statistics'))) and counter_name ='Processes blocked') as BlockedProcesses
        ,(select top 1 cntr_value from sys.sysperfinfo where (object_name='SQLServer:General Statistics' OR object_name= (Select('MSSQL$'+convert(varchar,SERVERProperty ('InstanceName'))+':General Statistics'))) and counter_name ='User Connections') as USerConnections

A noter qu'un IF EXISTS permet à cette table de mettre à jour les événements déjà existants...Vous pouvez néanmoins disposer en parallèle d'une table d'historique (pensez à purger !).

Il nous reste a activer la file du broker :

1
2
3
4
5
6
ALTER QUEUE [dbo].[NotifyQueue]
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = [LogEvents] ,
MAX_QUEUE_READERS = 2,
EXECUTE AS 'DBO'

C'est à l'aide de Notification Services que nous allons lever les évenements de LOCK et ainsi les capturer dans nos files service broker :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE EVENT NOTIFICATION BlockedProcessNotification
  ON SERVER
  FOR BLOCKED_PROCESS_REPORT
  TO SERVICE 'NotifyService', 'current database';
  GO

  CREATE EVENT NOTIFICATION Deadlock_Graph_EventsNotification
  ON SERVER
  FOR DEADLOCK_GRAPH
  TO SERVICE 'NotifyService', 'current database';
  GO

  CREATE EVENT NOTIFICATION LOCK_EscalationNotification
  ON SERVER
  FOR LOCK_ESCALATION
  TO SERVICE 'NotifyService', 'current database';
  GO

Il ne restera plus qu'a créer des rapports ou créer des alertes sur le contenu de nos tables...

Nous verrons une autre fois une autre manière de monitorer de manière efficace un serveur, à l'aide des DMV...

Publié jeudi 5 juin 2008 10:48 par guldan
Classé sous
Ce post vous a plu ? Ajoutez le dans vos favoris pour ne pas perdre de temps à le retrouver le jour où vous en aurez besoin :

Commentaires

# re: Monitoring d'un serveur à base de service broker @ jeudi 5 juin 2008 19:18

Juste une question (et non je ne suis pas casse couille ;o))

Pourquoi avoir mis un WAITFOR(RECEIVE) plutot qu'utilisé l'activation de la file d'attente du Broker ?

Via :

ACTIVATION (STATUS = ON ,            PROCEDURE_NAME = MaSP,

...

christian

# re: Monitoring d'un serveur à base de service broker @ vendredi 6 juin 2008 11:50

La PS sera activée dès l'arrivée d'un premier message puis elle restera active pour les autres messages à l'aide du WAITFOR.

Sinon, j'avoue que je ne connais pas l'impact entre l'activation automatique de la PS et la gestion dans une boucle en attente de messages...

guldan


Les 10 derniers blogs postés

- Merci par Blog de Jérémy Jeanson le 10-01-2019, 20:47

- Office 365: Script PowerShell pour auditer l’usage des Office Groups de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 11:02

- Office 365: Script PowerShell pour auditer l’usage de Microsoft Teams de votre tenant par Blog Technique de Romelard Fabrice le 04-26-2019, 10:39

- Office 365: Script PowerShell pour auditer l’usage de OneDrive for Business de votre tenant par Blog Technique de Romelard Fabrice le 04-25-2019, 15:13

- Office 365: Script PowerShell pour auditer l’usage de SharePoint Online de votre tenant par Blog Technique de Romelard Fabrice le 02-27-2019, 13:39

- Office 365: Script PowerShell pour auditer l’usage d’Exchange Online de votre tenant par Blog Technique de Romelard Fabrice le 02-25-2019, 15:07

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Stream Portal par Blog Technique de Romelard Fabrice le 02-21-2019, 17:56

- Office 365: Script PowerShell pour auditer le contenu de son Office 365 Video Portal par Blog Technique de Romelard Fabrice le 02-18-2019, 18:56

- Office 365: Script PowerShell pour extraire les Audit Log basés sur des filtres fournis par Blog Technique de Romelard Fabrice le 01-28-2019, 16:13

- SharePoint Online: Script PowerShell pour désactiver l’Option IRM des sites SPO non autorisés par Blog Technique de Romelard Fabrice le 12-14-2018, 13:01