Best way to monitor database availability

[Former Member]

My program runs SQL Anywhere 12.0.1.3985 as an embedded database. One of the requirements is that it is supposed to monitor the availability of the database. That is, the program has an indicator marked "DB" that is Green when the database server is up and Red when the database server is down.

Up until this point, I've implemented this feature using a timer that executes the following code once a second:

public static function IsDatabaseUp() {
    using ( CarSystemEntities context = new CarSystemEntities() ) {
        context.CommandTimeout = (int) AdvancedSettings.TimeOut.TotalSeconds;
        EntityConnection connection = (EntityConnection) context.Connection;
        try {
            if ( connection.State != System.Data.ConnectionState.Open ) {
                connection.Open();
            }
        } catch ( Exception ) {
            return false;
        }
    }
    return true;
}

This has worked fine, however, we've noticed that dbserv12.exe's CPU usage approximately doubles on some machines while data is transferring to our database from our server. When I stop my program, even though data is still downloading from the server, the CPU usage for dbserv12.exe is much lower.

I have since replaced that code with the following:

// The following 2 lines declare properties of this class.
private static CarSystemEntities DbIsUpContext { get; set; }
private static EntityConnection DbIsUpConnection { get; set; }

public static function IsDatabaseUp() {
    if ( DbIsUpContext == null ) {
        DbIsUpContext = ConnectToDatabase() as CarSystemEntities;
    }
    if ( DbIsUpConnection == null ) {
        DbIsUpConnection = DbIsUpContext.Connection as EntityConnection;
    }
    try {
        DbIsUpConnection.Open();
        DbIsUpConnection.Close();
    } catch ( Exception ) {
        Log.Error( "Database connection is down." );
        return false;
    }
    return true;
}

This seems to cause dbserv12.exe to use much less CPU as Task Manager shows its CPU usage staying pretty level whether my program is running or not. But the question is why? Is instantiating an Entity Framework context that expensive?

I really need the answers to the following three questions:

1. Why does the first version of this method seem to be so expensive?
2. How to tell which queries are running & causing high CPU usage in dbserv12.exe?
3. What's the best way to detect that the DB server is still running ("best" in this case means the method with the least impact on server CPU activity).
   

Edit:

I've just found that the change I've described in this post actually makes no difference in dbserv12.exe's CPU usage. It appears that the problem occurs when one of the tables reaches a certain size, around 9-10 million rows. I changed the frequency of the timer from 1 second to 1 minute & the CPU usage stays low. Unfortunately, I need that code to run more often than that. I'm going to try a frequency of 5 seconds & see what impact that has, but there has to be a better way.

[Breck_Carter]

Chances are the answer lies on the client side.

Check the classes you're using to see if there is any code under-the-covers that performs unnecessary work.

[jack_schueler]

If you maintain an open connection to the server, then a query of the connection State property causes the provider to ping the server to see if it is still alive (the least expensive round-trip request to the server that we have).

System.Data.ConnectionState state = _conn.State;

If the state is "System.Data.ConnectionState.Open", then you know that the server was pinged and is still around.

However opening a connection, and then closing it, every time you want to know if the server is around is a lot more expensive (for example, the pooling set up/tear down cost).

[Former Member]

Now that's interesting. Thanks for that information, it will be useful.

[VolkerBarth]

Just to clarify:

When not using ADO.Net but ODBC, is the following a similar cheap method (via SQL_ATTR_CONNECTION_DEAD) to get the connection state without actually issuing a query?

Getting connection attributes

(If my understanding is correct, it might also fail if the client has been disconnected though the server is still running but that's not my question here, and it would also apply to the ADO.Net ConnectionState, methinks...)

[jack_schueler]

A call to SQLGetConnectAttr( SQL_ATTR_CONNECTION_DEAD ) returns the current state of the connection. It does not initiate traffic to the server. So TRUE would indicate that an earlier request to the server failed because you had been disconnected (for example, by a forced disconnect, or an IdleTimeout, or liveness timeout). http://dcx.sybase.com/index.html#sa160/en/dbprogramming/db-register-a-callback-esql.html*d5e30146

It would not necessarily mean that the server was down.

[jack_schueler]

Further to my comments about the SQLA .NET provider.

If the state is "System.Data.ConnectionState.Closed", then you know that your connection to the server no longer exists. To determine if the server is still available, you would have to re-attempt the connection. A failed attempt does not guarantee that the server is down either since there are a number of reasons why you might not be able to reconnect - for example, connection limit exceeded. At that point, you'd need to do the hard work to determine if the server is up and running.

[Former Member]

so to sum it up:

1. If you don't have a connection, try to connect. If that fails, the server may not be down, so use other means to determine if its up.
2. If you have a connection, get the state. If it is "System.Data.ConnectionState.Open", you're connected & the database is up.
3. If the state is "System.Data.ConnectionState.Closed", you don't have a connection. See step 1.

[jack_schueler]

I think you have summed it up perfectly. 🙂

[jeff_albion]

What's the best way to detect that the DB server is still running ("best" in this case means the method with the least impact on server CPU activity).

Tony, how is the actual database server running on the system - is it set up as a system service (via the Service Utility (dbsvc))? If the database is set up as a system service then you don't need to check the actual database connection necessarily - you can just query the Windows service status directly from .NET.

Even if you are still trying to test the actual connectivity from .NET, you shouldn't need to 'check' periodically on a newly constructed and separate connection - if a connection is 'down', it should immediately get back an exception when it tries to execute an operation on an existing connection and get a communication error back. You should be able to catch this exception and set the connection state appropriately.

---

I've just found that the change I've described in this post actually makes no difference in dbserv12.exe's CPU usage. It appears that the problem occurs when one of the tables reaches a certain size, around 9-10 million rows.

I wouldn't expect that it would. The CPU usage of the server is going to largely depend on what is currently executing on the server, not 'ping' tests for connectivity.

[Former Member]

Jeff: Regarding your comments about not needing to check periodically, that is way better than what I'm doing. I was trying to do something like that at one point, but I didn't get it to work. I did get what I'm doing to work, but I'm still not sure that this is the culprit. I don't have any information that says categorically where the problem actually is. I've been assuming that the problem is where I'm looking because this is where I've done the most recent work before we saw the CPU usage escalate.

Regarding you comment about my edit, I agree, the "ping" test shouldn't be affecting server CPU usage. Its just that when I started the download process with the change I'd made, I wasn't seeing the huge amounts of CPU usage. It wasn't until much later when in the process that I started seeing the CPU usage increase.

The thing is that when I stopped my program when I saw the huge spike in dbserv12's CPU usage, the CPU usage went back down to levels I was expecting. Then, when I started the program again, CPU usage went back up. Obviously, my program's doing something to cause the spike, and this is the only process that continuously runs.

What I really need to debug this is a way to see what the server's doing so I can pinpoint which query is causing the high CPU usage.