Critical performance counter for sql servers through Nagio

[dbasupport]

Hi Team ,

We would to monitor the sql server and sql server database through Nagios XI .

1. I would like to know which most critical performance counter from sql server database point of view .

2. In Nagios , we have two wizard related to MSQL database , is that sufficient ?

3. There is any other plugin through which can monitor the performance counter of our choice instead of going through database wizard of Nagios 4. From window OS point of view which other services which needs to be monitor apart from memory, CPU load , Disk uasge ?

Please advice .


Thanks

[benjaminsmith]

Hi @dbasupport,


There are a lot of metrics that you can monitor for MS SQL Servers and the Wizards are an easy and time-saving way of getting started. My suggestion would be to start with those as they only take a few minutes to get checks running, and then explore other metrics that are critical to your environment. I probably know about monitoring and Nagiso XI than MS SQL server, so here are some resources to help get you started.

For performance counters, you can use NCPA to set up checks.
Nagios XI - Monitoring Performance Counters in Nagios Using NCPA
NCPA Reference Guide

There are also many additional plugins for monitoring MS SQL servers on the Nagiso Exchange:
https://exchange.nagios.org/directory/P ... /SQLServer

Here's the guide for setting up checks in Nagios XI using other plugins:
https://assets.nagios.com/downloads/nag ... ios-XI.pdf

Lastly, from the OS point of view, you can use NCPA again to run those checks (CPU, Memory..etc). Those can be easily setup by running the NCPA wizard in Nagios XI.

I hope that helps answer a few of your questions, let me know what you need clarification on.

[dbasupport]

Hi benjaminsmith,

Thanks for information updated , basically we would be monitoring the sql server database through Nagios XI .
We have two plugin as default for monitoring the sql server database through sql server wizard from Nagios .
These are two plugin ./check_mssql_database.py and check_mssql_server.py associated with sql server database wizard .
Apart from these our team would like to exlpore more critical performance counters for sql server database .
I have attached SQL Server - Performance Poster from this chart you will able to see all the performance counter .
From the attached , I need to identify 10 -15 performance counter which are critical from sql server database point of view .
Any help in identifying the critical performance counter would be of great help for me .


Thanks.

[benjaminsmith]

Hi @dbasupport,

While I wouldn't consider myself an expert in the area, I did some research, and here a few suggestions to help get you started. At the end are a few general metrics related to server performance that should be included.

User Connections
A good general metric that will help track changes in availability or performance.

Batch Requests/sec
Monitors the number of page splits per second which occur due to overflowing index pages and should be as low as possible

Lock waits/sec
The number of requests causing the calling transaction to wait for a lock (per second)

Page Life Expectancy
Number of seconds a page will stay in the buffer pool without references

Total Pages
Number of pages in the buffer pool

Checkpoint Pages/sec
Number of pages written to disk per second by a checkpoint

Buffer Cache Hit Ratio
Percentage of requested pages found in the buffer cache

Compilations/sec
Number of times that Transact-SQL compilations occurred, per second (including recompiles). The lower this value is the better. High values
often indicate excessive adhoc querying and should be as low as possible.

Recompilations/sec
Number of times, per second, that Transact-SQL objects attempted to be executed but had to be recompiled before completion. This number
should be at or near zero, since recompiles can cause deadlocks and exclusive compile locks.

General CPU, Memory and Disk I/O metrics
Used Memory
CPU usage %
Disk read IO/sec
Disk write IO/sec

Hope that helps! Once you start setting this up, we would appreciate your feedback.
Benjmain

[dbasupport]

Hi Benjaminsmith ,

Thanku so much for the information and update , even I had research and considered the same performance counter which you have mentioned .

Thanks

[benjaminsmith]

Hi @dbasupport,

Your welcome! Did you have any other questions or may we close this thread?