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.