Monitoring system health using system statistical functions

SQL Server has a set of few useful system statistical functions that provide certain statistics related to SQL Server health. These functions are useful for checking and monitoring the health of the server.

Suppose that, in your database environment, one of the web applications is processing records row-by-row. In order to process each row, the application makes a round trip to the database. This means that to process each row there is a new connection. In order to troubleshoot the issue, you want to monitor at regular intervals the number of connections that are taking place. This example will describe how to perform the said task.

Getting ready

In this recipe we will use following functions:

• @@CONNECTIONS

• @@TIMETICKS

• @@CPU_BUSY

• @@IDLE

• @@IO_BUSY

• @@PACK_RECEIVED

• @@PACK_SENT

• @@PACKET_ERRORS

• @@TOTAL_READ

• @@TOTAL_WRITE

• @@TOTAL_ERRORS

In this example, we will use all these functions, and create and execute a script several...