Product articles SQL Prompt Query Performance
Testing the Performance of Individual…

Testing the Performance of Individual SQL Statements within a Batch using SQL Prompt

With SQL Prompt and Phil Factor's chk code snippet, you can, with a few clicks, get a list of all the SQL statements executed within a batch, in SSMS, their execution plans, and their execution statistics, such as duration, CPU, logical reads and so on.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

This article is all about using a SQL Prompt snippet to create and run a test harness on a batch of SQL code, to provide performance data. Behind the scenes, the snippet creates and starts an Extended Events event session, inserts and runs the text of the batch of SQL you’ve selected in SSMS, and uses XQuery on the XML event data, to return it in digestible, tabular form. With a few clicks to invoke and execute the snippet, we get the execution plan for any batch, along with the SQL text and query execution statistics for every SQL statement in the batch.

Why bother setting it up this way? When wrestling with XML and extended events, I sometimes feel like a goldfish staring at a television set. It’s hypnotic, and I know it is lovely, but I don’t pretend to understand it. As a developer, I’m more at home thinking of performance in simple terms, as being the lapsed time, from the perspective of the application. It is a solid metric that is the same as the experience of the user. However, when the elapsed time is longer than the users can tolerate, or is erratic, and I can’t find an obvious fault in the logic, then I like to see the server-based I/O metrics, and to check the execution plans.

To get these, I need to use Extended Events, and if I can keep the complexity at bay by means of snippets or templates, it is an easier decision to make. I’m not Billy Two-Brains, so I’m happy to get help where I can.

An example of using the snippet

Let’s say you have an aggregation query that is not performing as hoped. You’ve read Grant Fritchey’s account of the HASH | ORDER GROUP query hints, in his Execution Plans book, which forces SQL Server to choose a particular aggregation mechanism for all aggregations in the query that are caused by GROUP BY or DISTINCT. The HASH GROUP hint forces it to use a Hash Match aggregation (which is hash based), and ORDER GROUP a Stream Aggregate (which is order based).

You’re wondering whether these hints were worth pursuing, or whether it makes more sense to create an index. The only way to know for sure is to test all the alternatives. Listing 1 shows the code I used.

Listing 1

Which strategy performs best? In the SSMS query pane, I hit Ctrl-A to select the whole batch. With the code selected, I find that SQL Prompt’s little red action list icon appears at the left base of my selection. Its drop-down menu lists all the installed snippets, and I click on my chk snippet.

The chk snippet, which I’ll show you how to create shortly, will embed the selected code in a special extended events event session that will track the individual statements in a batch that starts with a particular string. This identifier is added by the snippet, in a comment. I’ll show you how to examine several batches later in the article.

Quite a bit of code gets heaved into query pane, but I have steady nerves. I just hit ‘Execute’ and a second later I see the results, which includes the text of all the statements executed, their execution time, and links to the execution plan.

Scrolling across, there is more information, including the CPU time, the IO statistics, and number of rows returned, for each statement.

We can see straight away, from these execution statistics, that providing a suitable index is far better than messing with hints. By providing an index that’s logically ordered by the column on which we’re grouping, we instantly make the query run four times faster.

If we want to know why, we can look at the showplan. As we are, in this first example, examining just one batch, all the showplan links are to the same plan, which is the plan for the batch, and it contains the individual plans for every statement in the batch:

With no useful index, the optimizer can either use hash match aggregation, which has the overhead of building and populating a hash table in memory, or it can first sort the data emerging from the clustered index, and then use stream aggregation.

It deems the former strategy to be the cheapest one, in this case, but the ORDER GROUP hint forces the optimizer to adopt the latter. Our query execution statistics indicate that it makes little difference for a small table like this, but sort operations are expensive, and so the performance of this hinted query is likely to degrade as the number of rows in the source table grows. Instead, by providing an index ordered by the GROUP BY column, we enable the optimizer to choose the lower-overhead stream aggregation, without requiring an additional sort operation.

The reason I mention all this is not dissuade you from using hints, but to persuade you to measure things like this as accurately as possible, before deciding on a course of action. With this snippet, which we can use for any set of queries, we get to see very quickly if any one strategy is going to offer a ‘big win’.

The Snippet for checking SQL Statements in a batch

Listing 2 shows the code for creating the Prompt snippet.

Listing 2

That $SELECTEDTEXT$ placeholder allows you to put the code from the snippet either side of the selected code. As you see, we create the session, execute the code and immediately stop the session. We take the XML document that represents the contents of the ring buffer and shred it into a relational table that appears in the results. The event session filters out everything but the batch that starts with the string, '--Test these queries'.

Simply paste the code in Listing 2 into an SSMS query pane, use Ctrl-A to highlight the code, then right-click and select Create Snippet. Decide the characters you want to enter to invoke the snippet (I chose chk), give the snippet a description, and hit save.

Checking more than one batch

This snippet is set up for just one batch. It inserts the following line at the start of the batch;

However, the event session is set up to report on any number of batches that start with that string. It is possible to provide a separate execution plan for each statement in a series of separate batches. To demonstrate this, we’ll repeat the test but put each statement into its own batch.

Listing 3

Notice the string, --Test these queries, at the start of each batch. Notice too that we’ve replaced the table variable with a temporary table, because it is visible across batches. Now put this in the test harness using Prompt, and you get a neater result with only the queries you are interested in, and each one has its own Execution plan.

I put only one query in a batch but if there is more than one they will appear and will share the execution plan with the others in the same batch.

A Snippet for checking statements within a stored procedure or function

It is very easy to extend the basic snippet to also show, for example, the statements executed within stored procedures and functions. Simply execute the CREATE EVENT portion of Listing 2, and then navigate the event session in the SSMS object explorer and choose Properties from its right-click context menu.

We can add whatever events, fields and filters we need. Here, I am adding the sp_statement_completed event to the event session.

Having made your changes, you can script out the DDL to create the event session and use it in your Prompt snippet. Remember to add the preliminary code to delete any existing session with that name.

It is worth getting more familiar with Extended Events, by changing event sessions. via the property window, to see what detail you can get from them. However, be warned that you will need to be able to stare at raw XML without flinching until you have been able to shred the XML into relational form. Fortunately, there are similarities between the TSQL events, so a snippet that records all such events is reasonably easy.

Listing 4 shows the code for a snippet that will return the execution statistics and plans for all statements in a stored procedure, or batch. It will work for functions as well as procedures. It great as a secondary investigation, but the level of detail can soon get overwhelming.

Listing 4

As an example, I’ve used it here to check on an old stored procedure of mine which lays out a calendar of the current month, or any month you specify.

Conclusion

If you want to use Extended Events for developing code, it is well worth stepping back for a moment to create snippets or templates, so that you can reuse these test harnesses as you develop.

They save a great deal of time spent trying to work out what exactly is making the code run slowly. You don’t always need a great level of detail when tackling performance problems, and I will generally time whole sections of code before focusing at the SQL Statement level. However, when you want to get to the detail, there is nothing else to compare with a test harness that is based on Extended Events.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more