Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

count(*) taking forever... emergency Expand / Collapse
Author
Message
Posted Friday, October 5, 2012 8:36 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 1, 2014 9:49 PM
Points: 75, Visits: 409
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.



How To Post
Post #1369366
Posted Friday, October 5, 2012 8:44 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:35 PM
Points: 22,992, Visits: 31,471
Have you checked for any blocking?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1369367
Posted Friday, October 5, 2012 10:50 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:31 PM
Points: 3,116, Visits: 11,388
Try running the statement below. If it runs much faster, your original query is probably being blocked.


select COUNT(*) from TransactionLog with (nolock)


Post #1369384
Posted Saturday, October 6, 2012 12:51 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 1, 2014 9:49 PM
Points: 75, Visits: 409
yup - the nolock is soo much faster...

How To Post
Post #1369389
Posted Saturday, October 6, 2012 1:44 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:31 PM
Points: 3,116, Visits: 11,388
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.



Post #1369396
Posted Saturday, October 6, 2012 3:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1369399
Posted Saturday, October 6, 2012 11:10 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 1, 2014 9:49 PM
Points: 75, Visits: 409
Thanks guys. I will read up on that.

How To Post
Post #1369496
Posted Sunday, October 7, 2012 3:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1369511
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse