March 30, 2011 at 9:37 am
Hi,
We have an application that for some reason, likes to keep queries alive until they close the client of the application.
If i run a sp_who2 i get a good old list of users that did a query on the db at 08:05 which can be anything from a select A frmo table t to a more complex query, But either way its still there now 7 hours later.
Any ideas what effect this will have? Does a SPID that has apparently returned the answer to the client, still hold the memory it says it used to run the query? or anything as evil as this? Is this the norm?
Thanks for any input.
S
March 30, 2011 at 9:52 am
I don't see where that is an issue in and of itself. One of the reasons connection pooling was invented was to fix the resource intensive philosophy of making and closing a connection for every activity a given clinet might be doing from an application.
If I am designing a client based application that is capable of generating a significant amount of database activity (i.e. queries and/or transactions) I would design it to use a nailed up connection to the server for performance reasons. Of course that might require a bit more sophistication in the error handling than I usually see (or people like to take the time to design in) but the end result is better performing (and scaling) application.
On the other hand, if the database activity is low volume and the application is simple and never has a need to scale it is generally easier to design for closing the connection after every transaction.
Yes, connections do consume resources but the performance benefit of leaving them open outweighs the any concern over what memory they consume (unless you have bazillions of them and you are on a 32-bit version of SQL Server).
The probability of survival is inversely proportional to the angle of arrival.
March 30, 2011 at 11:40 am
Thanks sturner,
New to this wacky world of SQL, advice from people like you makes it all a lot easier to get to grips with!
S
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply