I’ve been using Profiler to explore Dynamics SL’s database calls for years. Recently, I learned about Extended Events, which is Microsoft’s replacement for Profiler. They’ve done an excellent and, for me, compelling job of replacing Profiler. I’ll abbreviate Extended Events as XEvent(s) or XE below.
If you download SSMS 17 from the Microsoft web site, you’ll see something called XEvent Profiler at the bottom of Object Explorer. There are two pre-configured sessions to watch live events. No need to launch an external application anymore! Double-click one and you can watch the data stream. SSMS 17 works with SQL 2008-2017.
You can also create an Extended Events session under the SSMS Management node, starting with SSMS for SQL Server 2012. I like to right-click the Sessions node under Extended Events and create a New Session, rather than use the Wizard. Later, you can right-click the session, select “Properties”, and you’re right back in the same editor you used to create it. In SSMS 17 all but one of the Profiler templates are present starting with SQL Server 2012, and I never used the one that’s missing (TSQL_Grouped).
One reason I’ve heard for continuing to use Profiler among Dynamics SL consultants is that Dynamics SL has provided templates to use with Profiler. There’s also a helpful YouTube video on how to use a template. I’d recommend you check with them before using the solution below as an alternative for a support incident. But if you’re an I.T. or D.B.A. end-user or a consultant who will evaluate the results yourself, why not give XEvents a try?
I imported the SQL Server 2012 template to Profiler, then created a spreadsheet that listed all the events and “event columns”, as Profiler calls them. After that, I found their Extended Event equivalents. The results are attached in a link to this post, titled DslXEventProfileEquivalents. The equivalent events and actions (which map to Profiler event columns) are included on the second tab as comments. Hover over the triangle in the upper-right corner of the row and column header cells to see the equivalent. In the spreadsheet comments, the XE “Package” name comes before the dot. For example, the Profiler event column “TextData” is the XE action (or event field) “sqlserver.sql_text”. The XE package is “sqlserver”. The event field is “sql_text”. I placed an X in each spreadsheet cell where the template cell is checked in Profiler.
I used the results to create an Extended Event session that is equivalent to the Profiler template. I exported it to a script, which I’ve also attached, titled DynamicsSLXESession. It’s a zip file, which you’ll need to unzip. WordPress won’t allow a straight SQL file as an attachment. After you unzip it, just execute the SQL Script, and it will create an Extended Event session named “DynamicsSLDiagnostic”. You can execute it on SQL Server 2012 or later.
Personally, I’ve never used the Dynamics SL Profiler template to troubleshoot or explore SL. It collects far more data than I need, or am interested in reviewing. I tend to create a SQL-focused trace and add just the events and actions I need. I also filter to limit the results the XE session returns. When I’m hunting for the needle, I want the smallest haystack.
If you’ve used the Dynamics SL Profiler template, I’d be interested in your thoughts about this Extended Event session. Did I miss, or misinterpret anything? Do you find it useful? How could it be improved? You can add a comment below, or use the contact form to get in touch. I’ll improve the attached XE session based on constructive feedback.
Great post, Delmer!