Help Menu

This help page is for version 7.3. The latest available help is for version 8.0.

How to Monitor Microsoft SQL Server

Listed below are some of the recommendations for monitoring a Microsoft SQL Server Database counters and services. These generally refer to SQL Server 2012, but most of the details also apply to other versions as well.

SQL Server Services

Microsoft SQL Server consists of three core services for the database engine. They are the SQL Server service itself (or MSSQLSERVER), the SQL Server Agent (SQLSERVERAGENT), and the SQL Server SQL Browser. Then there are many add-on or supplemental products, tools and reporting services that Microsoft offers that you can monitor but again they are optional.

The core services, the services that should be monitored is the MSSQLSERVER & SQLSERVERAGENT service. The SQL Server Agent (SQLSERVERAGENT) is the job scheduler for SQL Server and handles other maintenance tasks. All applications and communication with the relational database engine happens with the SQL Server Service (MSSQLSERVER).

Services to Monitor

ServicesService Name
SQL Server ServiceMSSQLSERVER
SQL Server AgentSQLSERVERAGENT

SQL Server Counters

Microsoft SQL Server provides many objects and counters that can be used to monitor the health and activity of an SQL Server instance running on a server. An object in an SQL Server instance can have one or many counters depending on the resources that are available and each can be monitored. Each of the following counters below are counters that are recommended to be monitored for the performance of your SQL server instances.

Performance Counters to Watch

Object\CounterDefault Threshold
System\Processor Queue Length< 4 per CPU
SQL Server: Buffer Manager\Page Life Expectancy> 300
SQLServer:General Statistics\User ConnectionsWatch counter over time to get a high and low count, then set your threshold to meet your requirements.
SQLServer:SQL Statistics\Batch Requests/secThe higher the better. Watch counter to find a low threshold that meets requirements.
SQLServer:SQL Statistics\Compilations/sec10% of Batch Requests
SQLServer:SQL Statistics\Recompilations/sec10% of Compilations
SQLServer:Locks\Lock Waits/sec0
SQLServer:Access Methods\Page Splits/sec< 20 per 100 Batch Requests/Sec




References

http://msdn.microsoft.com/en-us/library/ms190382.aspx
http://www.quest.com/techbrief/sql-server-perfmon-counters-poster811635.aspx

PA Server Monitor

Help Map