SQL Server Management Studio – Client Statistics

Leave a comment (0) Go to comments

During the Performance Tuning activity, to measure how much data is retrieved from server to client side, we use Client Statistics. Client statistics are also used to gather information about :

  • Client Execution Time
  • Query Profile Statistics
    • Number of INSERT, DELETE and UPDATE statements
    • Rows affected by INSERT, DELETE, or UPDATE statements
    • Number of SELECT statements
    • Rows returned by SELECT statements
    • Number of transactions
  • Network Statistics
    • Number of server roundtrips
    • TDS packets sent from client
    • TDS packets received from server
    • Bytes sent from client
    • Bytes received from server
  • Time Statistics
    • Client processing time
    • Total execution time
    • Wait time on server replies

These statistics provide additional information you can use to see how efficiently a query is running, and also allows you to easily compare one query against another. These statistics can also be used to understand how a particular query is running, helping us to diagnose performance-related problems.

How to get Client Statistics Information

There are three methods to invoke Client Stats Information

Method 1

Press SHIFT+ALT+S together.

Method 2

Go to Menu >> Query >> Include Client Statistics

SQL Server Management Studio   Client Statistics Wait time on server replies Total execution time Time Statistics TDS packets sent from client TDS packets received from server SQL Server Wait time on server replies SQL Server Total execution time SQL Server Time Statistics SQL Server TDS packets sent from client SQL Server TDS packets received from server SQL Server Rows returned by SELECT statements SQL Server Rows affected by INSERT SQL Server Query Profile Statistics SQL Server Number of transactions SQL Server Number of server roundtrips SQL Server Number of INSERT SQL Server Network Statistics sql server Client Stats SQL Server Client Statistics SQL Server Client processing time SQL Server Client Execution Time SQL Server Bytes sent from client SQL Server Bytes received from server SQL Server 2008 Wait time on server replies SQL Server 2008 Total execution time SQL Server 2008 Time Statistics SQL Server 2008 TDS packets sent from client SQL Server 2008 TDS packets received from server SQL Server 2008 Rows returned by SELECT statements SQL Server 2008 Number of transactions SQL Server 2008 Number of server roundtrips SQL Server 2008 Number of SELECT statements SQL Server 2008 Network Statistics sql server 2008 Client Stats SQL Server 2008 Client processing time SQL Server 2008 Bytes sent from client SQL Server 2008 Bytes received from server SQL Server 2005 Wait time on server replies SQL Server 2005 Total execution time SQL Server 2005 Time Statistics SQL Server 2005 TDS packets sent from client SQL Server 2005 TDS packets received from server SQL Server 2005 Rows returned by SELECT statements SQL Server 2005 Number of transactions SQL Server 2005 Number of server roundtrips SQL Server 2005 Number of SELECT statements SQL Server 2005 Network Statistics sql server 2005 Client Stats SQL Server 2005 Client processing time SQL Server 2005 Bytes sent from client SQL Server 2005 Bytes received from server Rows returned by SELECT statements Rows affected by INSERT Query Profile Statistics or UPDATE statements Number of transactions Number of server roundtrips Number of SELECT statements Number of INSERT Network Statistics DELETE and UPDATE statements DELETE Client processing time Client Execution Time Bytes sent from client Bytes received from server


Method 3

Click on Include Client Statistics Option in Toolbar

SQL Server Management Studio   Client Statistics Wait time on server replies Total execution time Time Statistics TDS packets sent from client TDS packets received from server SQL Server Wait time on server replies SQL Server Total execution time SQL Server Time Statistics SQL Server TDS packets sent from client SQL Server TDS packets received from server SQL Server Rows returned by SELECT statements SQL Server Rows affected by INSERT SQL Server Query Profile Statistics SQL Server Number of transactions SQL Server Number of server roundtrips SQL Server Number of INSERT SQL Server Network Statistics sql server Client Stats SQL Server Client Statistics SQL Server Client processing time SQL Server Client Execution Time SQL Server Bytes sent from client SQL Server Bytes received from server SQL Server 2008 Wait time on server replies SQL Server 2008 Total execution time SQL Server 2008 Time Statistics SQL Server 2008 TDS packets sent from client SQL Server 2008 TDS packets received from server SQL Server 2008 Rows returned by SELECT statements SQL Server 2008 Number of transactions SQL Server 2008 Number of server roundtrips SQL Server 2008 Number of SELECT statements SQL Server 2008 Network Statistics sql server 2008 Client Stats SQL Server 2008 Client processing time SQL Server 2008 Bytes sent from client SQL Server 2008 Bytes received from server SQL Server 2005 Wait time on server replies SQL Server 2005 Total execution time SQL Server 2005 Time Statistics SQL Server 2005 TDS packets sent from client SQL Server 2005 TDS packets received from server SQL Server 2005 Rows returned by SELECT statements SQL Server 2005 Number of transactions SQL Server 2005 Number of server roundtrips SQL Server 2005 Number of SELECT statements SQL Server 2005 Network Statistics sql server 2005 Client Stats SQL Server 2005 Client processing time SQL Server 2005 Bytes sent from client SQL Server 2005 Bytes received from server Rows returned by SELECT statements Rows affected by INSERT Query Profile Statistics or UPDATE statements Number of transactions Number of server roundtrips Number of SELECT statements Number of INSERT Network Statistics DELETE and UPDATE statements DELETE Client processing time Client Execution Time Bytes sent from client Bytes received from server

Once you enable the client statistics, it will display another tab in the result span; when clicked, we obtain the following:

SQL Server Management Studio   Client Statistics Wait time on server replies Total execution time Time Statistics TDS packets sent from client TDS packets received from server SQL Server Wait time on server replies SQL Server Total execution time SQL Server Time Statistics SQL Server TDS packets sent from client SQL Server TDS packets received from server SQL Server Rows returned by SELECT statements SQL Server Rows affected by INSERT SQL Server Query Profile Statistics SQL Server Number of transactions SQL Server Number of server roundtrips SQL Server Number of INSERT SQL Server Network Statistics sql server Client Stats SQL Server Client Statistics SQL Server Client processing time SQL Server Client Execution Time SQL Server Bytes sent from client SQL Server Bytes received from server SQL Server 2008 Wait time on server replies SQL Server 2008 Total execution time SQL Server 2008 Time Statistics SQL Server 2008 TDS packets sent from client SQL Server 2008 TDS packets received from server SQL Server 2008 Rows returned by SELECT statements SQL Server 2008 Number of transactions SQL Server 2008 Number of server roundtrips SQL Server 2008 Number of SELECT statements SQL Server 2008 Network Statistics sql server 2008 Client Stats SQL Server 2008 Client processing time SQL Server 2008 Bytes sent from client SQL Server 2008 Bytes received from server SQL Server 2005 Wait time on server replies SQL Server 2005 Total execution time SQL Server 2005 Time Statistics SQL Server 2005 TDS packets sent from client SQL Server 2005 TDS packets received from server SQL Server 2005 Rows returned by SELECT statements SQL Server 2005 Number of transactions SQL Server 2005 Number of server roundtrips SQL Server 2005 Number of SELECT statements SQL Server 2005 Network Statistics sql server 2005 Client Stats SQL Server 2005 Client processing time SQL Server 2005 Bytes sent from client SQL Server 2005 Bytes received from server Rows returned by SELECT statements Rows affected by INSERT Query Profile Statistics or UPDATE statements Number of transactions Number of server roundtrips Number of SELECT statements Number of INSERT Network Statistics DELETE and UPDATE statements DELETE Client processing time Client Execution Time Bytes sent from client Bytes received from server

Note: A maximum of 10 trials are averaged in Client Stats window. When 11 th trial comes into the result, the very first trial is dropped, keeping total average of latest 10 trials.

Method 4

Get the same information from Server about all sessions/client.

select * from sys.dm_exec_connections

Returns information about the connections established to this instance of SQL Server and the details of each connection.

Tags : SQL Server Client Statistics,Client Execution Time ,Query Profile Statistics ,Number of INSERT, DELETE and UPDATE statements ,Rows affected by INSERT, DELETE, or UPDATE statements ,Number of SELECT statements ,Rows returned by SELECT statements ,Number of transactions ,Network Statistics ,Number of server roundtrips ,TDS packets sent from client ,TDS packets received from server ,Bytes sent from client ,Bytes received from server,Time Statistics ,Client processing time ,Total execution time ,Wait time on server replies ,SQL Server Client Execution Time ,SQL Server Query Profile Statistics ,SQL Server Number of INSERT, DELETE and UPDATE statements ,SQL Server Rows affected by INSERT, DELETE, or UPDATE statements ,Number of SELECT statements , SQL Server Rows returned by SELECT statements , SQL Server Number of transactions , SQL Server Network Statistics , SQL Server Number of server roundtrips , SQL Server TDS packets sent from client , SQL Server TDS packets received from server , SQL Server Bytes sent from client , SQL Server Bytes received from server, SQL Server Time Statistics , SQL Server Client processing time , SQL Server Total execution time , SQL Server Wait time on server replies ,SQL Server 2005 Number of SELECT statements , SQL Server 2005 Rows returned by SELECT statements , SQL Server 2005 Number of transactions , SQL Server 2005 Network Statistics , SQL Server 2005 Number of server roundtrips , SQL Server 2005 TDS packets sent from client , SQL Server 2005 TDS packets received from server , SQL Server 2005 Bytes sent from client , SQL Server 2005 Bytes received from server, SQL Server 2005 Time Statistics , SQL Server 2005 Client processing time , SQL Server 2005 Total execution time , SQL Server 2005 Wait time on server replies ,SQL Server 2008 Number of SELECT statements , SQL Server 2008 Rows returned by SELECT statements , SQL Server 2008 Number of transactions , SQL Server 2008 Network Statistics , SQL Server 2008 Number of server roundtrips , SQL Server 2008 TDS packets sent from client , SQL Server 2008 TDS packets received from server , SQL Server 2008 Bytes sent from client , SQL Server 2008 Bytes received from server, SQL Server 2008 Time Statistics , SQL Server 2008 Client processing time , SQL Server 2008 Total execution time , SQL Server 2008 Wait time on server replies

EOF - SQL Server Management Studio – Client Statistics, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.