Wednesday, 25 November 2015

SQL Client Statistics

SQL Server Management Studio: “Include Client Statistics” Button

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.

Figure 1 – Client Statistics
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.

Figure 2 – Client Statistics for multiple executions of a query