Statistical System functions in TSQL

TSQL has a bunch of statistical system functions that can be used to return information about the system. This includes details about the number of connection attempts, cpu time, and total reads and writes and more.

Many of these functions will be useful for performance monitoring. All of these functions return values that indicate cumulative activity since the last restart of SQL Server.

SELECT @@CONNECTIONS AS ConnectionAttempts,
	   @@CPU_BUSY AS CpuTime,
	   @@IDLE AS IdleTime,
	   @@IO_BUSY AS IoTime,
	   @@PACKET_ERRORS AS PacketErrors,
	   @@PACK_RECEIVED AS PacketsReceived,
	   @@PACK_SENT AS PacketsSent,
	   @@TIMETICKS AS MicrosecondsPerTick, -- Computer dependant
	   @@TOTAL_ERRORS AS TotalDiskWriteErrors,
	   @@TOTAL_READ AS TotalDiskReads,
	   @@TOTAL_WRITE AS TotalDiskWrites;

An additional interesting function is fn_virtualfilestats which can return I/O stats about a database or particular database file. This is essentially the same as the sys.dm_io_virtual_file_stats dmv. To retrieve information about a particular database run the following TSQL;

SELECT *
FROM fn_virtualfilestats (DB_ID(), NULL);