March 6, 2014 at 6:03 am
Hi All,
I have a strange problem. I just migrated datawarehouse db from windows server 2008 to 2012. In addition to that i also upgraded its ssms version to 2012. However, sessions does not release their keys even in sleeping mode (sys.dm_exec_request returns nothing with that particular session id however i see that session id blocks another one). Thus my reportserver db works incredibly slow. I need to fix it asap. Do you guys have any solution beside changing transaction isolation level
Thank you very much....
March 6, 2014 at 6:08 am
Check for open transactions. If those sessions are starting transactions and no committing, their locks will be held until thye commit, rollback or disconnect.
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
March 6, 2014 at 7:56 am
Since there is no open transaction it should've been committed.
March 6, 2014 at 1:16 pm
Did you have blockings?
select * from sys.sysprocesses
where blocked>0
March 6, 2014 at 1:50 pm
Locks are released either when the statement completes or when the transaction commits. The only way a sleeping connection can be holding a lock (other than the normal database lock) is by having an open transaction.
How are you checking what locks the sessions hold and how are you checking for open transactions?
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
March 6, 2014 at 2:01 pm
Author's ultimate goal are blockings. If nothing blocks his report, he does not need to care about held locks and open trans.
March 6, 2014 at 2:04 pm
SQL Guy 1 (3/6/2014)
If nothing blocks his report, he does not need to care about held locks and open trans.
To diagnose blocking you need to investigate locks and transactions, so I hope he does care about them.
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
March 7, 2014 at 12:57 am
This is the sql that how i query blocking sessions
SELECT blocking.session_id AS blocking_session_id ,
blocked.session_id AS blocked_session_id ,
waitstats.wait_type AS blocking_resource ,
waitstats.wait_duration_ms ,
waitstats.resource_description ,
blocked_cache.text AS blocked_text ,
blocking_cache.text AS blocking_text
FROM sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests blocked
ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)
blocked_cache
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle)
blocking_cache
INNER JOIN sys.dm_os_waiting_tasks waitstats
ON waitstats.session_id = blocked.session_id
for transactions i have several. but as you guess, i query from sys dm exec requests and sys.processes
March 7, 2014 at 1:37 am
You said earlier
Since there is no open transaction it should've been committed.
How did you identify that the session causing the blocking did not have an open transaction?
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
March 7, 2014 at 1:40 am
as far as i know if sys dm exec requests returns nothing there is no open transactionn. isn't it ?
March 7, 2014 at 1:45 am
Akayisi (3/7/2014)
as far as i know if sys dm exec requests returns nothing there is no open transactionn. isn't it ?
No.
Nothing from exec requests means there's no currently executing statement. A session can be sleeping (no currently executing statement) and still have an open transaction and still be holding locks. In fact it's the only way a sleeping session can be holding locks.
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
March 7, 2014 at 1:49 am
Then how im suppose to see the open transactions ? do you have any useful sql scripts that can help me out in order to find out what my problem is . Many thanks ...
March 7, 2014 at 2:06 am
Akayisi (3/7/2014)
Then how im suppose to see the open transactions ?
sys.dm_tran_active_transactions
sys.dm_tran_session_transactions
sys.dm_tran_locks
Lots of options. Good examples in Books Online, there's one on the page for sys.dm_exec_sessions, probably elsewhere too.
First confirm that the code is leaving a transaction open, then if it is, you'll need to work through the code and figure out why, make sure that any begin tran has a commit/rollback that cannot be avoided.
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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply