SQL Server Real-time Monitoring using WMI classes: Part -2

SQL Server Real-time Monitoring using WMI classes: Part -2

In the first part of this blog series, we discussed that starting with SQL Server 2005, we can use SQL Server agent to capture WMI based alerts.We can insert the alret data in to SQL Server tables and use it as needed .We took the example of Blocking and found how we can get all the information related to blocking without having the need to log on to the problematic SQL Server instance.

In this part we will try to explore how to create more such alerts easily .We will only discuss about SQL Server related events in this post. I will write separate blogs on creating SQL Server alerts for OS events like High CPU, Physical/virtual memory, Process shutdown alerts etc .We will also discuss about enabling Real-time Centralized monitoring where one instance of SQL Server will monitor other SQL Server instances.

In the blocking (see the 1^st part ) example, only 2 parts are worth noticing .Rest all is going to be same in almost all the alerts .These two parts are:

1) SQL Server Alert :In this alert there are 2 variables that we should concentrate on

@wmi_namespace

WMI namespace is like a database in any SQL Server instance .If you want to query the objects inside the SQL Server database you need to fire USE <DBNAME>.Similarly we need to use the WMI namespace before using its objects.If you check the blocking alert, you will see this namespace. After the wack wack (\\) is the server name and the rest is the namespace tree .You can see the same tree if you start >> Run >> wmimgmt.msc >> WMI Control >> Properties >> Security

@wmi_query

Is the query that we are using here to query the namespace (Database).It’s not PL-SQL but called as WQL i.e. Windows Query Language .We will create simple to moderate level WQL queries as we move forward.In the blocking example the WQL query that I have used is

N'SELECT * FROM BLOCKED_PROCESS_REPORT Where Duration <=8000000'

2) Step 1 of the Job : In this part we are reading the alert information and inserting it into the table that we created.

INSERT INTO BLOCKED_PROCESS_REPORT (
[PostTime] ,
[SQLInstance] ,
[Databaseid] ,
[computerName],
[SessionLoginName],
[SPID] ,
[TransactionID] ,
[EventSequence] ,
[objectID] ,
[IndexID] ,
[TextData],
[duration]
)
 
VALUES (
GETDATE(), 
N''$(ESCAPE_NONE(WMI(SQLInstance)))'',
N''$(ESCAPE_NONE(WMI(Databaseid)))'',
N''$(ESCAPE_NONE(WMI(ComputerName)))'',
N''$(ESCAPE_NONE(WMI(SessionLoginname)))'',
N''$(ESCAPE_NONE(WMI(SPID)))'',
N''$(ESCAPE_NONE(WMI(TransactionID)))'',
N''$(ESCAPE_NONE(WMI(EventSequence)))'',
N''$(ESCAPE_NONE(WMI(objectid)))'',
N''$(ESCAPE_NONE(WMI(indexid)))'',
N''$(ESCAPE_SQUOTE(WMI(Textdata)))'',
N''$(ESCAPE_NONE(WMI(Duration)))''
)', 
@database_name=N'ALERTDB', 
@flags=0

Here SQLInstance , Databaseid , etc are event properties .Now you might be wondering why have I used Escape_NONE and Escape_SQUOTE here .These are the SQL Sevrer Agent Escape Macros .For more information please go thourgh this link (not trying to complicate this more  ) .

That is it .Once you know how to manipulate these 2 parts you are done.You can use the same blocking script and just change the:

–    Object names (tables , jobs , alerts)

–    WMI class names

–    Column names you want to select with in the class

For example , instead of blocking if you want to capture the deadlock graph (no need to use any trace flags like 1222 or 1204 ) , make these modifications :

Create table :

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[DeadlockEvents]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[DeadlockEvents]
GO
	 
CREATE TABLE [dbo].[DeadlockEvents] (
[ComputerName] varchar(50),
[SQLInstance] varchar(50),
[AlertTime] [datetime] NOT NULL ,
[DeadlockGraph] [xml],
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_DeadlockEvents_Flag]  DEFAULT ((0))
) ON [PRIMARY]
GO
 
CREATE INDEX [DeadlockEvents_IDX01] ON [dbo].[DeadlockEvents]([AlertTime]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

Use this command in @command variable in step 1 of the job :

@command=N'
 
INSERT INTO DeadlockEvents (
ComputerName, 
SQLInstance,
AlertTime, 
DeadlockGraph
)
 
VALUES (
N''$(ESCAPE_NONE(WMI(ComputerName)))'',
N''$(ESCAPE_NONE(WMI(SQLInstance)))'',
GETDATE(), 
N''$(ESCAPE_NONE(WMI(TextData)))''
)', 
@database_name=N'ALERTDB', 
@flags=0

WMI Query : Use DEADLOCK_GRAPH WMI class when you create the alert

@wmi_query= N'SELECT * FROM DEADLOCK_GRAPH'

Finally ,You can use this link to find out all alerts that can you configure (and I am sure you will spend rest of the week in experimenting with them 🙂 , which is good )

Happy Learning !!

Regards

Abhay Chaudhary

Like us on FaceBook  |  Follow us on Twitter

Follow me on FaceBook | Join the fastest growing SQL Server group on FaceBook