[Note from Pinal]: This is a seventh episode of Notes from the Field series. The biggest issue any DBA have with their server is the health of it. We all want to configure our server optimally and know any issue with it if it faces one. This is one of the MOST essential tasks which developer often miss it. Mike comes up with another episode of the notes from the field where has addressed this essential element of SQL Server.
In this episode of the Notes from the Field series database expert Mike Walsh explains a very crucial issue DBAs and Developer faces in their career – how you perform a configuration checking for your database. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.
Last time I was invited to post some thoughts here, I talked about the importance of understanding SQL Server configuration options and checking for them. In fact I unveiled a new script (found here) for checking your configuration options.
This post continues with the theme of getting your environment off to a good start and being sure of its health. There are many things to do on the front of knowing your environment is healthy and ready to move forward with your business. When we do a WellDBA™ Exam at Linchpin People we look for problem settings and configurations as we discussed last week. We also look, though, for missing best practices.
One of those things we look to see happening is – monitoring and alerting. This can take many shapes – but the theme we are searching for is – “Is someone looking over the server? Does someone in IT have a chance to find an issue before the users start complaining?”
There are many ways to do this – some great third party monitoring products, some home grown scripts – and SQL Server Alerts.
What Are SQL Server Alerts?
Basically – SQL Server knows when certain events are fired, you can create alerts to look for these situations and then choose to do something with them.
For instance you can create an alert for an error message containing a certain text if you need to know when an application raises a custom event. You can create performance alerts when certain perfmon conditions are tripped. Or you can create more generic alerts that may fire whenever an error of a certain severity level is raised.
You also have choices on what to do when an alert is triggered. You can e-mail a predefined operator, run a script, or log the information.
What Do We Look For?
We want to see a DBA team looking for and responding to warnings and errors, period. It really is that simple. We like to see you understanding the status of the environment, and responding when something goes away from normal/healthy.
Specifically, with alerts, a really good start would be to see you with a defined alerting strategy as simple as:
- Having DB Mail setup properly. (More Info)
- Having an operator or operators set up. (More Info)
- Having one alert for each error severity 16 – 25. (More Info)
- Having the operator(s) signed up for each of these alerts. (More Info)
SQL Server is not a set-it-and-forget it system. You can’t just deploy a SQL Server instance and hope things go well. You need to proactively configure it and then cautiously watch it for signs of trouble. Using SQL Server alerts is one way to get a head start on trouble before it brings you down.
If you want to get started with performance analytics and triage of virtualized SQL Servers with the help of experts, read more over at Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
I am trying to set up sql server performance condition alerts for procedure cache hit ratio (get an error). Is there a source or article that states what this value should be. I am checking for pchr = 300) but for all of these counter especially locking and latching thredsholds to determine if I have a concurrency\blocking issue going on.
correction: checking for pchr< 90% and page life expectancy <300. I put in a value of 90 for pchr and it causes the alert to fail\error. I also need threshold values for locks\sec and latches\sec to determine is their is a concurrency\blocking issue.