Databases must be designed and developed to be easy to maintain. From the start, a database should be simple to check, monitor and study. Monitoring systems can only assist; it is up to the database developer to make things easy for those who are subsequently given the task of keeping it running.
I wish I could smile in some saintly way, and tell you that I've always written my databases, or other systems, in such a way that anyone could check it and understand what was going on. Twice, in my professional career, I've been caught out.
"Where do you work? Ah, The Imperial Bank! I once worked there. How did you like it?"
"Well, it is fine most of the time…but evidently we once had a wild man developer who wrote an essential database. It uses all sorts of unusual performance tricks and unconventional techniques. Nobody who worked on it subsequently ever really knew how it worked and so it was a nightmare to make changes in line with changes in the business. What is worse, if it ever hits a problem, which is mercifully rare, nobody can easily profile it or even detect what is slowing it down. Some people left the company rather than be assigned to work on it."
"How terrible. It must have been some time after I left because I don't remember it." (Gulp!)
I had a similar experience, mercifully not with a database, after I wrote a system that automatically updated thousands of PCs within a corporate network. It lasted at least two decades purely because everyone was too frightened to replace it. I've been a poor team-player at times.
I hesitate to try to list all the requirements for monitoring and maintaining databases, for such an august and learned readership as Database Weekly, but I'll try to explain what I mean.
In the same way that the unit test must precede the creation of a database 'programmable object' such as a stored procedure, so must devising the means to monitor that object, and ensure it provides the necessary evidence to reproduce and fix any problems. You must, in a sense, plan its visibility. The same goes for processes, and the entire database. The alerting system, extended events and scheduled tasks provide the means for an excellent start. They are there for a good reason. You can, of course, also use a third-party monitoring system, and these will help by providing supervision, and general metrics, create graphs and establish baselines so that you can see easily the symptoms of a problem. However, only the database designer and developer can provide the means to baseline, monitor and log the specific processes that have been created for that particular database system. The database itself, dedicated to storing data, provides a uniquely easy way of recording the monitoring and logging data for these processes.
The closer working relationship between Dev and Ops has made it much easier to understand what makes Ops people smile and hum while they maintain production database systems. It pays to discuss, and then create, a written and agreed 'monitoring standard' that its appropriate for your organisation. It is surprising how often this turns up ideas that help development as well and leave everyone wondering why the blazes they've never been done before.