Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
ASP.NET 1.0 and 1.1 Professional For advanced ASP.NET 1.x coders. Beginning-level questions will be redirected to other forums. NOT for "classic" ASP 3 or the newer ASP.NET 2.0 and 3.5
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 14th, 2005, 12:30 PM
Authorized User
 
Join Date: Nov 2004
Location: , , .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default MyConnection.Close does not kill process

We have been trying to do a little performance tuning on our MS SQL Server 2000, and one of the things we have noticed is that if you use myconnection.close() and myconnection = nothing and you look at the process info in sql server, the processes are still running on the sql server. We use some Access applications and when you close the application it ends the process. Is there anyway to kill the process using ASP.Net like it does in Access?

busher
 
Old December 14th, 2005, 02:19 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

I think what you're looking at is connection pooling, a feature enabled by default on your server.

When you close a connection that is pooled, it's not destroyed but returned from the pool. When your require another connection with the *exact* same connectionstring, you get one from the pool, instead of an entirely new connection.

Although it may not seem so at first, connection pooling is used to increase the performance of your site, so it's better if you leave it turned on....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old December 14th, 2005, 03:48 PM
Authorized User
 
Join Date: Nov 2004
Location: , , .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

So with this pooling, is there a big resource drain on the sql server? With the logic you just mentioned, and I am in no position to argue it, why does access open one, and then destroy it immediately, but ASP.Net doesn't? I am just trying to learn this stuff and help our DBA get some of these "problems" fixed or have information to show that they are "good".

How long does the "pooling" process stay active on SQL Server 2000, before it timesout?

busher

 
Old December 14th, 2005, 04:03 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

I am not an expert on connection pooling so I can't give you all the answers.

While I guess pooled connections mean a slight performance implication on your SQL Server, I think your system as a whole will benefit from it. Since you no longer need to create connections everytime you need one, you can serve more users in less time, resulting in increased performance.

To read more about pooling for SQL Server, take a look at this: http://www.sql-server-performance.co...ling_myths.asp

I am not even sure if connection pooling is enabled for Access. I guess it is, since it's an MDAC feature, but you don't see it in Access. Wild guess, though. Maybe this sheds some more light: http://msdn.microsoft.com/library/de...t/pooling2.htm

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old December 15th, 2005, 10:07 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

I'd like to revisit your original post:
Quote:
quote:Originally posted by busher
 ...and you look at the process info in sql server, the processes are still running on the sql server. We use some Access applications and when you close the application it ends the process.

When you say "process info in sql server" do you mean the MSSQL Server process itself on the separate server running the database? When you say "Access applications" do you mean a regular access database or are you connecting to MSSQL thru access?

MSSQL server will remain running all the time, it runs as a system service. It won't start and stop on the demand of a connecting application. That kind of behavior would make it basically impossible to have a functional application because the startup time of the database is too long to be practical.

When you run an access application, it's about the same as running any regular desktop application that starts up when you run it and shuts down when you close the main window. So this might be what you are seeing in the processes.

-Peter
 
Old January 11th, 2006, 06:12 PM
Authorized User
 
Join Date: Nov 2004
Location: , , .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

No, I don't mean the SQL Server process itself. Under the Enterprise Manager-->Database-->Management-->Current Activity-->Process Info, it gives the Process ID, Context ID, User, etc. When I open a connection through and asp.net page and run a query, it shows my username and the process id and so on. When I use the conn.close(), conn = nothing, and conn.dispose(), it does not get rid of the process running on sql server, NOT the os system. Even if you refresh it, it does not go away even after the web page has been closed. I hope this is a little clearer. When I reference MS Access, we use it as a reporting tool front end only. We connect to the SQL Server for the data, and when Access is closed, the process goes away on SQL Server, but it doesn't do that using ASP.net.

Thanks for the help!!!

 
Old January 12th, 2006, 03:21 PM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

Ok, well I suspect you are seeing connection pooling in action. I too am not an expert on connection pooling, but here is what I suspect is going on:

When you open and close an MSSQL connection from a web page it's being handled by the ASP.NET worker process. The connection is pooled and the pooled connection is linked to the ASP.NET worker process. That process remains running even though your page is done executing. That way the next database connection requested can come from the pool and be that much faster. Only if you stop the ASP.NET worker process will the pooled connection link be lost and eventually the connection will be dropped and cleaned up.

I think that there shouldn't be much concern for a connection to SQL staying alive as long as you aren't seeing accessive connections being created.

-Peter
 
Old January 12th, 2006, 04:15 PM
Authorized User
 
Join Date: Nov 2004
Location: , , .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Peter, you and Imar are the men. You were exactly right. I set pooling = false in the conn string and they didn't even appear on the process info. Our company is rather small in regards to users, so I don't think it really buys us anything to use pooling, but we will test the crap out of it and see. Thanks for the quick responses and thanks for the help.

-busher

 
Old January 13th, 2006, 10:05 AM
planoie's Avatar
Friend of Wrox
Points: 16,481, Level: 55
Points: 16,481, Level: 55 Points: 16,481, Level: 55 Points: 16,481, Level: 55
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2003
Location: Clifton Park, New York, USA.
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

You'll ALWAYS gain from pooling (compared to the extremely small cost). If you make 3 connections to the database in a single page hit, without pooling the connection will have to be constructed from the bottom up every time. Even across page hits, while the pooled connection is there, subsequent hits to the database will be faster. Given that in most applications establishing the DB connection is the most expensive procedure you'll see significant gains using pooling.

-Peter
 
Old January 13th, 2006, 06:00 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Yeah, I agree with Peter. Connection pooling is on by default, which means there must be a good reason to do so.

Everything I have read about connection pooling (which is quite a lot) suggest to leave / turn it on, unless you have very compelling reasons to turn it off. In your situation, I am sure you'll benefit from keeping it on.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.




Similar Threads
Thread Thread Starter Forum Replies Last Post
process.startinfo opens new instance of process Anypond General .NET 0 August 28th, 2008 05:35 AM
Browser close when screen saver close Rehanrana Pro VB 6 1 April 7th, 2008 03:09 AM
Kill all winword.exe process before opening Word mckly Pro VB Databases 1 March 6th, 2008 01:34 AM
make outlook.exe process close properly dgr7 Beginning VB 6 3 April 18th, 2007 03:51 PM
Process.Kill() online problem kool_creative General .NET 3 October 30th, 2006 12:07 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.