Using SQL Server Extended Events to debug applications

Introduction

Often enough, multilayer software has bugs. SQL Server Extended Events system offers tools that can help find those bugs. A bug can happen in any layer – data, logic, or presentation. To fix those bugs, it helps to see the exact parameters and values that the presentation layer sends to the data layer. As a data layer product, SQL Server Extended Events can help with this.

The Demo Application

For this article, we’ll work with a VB.net desktop application that I built for a customer. The application handles recipe management. The user sees a presentation layer that front-ends a locally hosted, completely relational SQL Server 2014 Enterprise database. First, we’ll see a bug in the Application Recipe Edit Module. Then, we’ll use the SQL Server Extended Events tool to understand and fix the bug.

The Bug

We’ll edit a recipe in the Recipe Edit module, as seen in this screenshot:

In the lower right Recipe Text control, we’ll edit the recipe text, as seen in the screenshot below:

The recipe text shown here has the exact content, formatting, casing, etc. that we want. We’ll click the Save Recipe button at the lower right to save the changes. Sometime later, we’ll try to edit the recipe again. We’ll again open it in the Recipe Edit module, as seen in this screenshot:

That previous edit shows the correct text, except now the recipe text has all upper casing. We just found a bug, and we need to fix it.

Investigating the bug

The Recipe Edit module calls the UPDATE_RECIPE stored procedure to update a recipe. We can call this stored procedure in a Query Analyzer, with parameters that match this test case. First, we’ll manually roll back the recipe, with this SQL Server statement:

1	EXEC UPDATE_RECIPE 5, 'A TEST RECIPE', 'This is a test recipe.', 5

This screenshot shows this statement in a Query Analyzer tab:

The SELECT statement at line 4 verifies the RECIPES table record rollback. This screenshot shows that the Query Analyzer rollback change worked:

This screenshot runs UPDATE_RECIPE with the changes we originally wanted to make:

It looks like the stored procedure ran correctly. We can again look at the recipe in the Recipe Edit tool, as shown in this screenshot:

Based on the research so far, the bug behavior shown in the third screenshot above most likely does not happen in the SQL Server data layer. We need to look outside this layer, and we’ll turn to the SQL Server Extended Events tool.

The Extended Events Tool

Microsoft offered the SQL Server Profiler tool since at least SQL Server 7. The Profiler captures SQL Server event and behavior “payloads” at a fine-grained level, and SQL developers and DBAs can use this for diagnostics. The Profiler can watch the parameter flows into the UPDATE_RECIPE stored procedure, so it would help here. However, in this article, An overview of the SQL S erver Profiler, Hadi Fadlallah explained that Microsoft would eventually phase out the Profiler. As a replacement, Microsoft first released SQL Server Extended Events in SQL Server 2008. That version of SQL Server Extended Events covered fewer events compared to Profiler, and it did not have a UI. In SQL Server 2012, added a UI, and with every major release, SQL Server Extended Events design covers more events. Microsoft defines SQL Server Extended Events as “… a lightweight performance monitoring system that enables users to collect data needed to monitor and troubleshoot problems in SQL Server.” As we’ll see, it will help us solve the problem.

Build an Extended Events Session

SQL Server Extended Events system operates on “sessions.” We’ll create a new Extended Event session here. First, launch SQL Server and expand Databases. Expand the RECIPES database, and drill into Stored Procedures. We’ll focus on the UPDATE_RECIPE stored procedure, highlighted in this screenshot:

To build an Extended Event session, drill down to Extended Events -> Sessions in the Object Explorer, as shown below:

Right-click “Sessions” and drill down to New Session, as shown in this screenshot:

We could pick “New Session Wizard” here, but “New Session” seems to involve less effort to build a session. The New Session window opens, as shown in this screenshot:

For this demo, we’ll focus on the General and Events pages at the upper left, starting with the General page. As we build the session, we’ll see a lot of options and choices available, but we’ll focus only on what we need. This screenshot shows the General page for the session:

We named the session “UPDATE_RECIPE_session” and later on, we’ll see how to manually launch it and watch the live data on the screen. Next, we’ll configure the session at the Events page, as shown in this screenshot:

First, we’ll pick the specific event we want in the Event library list box, although we can pick more. As of SQL Server 2014, SQL Server Extended Events covers more than 1300 events. Reference material about them might become hard to find, but as a start, we can look at the sys.dm_xe_objects table with this query:

SELECT *

FROM sys.dm_xe_objects

ORDER BY name

The following screenshot shows the basic information returned about the available events:

In the highlighted row above, the description for rpc_completed says that it “Occurs when a remote procedure call has completed.” This looks promising because a stored procedure might count as a “remote procedure.” Other research showed that this guess is correct. Click the highlighted rpc_completed row. Then, click the circled right arrow to move this event to the Selected events list box, as shown in this screenshot:

This enables the “Configure” control at the upper right. Click it to open the event configuration options, as shown below:

Click the Filter Predicates tab, and place these values in the Event configuration options list box row:

Field => object_name

Operator => =

Value => UPDATE_RECIPE

Ignore the And/Or field. This screenshot shows the Events page Configuration section:

Click OK at the lower right as the last step. This screenshot shows the new session in the Object Explorer:

To launch UPDATE_RECIPE_session, right-click it in Object Explorer, and click Start Session, as shown in this screenshot:

Now, enable the Watch Live Data option. Click it as shown in this screenshot:

The Watch Live Data option opens a Management Studio tab, as shown in this screenshot:

Rollback the recipe again, with this SQL Server statement:

1	EXEC UPDATE_RECIPE 5, 'A TEST RECIPE', 'This is a test recipe.', 5

Now that we set up everything, we’ll edit the recipe as we did earlier, and watch the data flow. The following screenshot shows the session tab:

In the lower pane, the statement row has this value:

exec UPDATE_RECIPE

@recipe_ID=5,

@recipe_name=N'A TEST RECIPE',

@recipe_text=N'THIS IS A TEST RECIPE. THIS WILL TEST THE EXTENDED EVENTS TOOL.',

@category_ID=N'5',@key_values=default

Something changed the @recipe_text argument value to all upper case. We know from earlier steps in the investigation that the @recipe_text argument should look like this:

1	@recipe_text=N'This is a test recipe. This will test the extended events tool.'

Based on the investigation, the bug happened before the call to the UPDATE_RECIPE stored procedure. The front-end software probably has a bug. As a first guess, we should look in the code around the call to UPDATE_RECIPE, as shown in this screenshot:

We know the bug involves the @RECIPE_TEXT value, and we can see a potential problem with it at line 599, as shown in the screenshot below:

The VB.net UCase function converts text values to upper case. If we remove it, roll back the data, and try again, we’ll see that we fixed the bug, as shown in this screenshot:

As configured, the SQL Server Extended Events UPDATE_RECIPE_session only “sees” UPDATE_RECIPE calls that come from outside the server. As we saw earlier, this statement calls UPDATE_RECIPE:

1	EXEC UPDATE_RECIPE 5, 'A TEST RECIPE', 'This is a test recipe.', 5

If we run it in Query Analyzer, the UPDATE_RECIPE_session tab won’t see that UPDATE_RECIPE stored procedure call.

Conclusion

As we saw, the SQL Server Extended Events tool offers SQL Server professionals a flexible, powerful way to understand the product and system engineering of the software they build.

Author
Recent Posts

Frank Solomon

Frank Solomon started out building Microsoft stack products, and he gradually focused on SQL Server. Some years ago, he began a parallel shift to writing and technical writing. He wrote published articles, he blogs at Bit Vectors, and he co-wrote The SQL Workshop for Packt Publishing, with SQL Shack writer Prashanth Jayaram.

See more about Frank at LinkedIn.
Remove the non-standard characters from this address: fb} <s.author@gmail.com to reach him by email.