Create SQL Server Extended Events Session Using SSMS PowerShell Provider


By:
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.

start powershell from SSMS

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()  
Additional Information





Comments For This Article

















get free sql tips
agree to terms