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.
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).
|SQL Server Service||MSSQLSERVER|
|SQL Server Agent||SQLSERVERAGENT|
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.
|System\Processor Queue Length||< 4 per CPU|
|SQL Server: Buffer Manager\Page Life Expectancy||> 300|
|SQLServer:General Statistics\User Connections||Watch counter over time to get a high and low count, then set your threshold to meet your requirements.|
|SQLServer:SQL Statistics\Batch Requests/sec||The higher the better. Watch counter to find a low threshold that meets requirements.|
|SQLServer:SQL Statistics\Compilations/sec||10% of Batch Requests|
|SQLServer:SQL Statistics\Recompilations/sec||10% of Compilations|
|SQLServer:Access Methods\Page Splits/sec||< 20 per 100 Batch Requests/Sec|