Learning About the Cloud?
Are you up on Cloud Technology? If not, Join Hostway on Tuesday, October 12th at 11:30AM EST/10:30AM CST for a practical 30 minute webinar on the opportunities presented and the real world prerequisites for deploying database assets and operational support to the cloud. The session will illustrate how cloud-based database automation can increase the efficiency of repetitive tasks, such as database installation, user administration, patch management, etc. Hurdles to the effective scaling of database assets and operations will be identified, and a best practices framework and reference architecture will be presented.
Register Today!
Featured Article(s)
Performance Data Collection (Part 1 of 2)
This session will show you some of the great stuff that we’re doing around our new performance data collection feature set for SQL Server 2008.
Featured White Paper(s)
An Introduction to Workload Tuning
Workload Tuning is just what it sounds like: tuning the performance of all processes that comprise a database workload in one… (read more)
Featured Script
admin db – can SQL Agent job be safely started
This is a commonly used hack using the undocumented xp_sqlagent_enum_jobs that will quicly tell you if a job can be started o… (read more)
Connection Pooling – Are You Using It?
Most DBAs are aware of an SQL Server connection string as it applies to database mirroring. However, the database connection string also defines if an application pools connections when communicating with the database. If you are not strong on this you may want to become so.
A lot of network traffic is required for an application to connect to an instance of SQL Server. If you were to run a simple query such as "SELECT GETDATE()" against an instance of SQL Server a few hundred times, one set using connection pooling and another set not using connection pooling, you would see a difference in execution time. This is more true if you are executing over a network.
In order to speed up execution, Microsoft introduced connection pooling. Each client application maintains a connection pool to talk to databases. When a connection to a database is required, ADO.Net first looks in the connection pool to see if there is an existing connection not being used which has the same server and database. If so, it simply uses that already existing connection to communicate with the database rather than re-establishing contact with the server. There is a lot more to it than that, but this provides some groundwork.
As a result, connection pooling can increase the number of active connections to a database. Depending on your licensing model, this may become an issue. You could run out of connections waiting for one of those connections in the pool to become available. Once I was trying to monitor active queries and found a constant 20 connections regardless of load. That is the view of connection pooling at work from the database perspective. Although the query may have completed long ago, the connection is retained for future activity.
On a plus side, connection pooling can increase your application performance without any extra effort,. Have your developers consider connection pooling; encourage them to set the pool size appropriately. By default the pool size it is rather large. So, if you have a thick client you may want to consider making the pool size much smaller.
Just so you know, each application has a separate connection pool…it is not shared across processes.
Are your apps using connection pooling? How to you establish policies with your development and administrative staff?
Drop me a note and share your thoughts or experiences…
Cheers,
Ben