Post reply

VERY small Table, VERY poor performance

  • August 3, 2012 at 9:57 am

    #261990

    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

    #1521512

    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.

    And then again, I might be wrong ...
    David Webb

  • August 3, 2012 at 10:27 am

    #1521515

    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

    #1521630

    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