Home > SQL Server > sp_monitor- SQL Server System Statistics Functions – CPU, IO, Network – SQL SERVER

sp_monitor- SQL Server System Statistics Functions – CPU, IO, Network – SQL SERVER


SQL Server statistics functions are used to understand the overall system activities of SQL Server since the server was started. These functions are nondeterministic functions. So, the output of these functions may differ from each execution.

System Statistics Functions:

pic1These functions are categorized in to CPU, IO and  Network

CPU

@@IO_BUSY : This function returns the total time spent since the SQL  server last started. To obtain the microsecond of CPU value we should multiply CPU value with @@timeticks function.

@@IDLE : This function returns the SQL  server idle time since it was last started. We have to multiply this value with @@timeticks to obtain microsecond.

IO

@@IO_BUSY : This function returns time spent in I/O operations since the SQL Server was last started.

@@TOTAL_READ : This function returns the number of disk reads made since the SQL server last started.

@@TOTAL_WRITE : This function returns the number of disk writes made since the SQL server last started.

@@TOTAL_ERRORS : This function returns the number of write errors happened since the SQL Server last started. It does not include all write errors.

Network

@@CONNECTIONS : This function returns the total number of connection make to SQL  server since it was last restarted. This connection includes both successful and unsuccessful connections.

@@PACK_RECEIVED : This function returns the total number of network packets received by the SQL Server since the server was last started.

@@PACK_SENT : This function returns the total number of network packets sent by the SQL Server since the server was last started.

@@PACK_ERRORS : This function returns the total number of error network packets received or sent by the SQL Server.

We can also use sp_monitor procedure to get all the above information with additional details.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: