Monday, April 18, 2011

Monitor SQL Server Performance and Activity

select @@connections as 'Total Login Attempts'
-- Returns the number of connections or attempted connections

select @@cpu_busy as 'CPU Busy', getdate() as 'Since'
-- Returns CPU processing time in milliseconds for SQL Server activity

select @@idle as 'Idle Time', getdate() as 'Since'
-- Returns SQL Server idle time in milliseconds

select @@io_busy as 'IO Time', getdate() as 'Since'
-- Returns I/O processing time in milliseconds

select @@pack_received as 'Packets Received'
-- Returns the number of input packets read from the network by SQL Server

select @@pack_sent as 'Packets Sent'
-- Returns the number of output packets written to the network by SQL Server

select @@packet_errors as 'Packet Errors'
-- Returns the number of network packet errors for SQL Server connections

select @@timeticks as 'Clock Ticks'
-- Returns the number of microseconds per CPU clock tick

select @@total_errors as 'Total Errors', getdate() as 'Since'
-- Returns the number of disk read/write errors encountered by SQL Server

select @@total_read as 'Reads', getdate() as 'Since'
-- Returns the number of disk reads by SQL Server

select @@total_write as 'Writes', getdate() as 'Since'
-- Returns the number of disk writes by SQL Server

select * from fn_virtualfilestats(null,null)
-- Returns input/output statistics for data and log files

No comments: