New SelectViews Show!
Craig Utley joins us on the show today – technology, databases and a lot more.
[Watch the show here]
Featured Article(s)
Import/Export Options (Part 2 of 3)
We’re going to spend a little bit of time in this session talking about import/export options, and the things that are available to you in the SQL Server Native Tools about how to get data quickly in and out of SQL Server. This is something that perplexes database developers quite a bit, in terms of “Well, how do I get sample data into my system?” Or, “How do I get data from another source into the database that I’m working with; how can I load that?We’ll spend a little bit of time just talking about a couple of tools that are available to you in terms of the Import/Export Wizard, and how we can go about generating files that are necessary to use for importing and exporting data.
Featured White Paper(s)
eBook: Oracle Performance Tuning with Solid State Disk by Mike Ault
The book is written by Oracle expert, Mike Ault, with contributions from Jamon Bowen, Joe Bromley, and Matt Key, all experts … (read more)
Featured Script
— dba3_DBMS_GlobalQueryStringCursor_DemoExecLoginUsers
demo adapted to implement execution of a procedure in within each DB… (read more)
Connection Pooling
I’m really planning on adding more content for Agile techniques for BI. In the mean time, I wanted to post a response from Mike on connection pooling. He is writing from a developers perspective.
One of the things I wanted to clarify is that connection pooling is great if used property. It is terrible if used incorrectly. The thing that makes connection pooling powerful is that it maintains an open connection with the database after the client has finished, so that the connection does not have to be re-established. So, when the client is done, the connection remains open and returns to the pool.
When the next client requires a connection it will ask the pool if a connection is available, and receive an already established connection. If not is available, and the pool limit will not been exceeded, a new connection will be created, and returned to the pool.
If the client does not close the connection by calling the close() method on the connection, it will not be returned to the pool for use by another client. So, be sure to Close your connection, but do not dispose it. If you dispose of the connection it it will not be returned to the pool. If you close it, the connection pool will instead of closing the connection from the data source, return it to the pool as an opened connection to be re-used for another request.
It is for this cause that if you review all the open connections from SQL Server, you will see all the inactive connections that were returned to the connection pool. If you have a lot of peak activity followed by periods of low activity, there may be many idle connections in the connection pool.
Here is an msdn link with more detail if you are interested.
So, here’s Mike’s input. Thanks for the comments, Mike.
Feel free to send your comments to me here.
Cheers,
Ben
From Mike:
I can provide some insight into database connection pooling from a .NET developers perspective. Troubleshooting connection pooling problems from the database side can sometimes leave people frustrated, as it is difficult to see what exactly a developer has done within their application. The DBA might only see 20 constant connections with no errors, but the developer sees that there are no connections available.
I use connection pooling in most applications I write. Occasionally, a client will ask me to not use pooling, at which time I try to educate them on the many benefits from a performance standpoint. Usually, someone who doesn’t want to use pooling either doesn’t realize the value, or has had a bad pooling experience in the past.
One such bad experience I’m familiar with stemmed from using pooling within a web service. The service was written in the early days of .NET when developers had questions such as "When do I close/dispose this connection?". Returning a dataset from a method before closing the database connection caused the connection count to increase until the pool limit had been reached. Once the limit was reached, no new connections could be made until the pool timeout period elapsed. Most of the time, the web service worked fine as the limit was set to 100 connections and that limit was rarely reached. As time went on, when more applications started calling the web service, occasionally there were no more database connections available. Modifying the code to close the connection before returning the dataset allowed the connection to be returned to the pool and fixed the problem.
It’s been my experience that most connection pooling problems can be tracked to application code. When you throw IIS into the mix as a web service provider, there may be other issues to consider, but normally these are security or configuration related. Depending on your database server’s licensing model, the database might reach it’s absolute maximum connection limit and cause a problem, but this should be identified within the database logs. If you’re having pooling problems, your database logs are clean and you have database connections available within the database server, have your developer review their code for proper connection usage.