Skip to main content
TechNet
Tip: Monitor SQL Server Performance and Activity with Built-In Functions

In addition to having the use of log files and Transact-SQL statements, you will find a set of built-in functions that return system information. Here’s an overview of key built-in functions and their usages. The values returned by these functions are cumulative from the time SQL Server was last started.
Built-In Functions for Monitoring SQL Server Performance and Activity
Function Description Example
@@connections Returns the number of connections or attempted connections select @@connections as 'Total Login Attempts'
@@cpu_busy Returns CPU processing time in milliseconds for SQL Server activity select @@cpu_busy as 'CPU Busy', getdate() as 'Since'
@@idle Returns SQL Server idle time in milliseconds select @@idle as 'Idle Time', getdate() as 'Since'
@@io_busy Returns I/O processing time in milliseconds select @@io_busy as 'IO Time', getdate() as 'Since' for SQL Server
@@pack_received Returns the number of input packets read from the network by SQL Server select @@pack_received as 'Packets Received'
@@pack_sent Returns the number of output packets written to the network by SQL Server select @@pack_sent as 'Packets Sent'
@@packet_errors Returns the number of network packet errors for SQL Server connections select @@packet_errors as 'Packet Errors'
@@timeticks Returns the number of microseconds per CPU clock tick select @@timeticks as 'Clock Ticks'
@@total_errors Returns the number of disk read/write errors encountered by SQL Server select @@total_errors as 'Total Errors', getdate() as 'Since'
@@total_read Returns the number of disk reads by SQL Server select @@total_read as 'Reads', getdate() as 'Since'
@@total_write Returns the number of disk writes by SQL Server select @@total_write as 'Writes', getdate() as 'Since'
fn_virtualfilestats Returns input/output statistics for data and log files select * from fn_virtualfilestats(null,null)



Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.
For more Tips on other products, visit the TechNet Magazine Tips index.