quote:

---

No blocks on the server.

---

quote:

---

Originally posted by tkizer

quote:

---

No blocks on the server.

---

How did you determine this? This very much sounds like a blocking or a ton of locks issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

---

	SELECT 
		GETDATE() AS block_date, 
		es.session_id AS request_session_id, 
		SUBSTRING(qt.text,qr.statement_start_offset/2 + 1,
			(CASE WHEN qr.statement_end_offset = -1
						THEN len(convert(nvarchar(max), qt.text)) * 2
						ELSE qr.statement_end_offset END -qr.statement_start_offset)/2
		) AS request_query_text,
		(	SELECT query_plan 
			FROM sys.dm_exec_requests er
			CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) 
			WHERE er.session_id = es.session_id
		) AS request_query_plan,
		qr.blocking_session_id,
		(	SELECT text 
			FROM sys.dm_exec_connections AS p
			CROSS APPLY sys.dm_exec_sql_text(p.most_recent_sql_handle) 
			WHERE p.session_id = qr.blocking_session_id
		) AS blocking_query_text,
		(	SELECT query_plan 
			FROM sys.dm_exec_requests er
			CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) 
			WHERE er.session_id = qr.blocking_session_id
		) AS blocking_query_plan,
		DB_NAME(qr.database_id) AS database_name, 
		es.last_request_start_time, 
		es.last_request_end_time, 
		qr.wait_type, 
		qr.wait_time, 
		qr.wait_resource,
		qr.open_transaction_count, 
		qr.transaction_id, 
		qtran.transaction_begin_time,
		CASE qtran.transaction_type
			WHEN 1 THEN 'Read/Write'
			WHEN 2 THEN 'Read-only'
			WHEN 3 THEN 'System'
			WHEN 4 THEN 'Distributed'
		END AS transaction_type,
		qr.cpu_time, 
		qr.total_elapsed_time, 
		qr.reads, 
		qr.writes, 
		qr.logical_reads
	FROM sys.dm_exec_sessions es
	LEFT OUTER JOIN sys.dm_exec_requests qr ON es.session_id = qr.session_id
	CROSS APPLY sys.dm_exec_sql_text(qr.sql_handle) AS qt
	LEFT OUTER JOIN sys.dm_tran_active_transactions qtran ON qr.transaction_id = qtran.transaction_id
	WHERE es.is_user_process = 1