Return resources to the system to improve .NET performance

by Tony Patton | Aug 15, 2006 1:40:00 PM

Tags: .NET, Databases, Storage, Application servers, Middleware, Tony Patton, performance, database, Microsoft .NET, Microsoft SQL Server Transact-SQL, server, .NET Newsletter

Takeaway: A common problem in the .NET platform is the consumption of system and database resources when developers fail to clean up after themselves when working with a database. Tony Patton notes that you can avoid such problems by releasing objects back to the system.

The promise of .NET's garbage collection feature meant no more worrying about object cleanup since .NET handles everything behind the scenes automatically. But I continually face database connections gone bad as developers fail to clean up after themselves. I'll explore this problem in more detail and suggest how you can avoid such issues in your project.

The problem

The problem with not properly closing and disposing database connections established via ADO.NET is simple—it taxes the backend database server as connections remain open and consume resources that could be better used elsewhere. Data access with ADO.NET is designed around an architecture that uses connections sparingly. Applications are connected to the database only long enough to fetch or update the data. Because the database is not holding on to connections that are largely idle, it can service many more users.

Monitor those connections

I've been working with a client that has several .NET applications built by another consulting firm, and these applications are performing well with the lone exception of managing its database connections.

The database administrator at the client site has been hammering me with messages about the number of connections open on their database. It is easy to get a peek at the connections (at any time) open on a SQL Server. The following T-SQL returns a list of open connections on an instance of SQL Server. Notice it includes the username assigned to the connection as well.

use master

select * from sysprocesses

You can view the server name (accessing the database server), program name, and login name in the hostname, program_name, and loginame columns. The following query returns these values:

use master 

select hostname, program_name from sysprocesses

With these columns, I use the following T-SQL to view only connections open by a specific server and login name (the login name used by the application in question):

use master
select * from sysprocesses(nolock)

where hostname = 'server_name'

and loginame = 'login_name'

The T-SQL allows me to quickly view the connections open by a certain application. Each of the client's applications utilize a unique logon, so it is easy to assign open connections to an application.

The solution

The solution is simple—clean up after yourself. The ADO.NET connection class contains a close method to properly close any connections that you may have opened. It is good to think of the open and close methods as bookends. Here is a quick rundown of the methods you should be concerned about in regards to cleaning up database connections:

• Open: A database connection is opened.
• Close: A database connection is closed.
• Dispose: All resources associated with the database connection are released. It forces garbage collection on the object. Consequently, it can be used to ensure no resources are being held after our connection is used. Also, the dispose method of a connection object automatically calls its close method. Nevertheless, I always call the close method before dispose. An exception is thrown if an object's dispose method is called more than once.
• State: The state of the connection object is returned. It is good to check if a connection is open before closing it.

It is easy to get an idea of how these methods may be used. For instance, check out the VB.NET code in Listing A. Listing B contains the equivalent C# code. You may view the code as overkill, but all of the database-related objects are closed, disposed, and released, and nothing is left for automatic garbage collection as we handled our business.

A common solution to the problem of open connections is using a single connection object that is opened and closed by each method, class, or sub that requires database operations. The objects often exist in a singleton database access class that centralizes connection management.

Don't forget the files

Now failing to properly close connections is not restricted to database access; it is also a problem when working with files or any kind of stream object. A good example is writing data to a text file. The C# snippet in Listing C creates a text file and adds arbitrary text.

The key is to remember to close the file once the addition of data has completed. While it seems self-explanatory, the problem does creep into production applications. The equivalent VB.NET code is in Listing D.

Watch what you are doing

The days of memory leaks and related problems with past Microsoft technologies may be a distant memory, but the .NET platform is not immune to problems. One common problem is the consumption of system and database resources as developers ignore cleaning up after themselves after working with a database. Developers can avoid such problems by releasing objects back to the system after they are utilized. The ADO.NET objects contain close and dispose methods to simplify the task, so don't forget!

Miss a column?

Check out the .NET Archive, and catch up on the most recent editions of Tony Patton's column.

Tony Patton began his professional career as an application developer earning Java, VB, Lotus, and XML certifications to bolster his knowledge.

Print/View all Posts Comments on this article

I prefer the using() rnshayes@... | 08/16/06

I agree GoboSlayer | 08/16/06

Using Statements Are Great But There Is One Drawback Logos-Systems@... | 08/16/06

I like to visualize it KeeBored | 08/16/06

Nothing forces garbage collection GaltSalt | 08/19/06

Keeping Single Connection mattohare@... | 08/28/06