Sunday, October 30, 2011

Tips: How to monitor SQL Server Performance and Activity using the built-in functions

We can even find a set of built-in functions that return system information apart from the use of the log files and Transact-SQL statements. In this article, let’s have an overview of the key built-in functions and their usages. The values returned by these functions are cumulative from the time SQL Server was last started.

































FunctionDescriptionExample
@@connectionsReturns the number of connections or attempted connectionsselect @@connections as 'Total Login Attempts'
@@cpu_busyReturns CPU processing time in milliseconds for SQL Server activityselect @@cpu_busy as 'CPU Busy', getdate() as 'Since'
@@idleReturns SQL Server idle time in millisecondsselect @@idle as 'Idle Time', getdate() as 'Since'
@@io_busyReturns I/O processing time in millisecondsselect @@io_busy as 'IO Time', getdate() as 'Since' for SQL Server
@@pack_receivedReturns the number of input packets read from the network by SQL Serverselect @@pack_received as 'Packets Received'








































@@pack_sentReturns the number of output packets written to the network by SQL Serverselect @@pack_sent as 'Packets Sent'
@@packet_errorsReturns the number of network packet errors for SQL Server connectionsselect @@packet_errors as 'Packet Errors'
@@timeticksReturns the number of microseconds per CPU clock tickselect @@timeticks as 'Clock Ticks'
@@total_errorsReturns the number of disk read/write errors encountered by SQL Serverselect @@total_errors as 'Total Errors', getdate() as 'Since'
@@total_readReturns the number of disk reads by SQL Serverselect @@total_read as 'Reads', getdate() as 'Since'
@@total_writeReturns the number of disk writes by SQL Serverselect @@total_write as 'Writes', getdate() as 'Since'
fn_virtualfilestatsReturns input/output statistics for data and log filesselect * from fn_virtualfilestats(null,null)

B y ,