May 24, 2012 at 12:48 pm
Hello,
Can anyone tell me if SQL has a transaction that is open but is in a sleep status, if that SPID is still using CPU resources? I have a server with over 4000 open transactions which most of them are not active, and I am trying to figure out what type of impact this is having on the CPU. Please note that this is a vendor application so my hands are tied as far as correcting the code.
May 24, 2012 at 12:55 pm
rodmeans (5/24/2012)
Hello,Can anyone tell me if SQL has a transaction that is open but is in a sleep status, if that SPID is still using CPU resources? I have a server with over 4000 open transactions which most of them are not active, and I am trying to figure out what type of impact this is having on the CPU. Please note that this is a vendor application so my hands are tied as far as correcting the code.
Hi, What exactly are you trying to accomplish?......are you trying to find the oldest open transactions?.........you can use DBCC OPENTRAN. Please provide some more details as to what exactly you looking to accomplish.
Regards,
TA
Regards,
SQLisAwe5oMe.
May 24, 2012 at 12:59 pm
SQLCrazyCertified (5/24/2012)
rodmeans (5/24/2012)
Hello,Can anyone tell me if SQL has a transaction that is open but is in a sleep status, if that SPID is still using CPU resources? I have a server with over 4000 open transactions which most of them are not active, and I am trying to figure out what type of impact this is having on the CPU. Please note that this is a vendor application so my hands are tied as far as correcting the code.
Hi, What exactly are you trying to accomplish?......are you trying to find the oldest open transactions?.........you can use DBCC OPENTRAN. Please provide some more details as to what exactly you looking to accomplish.
Regards,
TA
Hey, Once you find the oldest transactions.....run this command....DBCC INPUTBUFFER(spid)...to get more details.
Regards,
TA
Regards,
SQLisAwe5oMe.
May 24, 2012 at 1:07 pm
if that SPID is still using CPU resources?
No, not CPU (except perhaps a tiny amount periodically to check it for activity).
Connections themselves have some RAM overhead.
But since creating/destroying connections is quite a bit of overhead, connections are often pooled (shared); when not in use, they do not go away, but remain idle until someone needs them.
Whether or not 4000 of them is "too high", that depends on your specific server and app load.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 25, 2012 at 4:47 am
Are we talking connections or transactions? If you have 4000 open & uncommitted transactions, yeah, I'd be having screaming fits right about now. That's locks being held open, possibly leading to all sorts of issues. If it's connections, then that number is somewhat high, but not that troubleing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 25, 2012 at 5:27 am
Only running processes use CPU. Processes that are sleeping are doing nothing, processes that are suspended are waiting for resources and neither will use any CPU.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply