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)

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant.

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.