Solved

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

Posted on 2015-01-29
3
64 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 50

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 66

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

688 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