August 3, 2012 at 9:57 am
I have a dba question regarding a small small table.
Size is .008mb, 6 columns, 5 records total.
Select top 5 *
from table
Result is instantanious.
Select top 6 * Takes a very long time, I killed it before it came back.
Select *, takes a long time, killed it.
Select *
into Table_BU
From Table
Takes for ever... killed it too...
I ran the query:
SELECT * FROM sysprocesses WHERE open_tran = 1
But I don't know what I"m looking at... Reads Greek to me (I'm not a dba). Are there any red flags I should look for? for example, Everything shows up usually has CPU=0, or some very small number. The record associated with the ID I'm questioning says CPU=131247...
any help on this is appreciated.
Thanks
Crusty.
August 3, 2012 at 10:23 am
if you do sp_who, does your select query show as being blocked? Sounds like there is some uncommitted update still open on that table.
August 3, 2012 at 10:27 am
David,
I used
SELECT * FROM sysprocesses WHERE open_tran = 1
I saw CPU with 131579, or something like that. Big number. The User ID was for the external customer, and the db matched as well. Everything lined up. I killed the SPID associated with that record and now everything is fine.
Turns out, there was an insert that looks like it hung. That would explain why the SQL would hang when the other user would run:
select top 6 * vs. Select top 5.
Originally, there was only 5 records, the 6 wasn't there before.... Hmmm.. .Maybe I need to learn how to be a dba...
Crusty.
August 3, 2012 at 2:12 pm
Further if you suspect blocking you can run the following code to either confirm or rule out blocking:
select * from sysprocesses where blocked <> 0
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply