This documentation is archived and is not being maintained.
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|
|@@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.