Friday, April 06, 2007
Speaking of SQL
I have run into a situation where I am not sure if my SQL server is operating normally, or if it's madly running out of control and into oblivion (which is a great, fun game btw).
I manage and design a website www.toolup.com.
On this site, I use SQL extensively. And the ASP pages that I write use connections and recordsets quite a bit. When I go to look at all the processes running on the server I find hundreds of sleeping connections. Is this a result of connection pooling by the system. Is it a result of connection pooling by the IIS web gardens, of which I have probably 64+64 + 32 for a total of 160 actual web processes running the website.
The website seems to respond very quickly. On the order of 1/2 second or so. Often times returning in a 1/10th of a second. So performance is not an issue.
I have run through the code and made sure that I have closed my connections.
However, doesn't the connection get automatically closed upon the closure of a script? Is running conn.close() redundant?
does conn.close() happen when you execute
set conn = nothing?
So while I contemplate this fine feature of SQL - I find that I am staring at roughly 647 pids, most of which are happily sleeping, and my server's CPU load is fairly low all of the time, with occasional spikes to the disk or CPU load.
I just can't help but think that all of these pids should be going away after they complete.
I manage and design a website www.toolup.com.
On this site, I use SQL extensively. And the ASP pages that I write use connections and recordsets quite a bit. When I go to look at all the processes running on the server I find hundreds of sleeping connections. Is this a result of connection pooling by the system. Is it a result of connection pooling by the IIS web gardens, of which I have probably 64+64 + 32 for a total of 160 actual web processes running the website.
The website seems to respond very quickly. On the order of 1/2 second or so. Often times returning in a 1/10th of a second. So performance is not an issue.
I have run through the code and made sure that I have closed my connections.
However, doesn't the connection get automatically closed upon the closure of a script? Is running conn.close() redundant?
does conn.close() happen when you execute
set conn = nothing?
So while I contemplate this fine feature of SQL - I find that I am staring at roughly 647 pids, most of which are happily sleeping, and my server's CPU load is fairly low all of the time, with occasional spikes to the disk or CPU load.
I just can't help but think that all of these pids should be going away after they complete.
Comments:
<< Home
At the end of a procedure, locally scoped objects are supposed to be closed automatically. Closing a locally scoped connection object at the end of a script is both good practice and redundant. However, why trust Microsoft? Close the connection objects yourself. In additon, set the conneciton object to nothing to force the deallocation of the object.
"Set cnConn= nothing"
Post a Comment
"Set cnConn= nothing"
<< Home
