Capture Executions of Stored Procedures in SQL Server


By:   |   Updated: 2020-09-16   |   Comments (1)   |   Related: More > Extended Events


Problem

SQL Server database developers would probably agree that capturing stored procedure executions can be essential in understanding what code is running. This is because capturing the procedureís execution can be easily debugged. This, in turn, will definitely help to go into a deep dive on the application’s logic which can facilitate in bug-fixing and new development.

In a previous article, we illustrated how to capture stored procedure calls using SQL Server Profiler. While Profiler is still a very useful and effective tool for many database specialists, it is a depreciated feature according to Microsoft and it will be removed in future versions of SQL Server. Instead, Microsoft recommends using Extended Events. Thus, in this article, we are going to demonstrate how to capture stored procedure calls as we have done in the previous article, but instead of Profiler we will use Extended Events. This material can be helpful especially for those who are used to using Profiler and now are looking at moving to Extended Events.

Solution

Well, our task is to easily capture SQL Server stored procedure calls for debugging. We just need to track a specific procedure call and nothing more. We will avoid collecting additional, unnecessary data and, therefore, we will choose an option that best suits our needs and captures only our problem-related information.

In the previous article, we solved the same problem using SQL Server Profilerís Tuning template. We are going to choose a similar method while using Extended Events. Extended Events is a lightweight performance monitoring system aimed at collecting data for monitoring and troubleshooting SQL Server problems. Moreover, Extended Events is quite flexible and allows us to collect only minimal data which is enough for monitoring and analyzing the problem. Discussing Extended Events in detail is a wide topic and is out of scope of this article. Here, we are just going to use this for capturing stored procedure calls.

So, let’s create a test environment:

USE master
GO 
   
CREATE DATABASE TestDB 
GO 
   
USE TestDB 
GO 
   
CREATE TABLE TestTable 
( 
  ID INT PRIMARY KEY, 
  Val CHAR(1) 
) 
GO 
   
INSERT INTO TestTable(ID, Val) 
VALUES (1,'A'),(2,'B'),(3, 'C') 
GO

We will also create a simple stored procedure that adds data into our TestTable:

CREATE PROCEDURE uspInsertData 
   @pID INT, 
   @pVal CHAR(1), 
   @ResCode VARCHAR(10) OUTPUT 
AS 
BEGIN 
   BEGIN TRY 
  
      BEGIN TRANSACTION 
  
      INSERT INTO TestTable(ID, Val) 
      VALUES (@pID, @pVal) 
  
      COMMIT 
  
      SET @ResCode='OK' 
  
   END TRY 
   BEGIN CATCH 
  
      IF @@TRANCOUNT > 0 
         ROLLBACK 
  
      SET @ResCode='Error' 
  
   END CATCH 
  
END 
GO 

Now, let’s open SQL Server Management Studio (SSMS) and start.

We can locate Extended Events under Management. We can create an Extended Events session with the GUI using either New Session Wizard or New Session. We can find both by right-clicking on Sessions under Extended Events:

new exetended events session

We will choose New Session Wizard to create our Extended Event session. We click Next on the Introduction window and move to the next window:

extended events session wizard

Here, we choose a name for our session and move forward:

extended events session wizard

On the next window, we have an option to choose a predefined template for the event. As we can see, among these templates, there are Profiler equivalent templates. As this article is mainly aimed at those who are going to replace Profiler with Extended Events, these are exactly what we are looking for:

extended events session wizard

We will choose the Tuning template like we did in the previous article while working with Profiler:

extended events session wizard

It is worth mentioning that it is possible to capture stored procedure calls using other templates as well. However, I prefer this template as it contains minimal events related to stored procedure calls. As we can see on the right side of the next page, there are only events selected by default in this template:

extended events session wizard

We can add and remove events using the right and left arrows between the Event Library and Selected Events. We leave it as is and move forward to the next screen where we can pick global fields we want to capture:

extended events session wizard

We will not change anything here and move forward.

On this page, it is important to set the right filter to capture only our stored procedure call. We add a filter and as a field choose sqlserver.sql_text and like_i_sql_unicode_string as the operator. As the value we choose our stored procedure (or part of its name) included in ‘%’ signs as we do when using the T-SQL Like operator:

extended events session wizard

We do not specify any data storage on the next screen and will move to the Summary page:

extended events session wizard

On the summary page, we can generate our event session creation script by clicking Script on the bottom-right corner:

extended events session wizard

We can see the event’s T-SQL script:

extended events script

After clicking Finish, we successfully create the event session:

extended events session wizard

We can find our session under Management > Extended Events > Sessions:

extended events sessions

Now, let’s execute our stored procedure and see what happens:

USE TestDB 
GO 
  
DECLARE @ResCode varchar(10) 
  
EXEC uspInsertData 
  @pID = 4, 
  @pVal = N'D', 
  @ResCode = @ResCode OUTPUT 
  
SELECT @ResCode as N'@ResCode' 
GO

As we can see, the procedure’s call is captured. We can see the execution code of the procedure in the batch_text field:

extended events captured data

If we double-click on that field, we can easily copy the code and paste it into a SSMS query window for debugging. To edit the session, we can right-click on that session in SSMS under Management > Session and change options:

extended events change

As you can see, capturing stored procedure calls with the extended events is simple process and hopefully it will not be difficult for users who used Profiler for many years to feel comfortable with Extended Events as well.

Conclusion

Extended Events can effectively be used for capturing stored procedure calls. There are also special templates similar to SQL Server Profiler’s templates that facilitate the work of those who have worked with Profiler. Considering the fact that Microsoft mentions that Profiler will be removed in future versions of SQL Server, it is important to practice using Extended Events for solving the problems which we used to handle with Profiler.

Next Steps

Please find additional information about the discussed topic by following the links below:



Last Updated: 2020-09-16


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips
Related Resources





Comments For This Article




Wednesday, September 16, 2020 - 6:08:35 AM - Kane Back To Top (86489)
Good concise and easy to follow article. Been a long time user of SQL profiler and getting to know XE so this article goes a long way to help
Thank you


download





Recommended Reading

Query Data from Extended Events in SQL Server

SQL Server 2016 Telemetry Extended Event Session

Overview of XEvent Profiler in SQL Server Management Studio

Start and Stop Extended Events Sessions Automatically Using SQL Server Agent Jobs

Improve SQL Server Extended Events system_health Session








get free sql tips
agree to terms


Learn more about SQL Server tools