- Solutions
-
- File Server: Ransomware Protection
- File Server: File Copy
- File Server: Audit File Access
- File Server: Storage growth reporting
- Licensing/Pricing
- Contact
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
| Services | Service Name |
|---|---|
| SQL Server Service | MSSQLSERVER |
| SQL Server Agent | SQLSERVERAGENT |
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\Counter | Default Threshold |
|---|---|
| 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:Locks\Lock Waits/sec | 0 |
| 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