How to have a simple server monitoring in MS SQL Server

• MS SQL Server includes many tools and procedures for monitoring its opreations.
• The MS SQL Server tools are text only and are not graphical.
• The results from the MS SQL Server tools can then be imported in Excel and graphed

SQL Server built-in function

exec sp_monitor;
go

last_run                current_run             seconds
----------------------- ----------------------- -----------
2007-06-04 10:28:30.543 2007-06-04 10:29:04.903 34

cpu_busy                  io_busy                   idle
------------------------- ------------------------- -------------------------
0(0)-0%                   1(0)-0%                   77816(33)-97%

packets_received         packets_sent             packet_errors
------------------------ ------------------------ ------------------------
58(3)                    64(3)                    0(0)

total_read          total_write         total_errors        connections
------------------- ------------------- ------------------- ------------------
599(0)              121(1)              0(0)                1232(5)

Alternative to built-in function

select @@CONNECTIONS as 'Connections', 
       @@CPU_BUSY as '% usage', 
       @@ERROR as 'Error', 
       @@IO_BUSY as 'I/O', 
       @@LANGUAGE as 'Language', 
       @@LOCK_TIMEOUT as 'Lock timeout', 
       @@MAX_CONNECTIONS as 'Max Connections', 
       @@MAX_PRECISION as 'Precision', 
       @@PACK_RECEIVED as 'Packet received', 
       @@PACK_SENT as 'Packets Sent', 
       @@PACKET_ERRORS as 'Packet Errors', 
       @@SERVERNAME as 'Server', 
       @@SERVICENAME as 'Services', 
       @@TOTAL_ERRORS as 'Errors', 
       @@TOTAL_READ as 'Reads', 
       @@TOTAL_WRITE as 'Writes', 
       @@VERSION as 'Version';
go

Connections % usage     Error       I/O         Language     Lock timeout Max Connections Precision Packet received Packets Sent Packet Errors Server      Services      Errors      Reads       Writes      Version
----------- ----------- ----------- ----------- ------------ ------------ --------------- --------- --------------- ------------ ------------- ----------- ------------- ----------- ----------- ----------- -----------------------------------------------------
1232        15          0           40          us_english   -1           32767           38        60              66           0             W2K3BASE    MSSQLSERVER   0           599         130         Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) 
	Feb  9 2007 22:47:07 
	Copyright (c) 1988-2005 Microsoft Corporation
	Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)


(1 row(s) affected)