count(*) taking forever... emergency

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » count(*) taking forever... emergency

count(*) taking forever... emergency

Rate Topic

Display Mode

Topic Options

Author

Message

vick.ram79

Posted Friday, October 05, 2012 8:36 PM

Valued Member

Group: General Forum Members
Last Login: Monday, January 21, 2013 11:15 AM
Points: 73, Visits: 397

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

Lynn Pettis

Posted Friday, October 05, 2012 8:44 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 6:49 AM
Points: 21,591, Visits: 27,397

Have you checked for any blocking?

Cool

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

Michael Valentine Jones

Posted Friday, October 05, 2012 10:50 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 2,941, Visits: 10,484

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

vick.ram79

Posted Saturday, October 06, 2012 12:51 AM

Valued Member

Group: General Forum Members
Last Login: Monday, January 21, 2013 11:15 AM
Points: 73, Visits: 397

yup - the nolock is soo much faster...

How To Post

Post #1369389

Michael Valentine Jones

Posted Saturday, October 06, 2012 1:44 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 2,941, Visits: 10,484

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

GilaMonster

Posted Saturday, October 06, 2012 3:05 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 37,660, Visits: 29,913

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

vick.ram79

Posted Saturday, October 06, 2012 11:10 PM

Valued Member

Group: General Forum Members
Last Login: Monday, January 21, 2013 11:15 AM
Points: 73, Visits: 397

Thanks guys. I will read up on that.

How To Post

Post #1369496

GilaMonster

Posted Sunday, October 07, 2012 3:16 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 37,660, Visits: 29,913

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 »

Permissions