locked
SELECT in a table takes FOREVER RRS feed

  • Question

  • SQL Server 2000, QA Database: A table called Telephone_Directory with just 4.000 records.

    SELECT * FROM Telephone_Directory is taking forever.

    If I stop the select after 1 second I see 162 rows.

    If I stop the select after 1 minute I see again 162 rows.

    Why this could be happening?

    The same querie on Production Database is taking 6 seconds to retrieve the 4.000 records.

     

     

     

    Thursday, May 25, 2006 5:08 PM

Answers

  • I had this issue once, hey I still remember the SPID, it was number 71

    KILL 71

    KILL 71

    KILL 71

    KILL 71

     

    Nothing happenend, it was immortal ;-(

    this was caused by access and if I ran select * It would get back 144 rows  (after I hit cancel query)but If I included the nolock hint I would get the whole table in milliseconds

    My only resort was failing over the cluster to the 2nd node restarting node one and failing back to node one again (downtime of about 30 seconds, not too bad since we did it after hours)

    just for fun try this

    select * from tablename with (nolock) and see what happens

     

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/

    Thursday, May 25, 2006 7:53 PM

All replies

  • Thank you for your answer. I thought the same.

    This table has 11 indexes, one clustered.

    I rebuild some of them.

    But specially with one is taking hours, when like you said should not be taking so long.

    I also did DEFRAG of each index.

    I am afraid is something related with that index.

    Why do you think rebuild that index is taking a lot of time?

    Thank you

     

     

    Thursday, May 25, 2006 5:30 PM
  • maybe something is wrong, run this

    DBCC CHECKTABLE ('Telephone_Directory')

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/

    Thursday, May 25, 2006 6:19 PM
  • Also try

    UPDATE STATISTICS Telephone_Directory

     

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/


     

    Thursday, May 25, 2006 6:21 PM
  • Another possibility is blocking

    run sp_who2 look at the BlkBy column, grab the SPID from that row

    run DBCC INPUTBUFFER(SPID)

    Is this statement going against the Telephone_Directory table? try killing it by executing

    KILL SPID

     

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/

    Thursday, May 25, 2006 6:33 PM
  • Thank you for your ideas, I will try it and let you know.
    Thursday, May 25, 2006 6:48 PM
  • This a QA Database, I check the sp_who2 command output and the BlkBy column is empty for all the rows in that Database.

    Is funny because if you run SELECTS using index and looking for some records like EmployeeId, DepartmentCode, etc...is pulling the information.

    But if you make the SELECT * takes forever without a reason and is always showing the first 162 rows.

    Or if you make a SELECT wit a DepartmentCode that is not in the table, also is taking forever even is using the correct index.

    But that DepartmentCode index is taking hours to be rebuild.

    I never see it before.

     

     

    Thursday, May 25, 2006 7:39 PM
  • I had this issue once, hey I still remember the SPID, it was number 71

    KILL 71

    KILL 71

    KILL 71

    KILL 71

     

    Nothing happenend, it was immortal ;-(

    this was caused by access and if I ran select * It would get back 144 rows  (after I hit cancel query)but If I included the nolock hint I would get the whole table in milliseconds

    My only resort was failing over the cluster to the 2nd node restarting node one and failing back to node one again (downtime of about 30 seconds, not too bad since we did it after hours)

    just for fun try this

    select * from tablename with (nolock) and see what happens

     

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/

    Thursday, May 25, 2006 7:53 PM
  • YOU GOT IT !!!

    We are working also against Access on the Front End and we have a Cluster.

    I checked with (nolock) and it worked like is supposed to work.

    But now I am curious about why.....

    Do you think someone is holding a lock on the table or page?

    Remember that this a QA Database and no many people is working....

    Some Access process maybe still is locking that table?

     

     

    Thursday, May 25, 2006 8:12 PM
  • It's bizarre that you don't see the lock when running sp_who2

    That's why everything should be in stored procedures and not in Grids and other VBA stuff since you essentially lose control and have to look all over the place to see what is going on

    with (nolock) can cause dirty reads (as well as repeatable and phantom reads) and should not be used especially if you use the select to do an update because it might produce weird result and it will be very very hard to find out later on why the values are all messed up

     

    Denis the SQL Menace

    http://sqlservercode.blogspot.com/

     

    Thursday, May 25, 2006 8:21 PM