I’m curious. I like to know how things work. I have to read the user manual of everything I buy so I know all of its features. I like clicking all the buttons in applications I work with. However, SQL Server Management Studio (SSMS) has so many I haven’t gotten through all of them. At SQL Saturday #118 Wisconsin earlier this year, I was watching a presentation and the presenter clicked “Include Client Statistics”.
“What is this magic?!” I thought.
MSDN says it “Displays information about the query execution grouped into categories. When Include Client Statistics is selected from the Query menu, a Client Statistics window is displayed upon query execution. Statistics from successive query executions are listed along with the average values. Select Reset Client Statistics from the Query menu to reset the average.”
I mentioned it in last week’s webcast, “SSMS: More Than Meets The Eye“, but I wanted to give you more detail about it here.
This button is located on the SQL Editor toolbar.
Open a query editor window, click on it to highlight it, write a query, and execute it. Your results will look like Figure 1.
Some really valuable information can be found here, such as the number of INSERT, DELETE, UPDATE, and SELECT statements – think of how useful that can be when tuning a complex stored procedure. You can see the number of rows affected or returned, and the number of transactions executed.
The network statistics lets you see how much traffic is moving from your client to the server and back.
The time statistics tells you how much time was spent processing on the client versus how much time was spent waiting for the server. These figures are in milliseconds.
That’s useful when you run a query once, but its usefulness becomes more apparent when you are tuning a query. Statistics will be shown for the ten most recent executions, and they will be averaged, as shown in Figure 2. Thus, as you are making changes to a query – perhaps changing the conditions in the WHERE clause, or comparing an AVERAGE to a window function – the statistics will update. You can, at a glance, compare the changes. You’ll even see green arrows for improved performance, and red arrows for worse performance.
Client Statistics is not as in-depth of a tool as, say, execution plans, showing you where the performance of your query can be improved. It does, however, give you access to information that you might otherwise need to keep track of in your head, or scribbled on a Post-It Note – like I used to do when writing report queries. It’s another useful tool in the SSMS toolbox!