Home > Denali, Extended Events, SQLPS > XEVENT Provider in Denali

XEVENT Provider in Denali

XEVENT provider is new sqlps provider in Denali CTP3. To admit, I was surprised when I saw it. In SQL Server 2008R2 we don’t have any GUI for XEVENT (Extended Events) written by SQL Team and now, in Denali CTP3, we have nice GUI through SSMS and sqlps provider. Nice work.

XEVENT provider is built on SMO Library (Microsoft.SqlServer.Management.XEvent Namespace). I will try to show how to work with XEVENT provider from the beginning. To start to work with XEVENT objects in sqlps, we first need to access XEVENT provider in sqlps. From PS SQLSERVER:\ type CD XEVENT. Then you need to navigate to server instance typing <server name>\<instance name>. When you are at instance level, you can see that XEVENT provider have 2 subfolders: Packages and Sessions.

From Packages subfolder we can access to metadata of all objects that are available on the instance. To be precise, all public metadata, private objects are not visible with XEVENT provider. From Sessions subfolder we can see all available sessions on the system, manage it and create new sessions. We can even generate SQL DDL script for CREATE, DROP and ALTER session.

Let’s start with Packages subfolder. (1)From Packages subfolders we can see all public packages on the system. (2)To access some package ModuleId and Name need to be specified. (3) From the Package we can get all metadata that are available in the package: events, actions, targets…

From there, to list all Events in the package we can type: dir .\EventInfoSet
The same pattern can be used for any object in the package: ActionInfo for Actions, TargetInfo for target etc.
To see Event Fields for some specific Event we can use the code:

# Get Event "sqlserver.scan_started" in $event variable
$event = dir .\EventInfoSet | Where-Object {$_.name -eq "scan_started"}
# Return ReadOnly Event Fields
$event.ReadOnlyEventColumnInfoSet
# Return Data Event Fields
$event.DataEventColumnInfoSet
# Return Customizable Event Fields
$event.EventColumnInfoSet

It’s nice to have possibility to see Extended Events metadata with Powershell but I prefer to use T-SQL script or new GUI in SSMS.

For me, sessions subfolder is much more useful. (1) From Sessions subfolder we can see all available session on that instance, manage it or create new session. (2) On my server, 3 sessions are available. System_health is running and Perf1 and Perf2 are stopped. (3) We can navigate to system_health session and from there (4) we can see all events and targets that are associated to the session.

If you want to see all relevant information (actions, predicates and event fields) for specific event in the system_health session you can use:


dir .\Events | Where-Object {$_.name -eq "sqlserver.error_reported"} | Format-List Name, Actions,PredicateExpression, EventFields

or

$ev = dir .\Events | Where-Object {$_.name -eq "sqlserver.error_reported"}
$ev.Actions
$ev.PredicateExpression
$ev.EventFields

From sessions subfolder, session can be started and stopped.

CD XEVENT\DENALICTP3\DEFAULT\SESSIONS
# Find system_health session
$session = Get-ChildItem | Where-Object {$_.name -eq "system_health"}
# Stop session
$session.Stop()
# Check is session running
$session.IsRunning

If you want to create T-SQL DDL for CREATE EVENT SESSION and DROP EVENT SESSION you can use next code:

$session.ScriptCreate().GetScript()
$session.ScriptDrop().GetScript()

In this post I showed some basics of using XEVENT provider in Denali sqlps. I hope it will help someone.

Advertisements
Categories: Denali, Extended Events, SQLPS
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: