January 21, 2009 at 11:32 am
Do sleeping connections consume much resource or are they more or less harmless? If I have 200 connections, 190 of which haven't done anything for a few hours, should they be killed? Is there a way of seeing how much memory is being used by them?
January 21, 2009 at 1:35 pm
One way to investigate, is to run the SQL Server Profiler with the default template. If you see an Audit Login event, then there is a new connection.
If you see the even RPC:Completed with the text "sp_reset_connection", this means that a connection was reused from the pool. If you never see any sp_reset_connection, then you know you have a problem.
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 22, 2009 at 3:47 am
How do I see how much memory these connections are using?
January 22, 2009 at 4:15 am
SQL Server: Memory Manager: Connection Memory (KB) will give an hint of how much memory is getting used by SQL Server will give the information also try using DBCC memorystatus.
http://support.microsoft.com/Default.aspx?id=271624
"More Green More Oxygen !! Plant a tree today"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply