Overview
Similar to using T-SQL, we can create an Extended Events session programmatically using the PowerShell Provider.
Explanation
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 your 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
Now 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()
Additional Information
Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.
- MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017