how to troubleshoot when select count(*) spins for a long time on a small table...
Posted on 2015-01-29
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.