Monitoring Microsoft SQL Servers

The pre-built Microsoft SQL monitoring model that eG Enterprise offers (see Figure 1), provides in-depth monitoring for SQL database servers.

Figure 1 : Layer model for Microsoft SQL servers

Each of the layers of this hierarchical model reports a wide variety of metrics ranging from the basic operating system-level statistics to individual database related measurements to those indicating the database engine health. The table below sheds light on what the eG SQL Monitor reveals:

Database Service Monitoring	Is the database server available for servicing requests? What is the response time for a typical query? How many logins/logouts are happening on the SQL server? Which applications/users are accessing the SQL server and what is their respective resource usage? What queries are each of the applications currently executing?
Database Server Engine Monitoring	What is the CPU utilization of the database server engine? How much time is the SQL server spending on processing vs. I/O? What is the typical workload on the database server? Which databases are imposing most load on the database server engine? How many processes are running, and what queries are they executing? Which user(s) are executing these queries?
Lock Activity Monitoring	What is the typical locking activity on the database? Which processes are being blocked and by whom? Which are the root-blocker processes, and what queries are they executing? Are any deadlocks happening?
Database Activity and Space Monitoring	What databases are hosted on the SQL server? Is any of the databases reaching capacity? Which of the databases is seeing more transaction activity? How many active transactions are currently happening to each of the database server?
SQL Memory Monitoring	Is there sufficient memory available for the SQL server? How much memory is the server consuming and how much is it willing to consume? How much memory is used for connections, how much for locks, and how much for query optimizations? What is the server’s cache hit ratio? How many pages are available in the server’s buffer pool? How many of these are free pages?
Operating System Monitoring	Is there sufficient disk capacity? Is there excessive contention for CPU or memory resources? Are the disks unusually busy? Which processes are taking up most resources (CPU, memory, disk, etc.)?

The Operating System, Network, Tcp, Application Processes, and Windows Service layers of the layer model in Figure 1 have been discussed in the Monitoring Unix and Windows Servers document. This document explains in detail the Windows Service layer is the MS SQL Server layer.