| Author |
Topic |
|
lebedev
Posting Yak Master
126 Posts |
 Posted - 2009-10-01 : 12:57:30
|
| We have a web application which uses a pool of 8 database connections. After running the app for some time we get into a state where 7 out of 8 connections are in "Suspended" state and are blocked directly or indirectly by the 8th connection which is in "Runnable" state. The application remains in this state indefinitely until we kill the culprit connection.The strange thing is that the culprit connection is not holding any locks and "dbcc opentran" tells us that there are no active transactions.How is it possible that without any active transactions in the system all connections are suspended on a "Runnable" connection which holds no locks?Thanks,Alec |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2009-10-01 : 17:47:05
|
| Yes, I am sure. I was able to reproduce this scenario as follows:1. Open query window in SQL Server Management studio and run the following:begin tranupdate table x set col1 = col12. Open another query window in SQL Server Management studio and run the following:begin tranupdate table x set col1 = col1The command in the second window blocks, but dbcc opentran indicates that there are no active transactions. The first connection will appear as running and the second one as suspended.I am surprised that dbcc opentran does not display the transaction in the second window. Could you explain?Thanks.Alec |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2009-10-02 : 17:51:44
|
| I am using SQL Server 2008 build 10.0.2531.0 and was able once again to confirm what I was saying in the previous post. Just run the following batch and you will see that both "dbcc opentran" statements display "No active open transactions." even though the transaction was never committed or rolled back.use your_dbgobegin trandbcc opentran ('your_db')update your_table set name = namedbcc opentran ('your_db') |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lebedev
Posting Yak Master
126 Posts |
Posted - 2009-10-04 : 19:10:53
|
This is not what I am observing. I am using SQL Server 2008 and tested this with databases in FULL and SIMPLE recovery models.I have three query windows open. I ran the script from my previous post in one window and then another window. While the script in the second window is blocking on the first window I run dbcc opentran('your_db') in the third window and get "No active open transactions."Any ideas on why this is happening? Can anybody else confirm this behavior?Thanks,AlecHere is my script for AdventureWorks database:use AdventureWorksgobegin trandbcc opentran ('AdventureWorks')update HumanResources.Department set Name = Namedbcc opentran ('AdventureWorks')-- rollback tran |
|
|
|
opc.three
Starting Member
5 Posts |
Posted - 2010-08-02 : 08:30:01
|
Try this:use AdventureWorksgobegin trandbcc opentran ('AdventureWorks')update HumanResources.Department set Name = Name + 'x' -- must change the value otherwise nothing to log so no info will be returned by dbcc opentrandbcc opentran ('AdventureWorks')-- rollback tranMake everything as simple as possible, but not simpler. -- Einstein |
|
|
|
karolanet
Starting Member
1 Post |
Posted - 2011-02-10 : 15:58:44
|
quote:
Originally posted by lebedev
We have a web application which uses a pool of 8 database connections. After running the app for some time we get into a state where 7 out of 8 connections are in "Suspended" state and are blocked directly or indirectly by the 8th connection which is in "Runnable" state. The application remains in this state indefinitely until we kill the culprit connection.The strange thing is that the culprit connection is not holding any locks and "dbcc opentran" tells us that there are no active transactions.How is it possible that without any active transactions in the system all connections are suspended on a "Runnable" connection which holds no locks?Thanks,Alec
Hi !!! I suffered a lot with the same problem as you !!When I put a new version of my program, the opening of the main grid was so slow that users have to wait from 40 minutes to 3 hours depending on the network speed. The problem of my version was that I increased the characters of a description field of a table implied on that query. That field was a varchar(100) and then, I changed it for a varchar(500). The result was 400 bytes per row of extra traffic for the network. I had to bring 2,3000 row approximately. Thinking about this, I was able to reduce the amount of characters returned on my query of other fields too. So, finaly, I could reduce 500 characters per row. So, 500*23000 = 11,500,000 bytes. This is 11.5M. Looking closer to my query, I realized that I could improve some lines of the code ... and I rebuild indexes (with ALTER INDEX) ... and finaly, users could open the query in 5 or 10 minutes !! Great improve no?My simphtoms where the same you've described. State: RUNNABLE. And I don't remember but somewhere I red: ASYNCH_IO_COMPLETION or something like this.I've red that it is a problem of the programmer. The problem has to be on the query.For me it was true.For more details, this is a good link to view:(I've found it searchig this on google: states sql server analisys services network problem runnable asynk):[url]http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc[/url]I hope it could be helpful!!Bye bye !! |
|
|
|
|
Suspended connections