|
I am experiencing a blocking issue in sql server 2005. An end user executes a query (e.g. select statements) and seems to get into a never-ending blocking until i kill the process e.g. for hours on end . the end user querys run just fine when I test the. It seems to get into a blocking issue when qanother process tries to truncate and reload a table. how can I troubleshoot this? |
||||
|
The easiest way I look at it is, right click on the database in SSMS and under Reports => Standard Reports => All Blocking Transactions. There are other standard reports with blocking transactions as well but I found all one pretty useful. The other thing you could do is setup a SQL Server Agent Alert for figuring our locks for your database. Type: SQL server performance condition alert Object: SQL Server:Locks Counter: Anything you want Instance: Database Rises above: {x} number of occurrences you want to look for. And have the alert set to email you when the condition is met (considering you have Database Mail setup already). |
|||
|
|
SELECT-ing from a table that has beenTRUNCATE-d by another still open transaction then yes it will be blocked. Are you saying the blocking continues even after that other transaction is completed? – Martin Smith Jan 4 at 12:43SELECTis itself blocked by something else (look insys.dm_os_waiting_tasks) or sometimes it gets a catastrophically bad plan that means it is actually doing much more work (e.g. parameter sniffing). You say "the end user querys run just fine when I test" so sounds like the second one. Compare and contrast the execution plans from both sessions. – Martin Smith Jan 4 at 12:57