locked
index between SQL 2000 and 2008 RRS feed

  • Question

  • I migrate one database from SQL 2000 to SQL 2008.

    I got one table has update issue.

    The update SQL works sometimes, but sometimes just get time out.

    It works with SQL  2000 for years.

    The update SQL works a short peiord time after rebuild index for SQL 2008.

    I just wonder any index different between SQL 2000 and SQL 2008.

    This table has 6 columns composite as primary key and there are some field type are smalldattime, and datetime type.

    I open a trace to look the issue, there is no error when update hang there and SSMS just shows executing SQL forever.

    Trace shows exactly SQL running when update SQL works,

    Are there any place I need look in to?

    Ay suggestions or information will be great appreciated,

    Regards,

    Souris,

    Thursday, April 18, 2013 3:46 PM

Answers

  • Thanks for helping and information,

    The issue has been fixed. It is lock issue,

    Thanks again,

    Regards,

    Souris,

    • Marked as answer by sourises Friday, April 19, 2013 6:42 PM
    Friday, April 19, 2013 6:42 PM

All replies

  • Hello,

    Could you please share the result of the following query with us?

    SELECT TOP 40 *

    FROM sys.dm_os_wait_stats

    ORDER BY wait_time_ms DESC

    Thank you in advance.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, April 18, 2013 6:49 PM
  • Here are the result,

    Thanks for helping,

    Regards,

    Souris,

    wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
    DBMIRROR_DBM_MUTEX 5639543 7880375719 1630691 1174287
    LATCH_EX 262046673 3319362874 4708 67598648
    CXPACKET 130709421 3155647015 25209 47085402
    LAZYWRITER_SLEEP 1253071 1855360505 2929582 333192
    FT_IFTS_SCHEDULER_IDLE_WAIT 17064 1372059994 600031 2781
    BROKER_TASK_STOP 3606660 946093763 10168 722374
    DBMIRROR_EVENTS_QUEUE 2498961 928010666 1270 573706
    LCK_M_U 522444 506219497 12483 353518
    XE_TIMER_EVENT 15469 464024997 30119 464024050
    REQUEST_FOR_DEADLOCK_SEARCH 93924 463918781 5188 463918781
    LOGMGR_QUEUE 2452072 463834299 2863 743715
    XE_DISPATCHER_WAIT 1101 463814942 52981644 0
    DISPATCHER_QUEUE_SEMAPHORE 86861 463745732 3601726 17583
    CHECKPOINT_QUEUE 540 463026680 2987589 396
    SQLTRACE_BUFFER_FLUSH 115714 462927484 4163 28
    DBMIRROR_SEND 613782 462834158 1630684 130785
    FT_IFTSHC_MUTEX 5 307430687 177354083 0
    SLEEP_TASK 474717993 246638147 1205 5046142
    CMEMTHREAD 1507291433 236670205 773 170387195
    BROKER_TO_FLUSH 226494 232019185 1251 35588
    ASYNC_NETWORK_IO 428813 64004474 2121 80815
    SOS_SCHEDULER_YIELD 259154944 59765684 755 59273873
    CXROWSET_SYNC 4643244 54971937 834 2562654
    PREEMPTIVE_OS_PIPEOPS 1063 25771774 4433411 0
    LCK_M_SCH_M 87 11486197 10711495 14
    WRITELOG 2451681 9622786 4304 470017
    LCK_M_S 10937 5402554 60519 5891
    EXECSYNC 1994644 3653374 335 368106
    LCK_M_SCH_S 9 3095460 599985 0
    BACKUPIO 810332 2479737 62185 34244
    ASYNC_IO_COMPLETION 299831 2368381 99332 5350
    LCK_M_IS 155 2195812 60547 46
    LCK_M_IX 629 1846148 60521 186
    BACKUPBUFFER 913871 1572110 1285 76959
    OLEDB 40018428 1209509 5655 0
    PAGELATCH_UP 313114 1159606 182 35953
    SQLTRACE_LOCK 12398 740469 1000 4779
    PAGEIOLATCH_SH 36369 610028 3202 6474
    BACKUPTHREAD 12068 563125 62185 1530
    LCK_M_X 1556 529726 60518 975
    Friday, April 19, 2013 2:49 PM
  • Thanks for helping and information,

    The issue has been fixed. It is lock issue,

    Thanks again,

    Regards,

    Souris,

    • Marked as answer by sourises Friday, April 19, 2013 6:42 PM
    Friday, April 19, 2013 6:42 PM