Being a production or operational DBAs, whomever I know may rely on third party monitoring tools, which is not questionable. There are many good SQL server-monitoring tools help us to keep an eye on our database production infrastructure.
What I suggest/prefer is to have native alerts as first line of notification or this option could be use as failsafe. When I talked to my fellows about the options, I was surprised that either many did not know or did not realize how useful they all could be. First, there is no dependency on any monitoring tool; second alerts will be in your existing SQL echo system (database mail, operators etc.)
Here are the five alerts I like to suggest. However, it is entirely up to the one to change thresholds based on their production workload or not to turn them into annoyingly noisy. This post is to let us realize about the native alerts.
You can setup these alerts by a Right Click on Alerts folder and click on new alert. A dialog box will open give the name you want and the 2nd Type is drop down list where you would choose “Sql Server Performance Condition Alert”
These are alerts which are needed in an environment I work with. But you can choose whatever you think are suitable for your production environment. There are hundred of those and many of those will make sense for your work environment.
5NatvieAlerts click here to download to create alerts I mentioned above.
adrshen 