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
