Most database monitoring systems aren’t from the database vendors as you might think, but a hodge-podge of 3rd party vendors that seem to want to charge more than I make in a lifetime for database monitoring software — try finding low cost monitoring software for DB2 on the mainframe.
They typically use standardized, and often deprecated, monitor counters that when used for their product, interfere with any other monitoring products you might use. For example, if the Operations Department is using Nimbus to monitor the network, VoIP, hosts, tape archival systems, and the database servers to ensure that they are running, what happens when the DBAs want to use DBA Expert? The two products (keep in mind that I chose the products for the example at random) will trip over each other – neither will provide reliable metrics of the databases.
The front ends for the monitoring products always seem to show a fancy GUI full of bright colors, dials, graphs, and the latest and greatest designer kitchen sink. They are very rarely willing to provide any documented API or mechanism for you to obtain the data from their product without a nasty NDA. The premise is that you will use their front end to display and analyze the monitoring metrics.
The database vendors, themselves, are largely to blame. The monitoring APIs that they offer assume that you will only be using a single monitoring system. For example, in Sybase’s ASE, the new API is to use their MDA tables to obtain performance metrics but the problem comes in when the monitoring software would use multiple methods to obtain additional information that may not be (easily) obtainable from the MDA tables. sp_sysmon will reset several monitoring counters unless you call it with the ‘noclear’ option. Unfortunately, the ‘noclear’ is not widely known and rarely used in the monitoring software. Of course, this is just an example of multiple monitoring APIs from a database vendor.
You know what? I don’t care about the vendors’ fancy front ends. Give me a web service that I can access and use the monitoring metrics in another application, a PDA, etc. A few vendors have tried to offer an API but they are often so damned complicated that you would have had to work at the company to understand the API. Don’t even get me started on vendors keeping their APIs updated.
Update: Thanks goes to Peter Dorfman of Sybase to helping clarifying that the MDA tables in ASE ‘clear’ only on a connection basis. That means if you look at monDeadlocks on connection #1 twice, the first select might show 5 rows and the second 0 rows. If you ran the select on connection #2 sometime later, you would see the 5 rows plus any other deadlocks that might have occurred since then. I wasn’t very clear on that as I was (in my head) also including sp_sysmon and other monitoring options that would conflict with the MDA tables.