SQL Server Performance

CREATE INDEX on huge table

Discussion in 'SQL Server 2008 General DBA Questions' started by anilksharma99, Jun 19, 2010.

  1. anilksharma99 New Member

    Hello Experts, we are trying to create a non-clustered index on a 15billion row DW fact table (monthly partitioned but all data lives in two partitions). The index key is a single bigint column with 2 other columns included (1 is bigint and another is int). We are using SORT_IN_TEMPDB=ON, ONLINE=OFF and DATA_COMPRESSION=PAGE while creating this index and estimates the index size to be around 150GB. We tried to create this index and it didn't finish in 24hours and we needed to rollback since we couldn't go beyond the maintenance window further. We have two questions at this moment, first, is there any way to estimate how much time create index could take and second, what are the ways to create it faster. We can afford to bring the system down and use all resources but want to create it as fast as possible (Windows 2008+SQL2008, 24CPU, 256GB RAM)
    Thanks in advance for reading the post and any suggestion/pointer from your experience.
    - Anil
  2. satya Moderator

    What kind of clustered indexes keys you have?
    Are these clustered & newly created non-clustered index has any keys in common?
    Do you have any LOB data on that table?>
    In general it shouldn't take long to complete, again it depends on other processes that are running on the SQL instance.
    By default SQL engine puts up schema modification lock (SCH-M) on that table that prevents all the access (SELECT, UPDATE, DELETE) to the data until it finishes the CREATE INDEX operations. This is bit different to the action when you try to create NON CLUSTERED index which puts up shared lock (S) that will also prevent any data modifications only but still it is available for data readability.

    There are few guidelines (generic) that talks about n
    onunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns.

    Also the tempdb & disk space plays important role in index creation and
    disk space requirements are the same for online and offline index operations. An exception is additional disk space required by the temporary mapping index. This temporary index is used in online index operations that create, rebuild, or drop a clustered index. Dropping a clustered index online requires as much space as creating a clustered index online so you better s

    On multiprocessor computers that are running SQL Server Enterprise, index statements may use more processors to perform the scan and sort operations associated with the index statement just like other queries do. You can use the MAXDOP index option to control the number of processors dedicated to the online index operation. In this way, you can balance the resources that are used by index operation with those of the concurrent users. For more information, see Configuring Parallel Index Operations.

Share This Page