Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
 Posted - 2007-09-20 : 12:48:35
|
| folksWe have a situation where people just login run complex queries and then just don't shut off their query analyzer of apps.Some of these chew up a lot of CPU cycles and unless we kill the query the CPU cycles are not released.I see some of them are idle for over 1-2 hours or more, their last_batch would be at least 2 hours old.The question is, "Is there a setting in the sql server whereby we can automatically disconnect idle threads?"RegardsParesh MotiwalaBoston, USA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-20 : 12:50:45
|
| If the connections are truly idle, then they aren't consuming any CPU cycles. The only thing that they would be consuming is a very tiny amount of memory on the database server. They would probably be consuming a bit of memory on their client machine if they have the result set displayed, but that would not impact the database server.No such setting exists.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-09-20 : 13:16:17
|
| Hi tara, thanks for the quick response, but then why would sysprocesses show:spid status login_time last_batch cpu59 sleeping 2007-09-20 11:32:52.467 2007-09-20 11:46:44.067 12623119 sleeping 2007-09-20 08:54:52.217 2007-09-20 11:58:56.777 22486RegardsParesh MotiwalaBoston, USA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-20 : 13:18:10
|
| That just shows how much CPU the connection has used since it got connected. It's a cumulative number. It does not mean that the particular query has used that much.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-20 : 23:22:16
|
| If app uses connection pooling, you'll see sleeping connections on the server. App will reuse them, connection pooling saves time and has better performance. |
|
|
|
|
|
|
Idle Connections termination