SQL Server Denali CTP1 – PowerShell and XEvents

Just having a look at PowerShell support in SQL Server Denali CTP1. In Denali you must install PowerShell V2 or it will be installed as part of the installation process. Although the minishell architecture of SQLPS is unchanged so far. Rumor is that changing this (maybe into a module, like Chad Miller did?) might in the offing.

Not new cmdlets yet, unless I'm missing something, but there are enhancements to the provider. There are two new "subdirectories" in the provider, under the SQLSERVER: psdrive: XEvent and IntegrationServices. XEvent has two subdirectories, Packages and Sessions. Packages is a hierarchical rendering of metadata; the information you'd get from the sys.dm_xe_* metadata views. Sessions is a hierarchical rendering of information about currently defined sessions.The IntegrationServices subdirectory contains a Packages subdirectory (after the subdirectory specifying server\instance, naturally).

Having XEvents in the provides means that there is, of course, a set of classes that encapsulate XEvents in SMO. This was something that was missing in SQL Server 2008.So Jonathan Kehayias wrote one for his Extended Events Manager And where there is SMO, there is likely a set of dialogs in SSMS. Well, not really… there's a node off of the Management Node in SSMS and you can import event sessions from XML files. Not quite Extended Events Manager.

By browsing through the hierarchy (or looking at the sys.dm_xe_* views, you can also observe that XEvents has been greatly expanded in SQL Server 2011 CTP1. At the top level, there are three new XEvent packages, ucs, sqlclr, and a second sqlserver (same name but different package id). XEvent events in Denali have increased in number from 254 to 449; almost twice as many items can be tracked by extended events. I selected each event metadata set into tables on both releases and did the SQL equivalent of a diff, there's something new in almost every kind of metadata. For example There is a target called the "router" target that "routes events to listeners". But…routes to whom?

There may be a hint in two new DMVs for extended events, dbo.trace_xe_event_map and dbo.trace_xe_action_map. These DMVs map XEvent events to profiler events and XEvent actions to profiler columns. Perhaps an XEvent-based/profiler trace synergy of some kind?

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.