Capturing Queries Can Be a Pain

The moment you decide you want to monitor your SQL Server instance for query behavior, you’re going to be forced to make a bunch of choices. Further, all these choices lead to problems that you’re going to have to deal with. Some of the problems are obvious. For example, you decided to capture all the statements run against the system. That’s a lot of data you’ll have to be prepared to manage. However, other problems are extremely subtle and can really be a pain. For example, how do you differentiate between a query run by a person in SQL Server Management Studio and the queries that SSMS itself runs against the system?

Queries From SSMS

Let’s say I’m interested in capturing both the batch completed event and the statement completed event. I could set up an Extended Event session like this:

CREATE EVENT SESSION BatchTesting
ON SERVER
    ADD EVENT sqlserver.sql_batch_completed
    (ACTION
     (
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.is_system,
         sqlserver.username
     )
    ),
    ADD EVENT sqlserver.sql_statement_completed
    (ACTION
     (
         sqlserver.client_app_name,
         sqlserver.client_hostname,
         sqlserver.is_system,
         sqlserver.username
     )
    )
    ADD TARGET package0.event_file
    (SET filename = N'BatchTesting')
WITH
(
    TRACK_CAUSALITY = ON
);
GO

Simple enough.

I have added a few actions though. We have the username, to see who is logged in. The is_system action to identify system queries. Then, the client_app_name and client_host_name identify where the code is being run from.

I’ll get it all set up and then run this command:

SELECT *
FROM Sales.SalesOrderDetail AS sod;

The output in Live Data from the Session would look like this:

You can click on that to make it bigger. However, my point is already made. Notice that there’s not two events, my statement completed and batch completed for the one query I ran. Instead, there are four. There’s another query, part of a separate process because it has a unique activity guid value. That was run by SSMS as part of execution the query. It retrieves the SPID. However, it’s not an action I did myself. It’s also not marked as system calls. In fact, the app & client are the same. In short, there’s no differentiation. To all intents & purposes, SSMS is you and you’re running queries.

Let’s see what happens when I switch databases in the query window:

That’s 59 batch commands, one statement each. I clicked the drop down to get a list of databases, well, it queries the system for that. In fact, weirdly, it ran the same query four different times, one right after the other. And you wonder why SSMS is always so painfully slow.

I also ran this so you could see that Redgate Software is running a bunch of queries too. That’s SQL Prompt hard at work because I switched databases.

In short, there are a ton of queries coming from SSMS and they all look like you’re running them. If the app appropriately differentiates itself, like SQL Prompt did, great. The problem this represents is that, in all the 61 commands (2 up above, plus the 59 below) I’ve captured in this exercise, only one is of real interest to me as a means of monitoring my servers to see what the people are doing. And, only the events from SQL Prompt can be easily filtered.

How do we pluck the wheat from the chaff?

Short answer. You don’t. Well, you can, but stuff gets really ugly because you have to make a bunch of assumptions. Here’s a great example of Rob Sewell solving this problem.

Conclusion

Honestly, there’s not a huge lesson in this post. It’s just an illustration of how difficult all this stuff can quickly become. Simply saying, “I want to see the queries my users are running” doesn’t end in a really easy place. However, it’s information you need. A person I was helping recently discovered that his app issued 17,000 queries in a minute, all the same. And yeah, they had been experiencing slow performance. Gee, I wonder why? Kidding, of course. But, until they captured the queries, they honestly had no idea what was going on.

I guess the point really is, yes, doing this, capturing all the queries, can be messy. However, in the end, it’s also extremely informative.

Now, why did SSMS run that query four times in a row? That really bothers me.