super slow query


  • this is a weird one

    I have a simple query like

    select studentnum from matbrdoff


    update matbrdoff.dbo.students

    set active = 'Y', grade = '9', schoolcode = 'BMH', homeroom = '9/10', dept = '107371691'

    where studentnum = 60795 and schoolcode = 'BMH'

    and they take 4hr + so i started off shrinking log file since i had an error 'more that 1000 VLF file' that di no good so then I adjusted

    DBCC loginfo('matbrdoff')
    ALTER DATABASE matbrdoff
    modify FILE (name=matbrdoff_log
    , size=4096 mb
    , filegrowth=1024 mb)
    DBCC loginfo('matbrdoff')

    no luck profiler gives me this its saying it cant find the db? and these objects in the  tuning log ...any ideas


    Monday, November 04, 2013 10:41 PM

All replies

  • Hi,

    When you are executing the update statement, please check if there are any blockings. You can use the below query to check blockings.

    select * from sys.dm_exec_requests where blocking_session_id > 0

    You can also use sp_who and check the blk column to see if there are any blockings.

    Create an Index on the students table with studentnum and schoolcode as the key columns.

    Thanks and Regards Alankar Chakravorty

    Monday, November 04, 2013 10:58 PM
  • What about this query ? How many rows do it returns ? and how much time does it take?

    select count(*) from matbrdoff.dbo.students 
    where studentnum = 60795 and schoolcode = 'BMH'

    Tuesday, November 05, 2013 12:07 AM
  • hi

    select count(*) from matbrdoff.dbo.students
    where studentnum = 60795 and schoolcode = 'BMH'

    is less that a microsecond and it returns 1 as expected ,

    hmmm seems to be working now

    Wednesday, November 06, 2013 3:57 PM
  • cool i will give that a try i mad a nonclustered index using just the student number I will try adding schoolcode
    Wednesday, November 06, 2013 3:58 PM
  • hey

    I get the beolw response so does that mean these 2 are blocking?

    session_id request_id start_time status command sql_handle statement_start_offset statement_end_offset plan_handle database_id user_id connection_id
    58 0 2013-11-06 11:16:21.360 suspended UPDATE 0x020000004F1ED334DD92D78A0712BD23CA2CC94461B75AC4 206 572 0x060001004F1ED3344081BB4E050000000000000000000000 1 1 E7815323-CFBE-4F25-B231-6C978CF05A88
    59 0 2013-11-06 11:03:38.080 suspended UPDATE 0x02000000397DAE0BC347FBFF2CBEFD376901EA7F525D448C 0 -1 0x0600D601397DAE0B4001A44E050000000000000000000000 470 7 C152F41C-0E9D-4DEB-90EA-DB9DE157FFCD

    Wednesday, November 06, 2013 5:25 PM