By: Ben Snaidero
Overview
Similar to using TSQL we can create an Extended Events session programmatically using the PowerShell Provider. To start PowerShell simply type sqlps from a command prompt or if you prefer using SSMS, open the Object Explorer and expand the "Management" node and right-click "Extended Events" and select "Start PowerShell" as shown below.
Alternatively, you can start a regular PowerShell session using any method you are familiar with but in this case you will also have to import the sqlserver module so all the cmdlets that support the SQL Server features are available.
Once you have started a PowerShell session you can switch to the XEvent folder under the SQLSERVER drive and list all the packages and sessions configured in you SQL Server instance.
PS SQLSERVER:\XEvent\home-pc\DEFAULT> dir Sessions Name AutoStartUp IsRunning Start Time ---- ----------- --------- ---------- AlwaysOn_health False False First_XEvent_Session False False First_XEvent_Sess... False False First_XEvent_Sess... False False system_health True True 5/8/2019 9:45 AM telemetry_xevents True True 5/8/2019 9:45 AM
NNow that we have a PowerShell session we can write a simple .ps1 script to create our Extended Events session. For this example we will create a session similar to the one we created with the "New Session Wizard". You can read through the comments in the complete script listing below and see that we basically have a command for each item we are adding to the session. As with the other methods for creating a session, you can add as many events, predicate/filters, fields or targets as you require.
import-module sqlservercd SQLSERVER:
cd XEvent
$h = hostname
cd $h
# set the default instance as location to create the XEvent session
$store = dir | where {$_.DisplayName -ieq 'default'}
# name the instance
$session = new-object Microsoft.SqlServer.Management.XEvent.Session -argumentlist $store, "First_XEvent_Session_Powershell"
# add the deadlock event
$event = $session.AddEvent("sqlserver.database_xml_deadlock_report")
# add a global field
$event.AddAction("package0.callstack")
# set a filter predicate
$event.PredicateExpression="([sqlserver].[database_name]=N'Production')"
# add a target
$session.AddTarget("package0.ring_buffer")
# create the session
$session.Create()
