?
Solved

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

Posted on 2015-01-29
3
Medium Priority
?
70 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 51

Assisted Solution

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

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 664 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 668 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

801 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