none
Index creation is taking more time RRS feed

  • Question

  • Hi All,

    We are trying to creating a clustered idx on a HEAP. It is almost 45 mins, its still running. Heap is around ~400 GB. Is it expected ? anyway to speed up the creation of clustered idx?

    SQL Server 2012 SP4 EE.

    Thanks,

    Sam

    Saturday, December 7, 2019 10:07 AM

Answers

All replies

  • Have you checked for blocking?

    I will have to admit that I would expect that operation to complete in 45 minutes - but maybe not if I would try it on my laptop, if you get the drift. That is, the more able the server is, the shorter time it will take. Also, the key definition matters. If the key column(s) are only integers, it will be faster than if you have character data.

    And of course if there is some blocking, the process will not make any progress at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, December 7, 2019 6:29 PM
  • check to see if your DBA made the helpful setting of Max Degree of Parallelism = 1

    if so, do the CREATE CLUSTERED INDEX … WITH(MAXDOP=xx, SORT_IN_TEMP_DB=ON)

    check to see how much free space you have in the filegroup, and if your log file was growing during the rebuild

    you might consider partitioning this table, it won't help you now, but will in future rebuilds

    also, is this with or without compression?


    jchang


    • Edited by jchang61 Saturday, December 7, 2019 6:56 PM
    Saturday, December 7, 2019 6:55 PM
  • Do the users continue working against that table?

    I see the table does not have NCI indexes am I right?

    A few days ago  I created CI on the 300 GB table with no activities and it completed in 40 minutes.

    4 CPU +64 GB memory


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Sunday, December 8, 2019 5:40 AM
    Answerer