October 5, 2012 at 8:36 pm
Team - Friday night is turning bad for me...
One of my SPs was taking forever and after a long haul I have nailed it down to one table "TransactionLog" in my db.
Even the statement below is taking forever:
select COUNT(*) from TransactionLog
Just not ending... Can anyone tell what could be the reason??
I ran a dbcc check:
DBCC CHECKTABLE ("dbo.TransactionLog")
The output was:
DBCC results for 'TransactionLog'.
There are 67632 rows in 531 pages for object "TransactionLog".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
October 5, 2012 at 8:44 pm
Have you checked for any blocking?
October 5, 2012 at 10:50 pm
Try running the statement below. If it runs much faster, your original query is probably being blocked.
select COUNT(*) from TransactionLog with (nolock)
October 6, 2012 at 12:51 am
October 6, 2012 at 1:44 am
vick.ram79 (10/6/2012)
yup - the nolock is soo much faster...
I am not suggesting that you use NOLOCK in your stored procedure.
You should investigate why your query is being blocked. You may have a long running or uncommited transaction that is causing a problem.
October 6, 2012 at 3:05 am
Chapter 6: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ (Blocking)
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
October 6, 2012 at 11:10 pm
October 7, 2012 at 3:16 am
For the nolock, see - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply