Solved

how to troubleshoot when select count(*) spins for a long time on a small table...

Posted on 2015-01-29
3
59 Views
Last Modified: 2015-02-17
a table has 18k records. (can see from sysindexes). when i did a select COUNT(*) from tablename, it runs for 10 minutes or so and still ran.. so i had to cancel it..
                         when i did a select * from tablename, it shows records upto 16995, then it just spined and spined.. (again so many minutes run by before i canceled it).
                         
                         when i did a checkdb, the database returns 'fine with no errors'. i tried to drop the index (thinking I will add them again, which may help), but that also ran for minutes but nothing happened.
                         this was a small database. i checked similar databases on the same server, and they also responded very fast and fine.
                         
                         then i came this morning and tried the select, and it ran in less than a second..
                         
                         what do you think might have been happening. how would you troubleshoot this (if it happened again). this is a test environment.. so only 1 person (or 2 at max) would hit this database for now..
                         i also checked the maintenance plan.. every night rebuild/reindex happened on this database as well as other user databases without any single error or timeout.

this is a 2008 database with latest patches.
0
Comment
Question by:25112
3 Comments
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40577746
There is any index on that table?
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 166 total points
ID: 40577758
Something is blocking you.

If the table has a nonclustered index that is not filtered, you can do this:
SELECT COUNT(first_column_in_index)
FROM tablename
and maybe avoid the blocking.

You can also add a nolock hint:
FROM tablename WITH (NOLOCK)
but that risks giving you an incorrect total.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 167 total points
ID: 40577895
Don't have a direct answer for you, and don't know if this will help but you can try to execute the DMV to get rowcounts
SELECT t.name as table_name, st.row_count
FROM sys.dm_db_partition_stats st
	JOIN sys.tables t ON st.object_id = t.object_id
ORDER BY t.name

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now