Create clustered index on read committed snapshot db

Posted on 2011-05-03
Last Modified: 2012-05-11
If anyone had experience with read committed snapshot enabled, can I have some advised what would be the status of insert / select statement during the index creation ?
one of the table has no clustered index and we plan to create it during peak hours.
Question by:motioneye
    LVL 15

    Accepted Solution

    If you are creating a new index on a table with existing data, all existing rows will be indexed as part of the CREATE INDEX statement. If the table is large, the indexing process could take some time. The impact of this indexing process on other user sessions is based whether SQL server is using the Offline mode or Online mode.

    Be default, SQL Server performs indexing operations in Offline mode, where table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

    SQL Server Enterprise Edition supports indexing operations in Online mode, where other user sessions will not be impacted.

    LVL 57

    Assisted Solution

    by:Raja Jegan R
    >> one of the table has no clustered index and we plan to create it during peak hours.

    I have used Read Committed Snapshot Isolation set in my environment.
    Kindly let us know the mount of data currently available in your table: If it is less, then it won't take much time or impact. And Offline/ Online concepts mentioned above are applicable.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now