Performance Tuning · Uncategorized

How Client Statistics helps in improving performance in SQL Server

Client Statistics is SQL Server data tool which is very helpful in determining the statistics that how much data received from server to the client side. In other words, client statistics helps in analyzing the traffics load like packets/bytes sent and received at client – server side. When we run a script or query in T-SQL editor, we can enable Client statistics to collect statistics like application profile, time statistics and network statistics which help in checking the efficiency of the script.

Client statistics are grouped into 3 categories:

  1. Query Profile Statistics
  2. Network Statistics
  3. Time Statistics

Let’s briefly look out about the categories of Client Statistics which gives you a picture what these categories show.

Query Profile Statistics

Query profile statistics section shows the information about the query execution like:

How many INSERT, UPDATE, DELETE and SELECT statements are executed in query editor window?

How many rows appeared in query output combining rows of multiple SELECT statements?

How many transaction occur in query window?

Network Statistics

Similarly, network statistics shows the data about size of data send and receive from server to client in form of Packets and Bytes.

Time Statistics

Time statistics shows the data about time taken by server in sending and receiving data.

How to enable Client Statistics?

When you have to work with Client Statistics first we need to enable it using SSMS.

Method 1

Press Shift + Alt + S

CS_1

Method 2

Move to the Query option in Menu Option and click on Include Client Statistics.

CS_2

Method 3 –

Right click in the query window and select Include Client statistics

CS_3

Now let’s put some extra light on Client Statistics internals. As per below screenshot I have executed two SELECT statement in a single query editor pane. As the query gets executed one more tab ‘Client Statistics’ appears in output window.

CS_4

As we can see the screenshot there are some different color Arrows signs (Black, Green and Red) which makes the looks more significant. Green sign signifies improvement in statistics while Red sign signifies degradation in statistics. Black arrow signifies that the value is unchanged from previous run.

Let’s execute the query one more time to get the picture clearer.

CS_5

Every time when a query executed a Trail tabs gets added with latest one in descending order. Average tab is the average of all Trail tab values.

There is also a row with name ‘Client Execution Time’ which shows at which time the query gets executed in T-SQL editor when Client Statistics was enabled.

Now we will see the calculation of each category one by one:

Query Profile Statistics

We don’t have any INSERT, UPDATE, DELETE statement in query window so value of ‘Number of INSERT, UPDATE and DELETE statement’ is 0.

Value of ‘Rows affected by INSERT, UPDATE and DELETE statement’ is 0 as no such statements are present.

Value of ‘Number of SELECT statements’ is 2 as you can see we have 2 select statement in query window.

Value of ‘Rows returned by SELECT statement’ is 4 which is sum of output of each individual Select statement.

Value of ‘Number of transaction’ is 0 as no transaction is happening currently in query window.

 

Network Statistics

Value of ‘Number of server roundtrips’ is 1 as only a single time we sent the request to the server and received a reply.

Value of ‘Number of TDS packets sent’ is 1 which represents the number of packets sent to server.

Value of ‘Number of TDS packet received’ is 1 which represent the number pf packets received from the server.

Value of ‘Number of bytes sent’ is 2 which represent the number of bytes sent to server in TDS packets.

Value of ‘Number of bytes received’ is 2 which indicates the number of bytes received from server in TDS packets’

Time Statistics

Value of ‘Client processing time’ is 20 which indicates the time spent by the driver in processing.

Value of ‘wait time on server replies’ is 30 which indicates the waiting time for the driver spent to get the reply from the server.

Value of ‘Total execution time’ is sum of values of Client processing time + Wait time on server replies (20 + 30 = 50).

This is simple case of client statistics. To get some interesting information now let’s disable the Client statistics from the tab and execute the query one more time. Again enable the Client statistics from any method which is mentioned above and execute the query one more time.

Now one more Trail tab is added and also Red arrows increases. You will observe that values of Trail1 and Trail2 is almost identical while value Trial3 is totally different.

CS_6

Why Trail3 values are different as we run the query only a single time after enabling the Client statistics?

The answer to this is – Trail3 value is the sum of number of execution of query when Client statistics was disabled and sum of the values when Client statistics was enabled again. Means, whenever a query gets executed it will added all the previous values till client statistics is enabled.

Values in Query profile statistics is showing the sum of previous execution value including query execution after enabling.

Value of Number of SELECT statement is 4 [2(Query executed when statistics was disabled) + 2 (Query executed when statistics enabled again)]

Same is with value of Rows returned by SELECT statement i.e. 34 [17 (When client statistics was disabled) + 17 (When query statistics enabled again)]

In Network statistics also values are showing values of sum of query execution when client statistics was disabled and sum of values when client statistics was enabled. You also see Red arrow signs which indicates the degradation in statistics when compare with Trial2.

Same calculation is with Time statistics.

How to reset/clear the Client statistics data?

Go to the Query tab >> Reset Client Statistics.

CS_7

In summary, client statistics also helps you in analyzing the script performance by monitoring the response time of query, including client server execution time, data send in packets/bytes.

That’s all folks. Hope you will like it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s