Analyze Script Performance- Include Client Statistics

Include client statistic “is one of the helpful and interesting tool of the SQL Server Management Studio for query tuning and to analyze the script performance. It displays information about the query execution grouped into categories; it helps to analyze script performance.

It’s very helpful if you want to know below property of executed scripts:

1.      How much byte of data sent from client?

2.      How much byte of data received from the server?

3.      How many TDS packets sent from the client?

4.      How many TDS packets received from server?

5.      Number of server round trips.

6.      Number of transaction etc.

 Sometime query run slow due to slow network traffic between client and server, or because of high number of server roundtrips. (Creating procedure is one of the solutions for this).

  When we run a script  in the Transact-SQL Editor, we can choose to collect client statistics such as application profile, network, and time statistics for the execution. Such metrics allow us to gauge the efficiency of the script, or benchmark different scripts.

• How to use?

To see the client statistics we need to select “Query” menu and select “Include client statistics” .

 

• How it works?

It records 10 regular executions of that session and shows the statistics together where we can compare the value of different parameters of the Query execution. We can also reset the client statistics.  In client statistic windows it will show green arrow if value is decreased, Red arrow if values increased, and black arrow if value is same from privies trial value.

Try below sample scripts :

Use tempdb


Go

/*Trial-1*/

select * from sys.tables


select * from sys.columns



/*Trial-2*/



select * from sys.tables

GO

select * from sys.columns

/*Create procedure*/


Create Proc mdata


as


begin


set NoCount ON


select * from sys.tables


select * from sys.columns


end

/* Trial- 3*/



EXEC mdata