SQL Server - Extended Events to trace Stored Procedure call

Extended Events enable users to collect necessary data to troubleshoot or identify a performance problem. Extended Events is configurable and scalable, because its a lightweight performance monitoring system that uses minimal performance resources.

Extended Events are replacing the deprecated SQL Trace and SQL Server Profiler features

SQL Server Management Studio provides a graphical user interface for Extended Events to create and modify sessions and display and analyze session data.

In this post I will explain how to create a session to collect data for a particular case, for example, trace execution of an SQL Statement.

Lets start creating our first session.

In SSMS, expand server node > Management > Extended Events > Sessions.

Right click on Sessions node and click New Session...

In New Session dialog > General Page > enter Session name, i.e. MySession

On the Events Page, in the textbox under Events library label, type sql_statement. Event list will be filtered.

Select sql_statement_completed, and click > button to move this event to Selected events list.

Click on the Configure button.

Select Filter (Predicate) tab

Click on the first row of grid and select field sqlserver.sql_text

In Operator column, select like_i_sql_unicode_string

In Value column, type the part of query for which you want to collect data. For example, I have written the name of Stored Procedure (MyProcedue) to collect data whenever this SP is called.

On Data Storage Page, In targets grid.

Select type event_file.

In the below pane, provide a file name where it will write the data.

On the Advanced Page, enter 3 seconds for Maximum dispatch latency.

Click OK.

Extended Events Session is created successfully.

By default the Session is not started (it gives you the option on General Page to start the session at creation, we have not selected that option in this exmaple).

We have to start the session manually by right click and select Start Session.

Once started, our Session is able to catch tracing data whenver the event occurs which we have defined in Events Page. In our example, we are checking for the query text that runs the SP MyProcedure. Try executing the SP a few times and see how this data is captured by the Session.

After the desired event is triggered (i.e. ran the SP MyProcedure in our case), Session has captured the data and stored in the target file which we have defined in Data Storage Page.

Expand the session node, and right click on package0.event_file and select View Target Data.

It will display the grid with event name (sql_statement_completed) and the timestamp when this event has occurred. Click any row to see more details about that particular event.

References:

• Microsoft Docs: Extended events overview
• Microsoft Docs: Quickstart: Extended events in SQL Server

SQL Server : Group multiple row values into comma-separated values

You may need to aggregate a column values for a given group of rows in a comma-separated string.

Lets say you have the following table:

DECLARE @tbl TABLE (JobId INT, TechSkills VARCHAR(100))

INSERT @tbl 
VALUES
(1, '.Net Framework'),
(1, 'C#'),
(1, 'PHP'),
(2, '.Net Core'),
(2, 'Laravel'),
(3, 'Angular'),
(3, 'React')

In this table, we have 3 distinct JobIds with multiple technical skills. If we use simple select statement it will return multiple rows for each JobId.

select * from @tbl

You can use subquery with FOR XML PATH to aggregate multiple row values in a single cell value.

SELECT distinct
    JobId,
    (
        SELECT ',' + TechSkills
        FROM @tbl t2
        WHERE t2.JobId = t1.JobId
        FOR XML PATH('')
    ) Concatenated
FROM @tbl t1

In the output you may notice that each value contains an extra comma at the beginning of the text value.

You can use STUFF function to remove this extra comma at the beginning of text string.

SELECT distinct
    JobId, STUFF(
    (
        SELECT ','+ TechSkills
        FROM @tbl t2
        WHERE t2.JobId = t1.JobId
        FOR XML PATH('')
    ),1,2,'') Concatenated
FROM @tbl t1