Solved

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

Posted on 2015-01-29
3
57 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 45

Assisted Solution

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

Assisted Solution

by:ScottPletcher
ScottPletcher earned 166 total points
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

12 Experts available now in Live!

Get 1:1 Help Now