Solved

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

Posted on 2015-01-29
3
62 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 48

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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 …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

820 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