Post reply

Index creation taking long

  • November 22, 2015 at 9:19 pm

    #399367

    In a test database server ,database changed to single user mode while creating non clustered index on primary key taking long time to complete. the table is a large one.

  • November 22, 2015 at 9:49 pm

    #1841582

    That is not unusual. The different between two polls of sys.dm_os_wait_stats is likely to show SQL Server is waiting upon significant amount of wait_time_ms for PAGEIOLATCH% wait_types. Perfmon.msc's Physical Disk counters are likely to show Disk Bytes/Sec, Avg. Disk Sec/Transfer, Avg. Disk Queue Lnght are higher than normal for your system. Seeing an Avg. Disk Sec/Transfer > 0.020 seconds indicates that your disk IO throughput is inadequate for the indexing's needs. If you do not throttle max degree of parallelism (or throttle the CREATE INDEX statement), it is possible for a busy system to not have sufficient worker threads (and it is possible for sys.dm_exec_requests to show hundreds of worker threads being consumed by the spid running CREATE INDEX). If your Database Property's database files or log files have an excessively high growth rate (in % or in MB), the index will wait for the growth to complete.

    If you cannot determine how long an index creation "should" take for your system, it is not clear whether it is worth addressing any of the above concerns now (because you will have to start over again). Longer term, it is still worth considering all of the above.

  • November 22, 2015 at 10:15 pm

    #1841584

    There's nothing special causing it to be slow outside of the ordinary considerations. Clearly the number of rows in the database is going to be a primary factor, then the processor speed and amount of memory on your computer.

  • November 22, 2015 at 10:35 pm

    #1841586

    Thanks John 🙂

  • November 22, 2015 at 10:51 pm

    #1841589

    :Wow:

  • November 29, 2015 at 1:36 pm

    #1842762

    rameelster (11/22/2015)

    In a test database server ,database changed to single user mode while creating non clustered index on primary key taking long time to complete. the table is a large one.

    Define "large one" and type and number of column(s) your PK consists of. Then, tell us what a "long time" is.

    The reason why I'm asking all these "silly" questions is because if you have a 1 million row table and it's taking 14 hours (as an example), then you have some other serious considerations to discover.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply