|
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:
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. |
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 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. 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.
(07 Jan '14, 13:46)
TonyV
|
|
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). Now that's interesting. Thanks for that information, it will be useful.
(08 Jan '14, 11:10)
TonyV
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? (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...)
(08 Jan '14, 11:59)
Volker Barth
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.
(08 Jan '14, 12:54)
JBSchueler
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.
(08 Jan '14, 13:01)
JBSchueler
so to sum it up:
(08 Jan '14, 13:45)
TonyV
I think you have summed it up perfectly. :-)
(09 Jan '14, 09:08)
JBSchueler
More comments hidden
|
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.