Re: How does CREATE INDEX impact current users?

From: Paul S Randal [MS] (prandal_at_online.microsoft.com)
Date: 05/15/04 

Date: Sat, 15 May 2004 09:56:22 -0700

Here's the correct answer for you (Michael please take note).

Creating a non-clustered index will take an S lock on the table, thus
preventing all modifications to the table. Creating a clustered index will
take an X lock on the table, thus preventing any access to the table at all.
This problem is alleviated in SQL Server 2005 with the concept of online
index builds and rebuilds, where complete concurrent access to the data is
available during the index operation.

Creating a clustered index does not copy the table per se - the heap
contents are fed into a sort and the output of the sort is used to construct
the b-tree for the clustered index. Once complete the original heap is
discarded. In SQL Server 2000, you need approx 1.2x the original heap size
of free space to create the clustered index. The amount of transaction log
created should also be taken into account, and depends on your logging mode.

Please let me know if you have any additional questions.

Thanks and regards. 

-- 
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
""Michael Cheng [MSFT]"" <v-mingqc@online.microsoft.com> wrote in message
news:lsrrgRlOEHA.3972@cpmsftngxa10.phx.gbl...
> Hi Danny,
>
> Thanks for Alex's reply
>
> From your descriptions, I understood that you would like to make CREATE
> INDEX and you are afraid of its possible impact on logged users. Have I
> understood you? If there is anything I misunderstood, please feel free to
> let me know :)
>
> Based on my knowledge, it will not lock your table when creating index on
> it. However, when you create a clustered index, the table is copied, the
> data in the table is sorted, and then the original table is deleted.
> Therefore, enough empty space must exist in the database to hold a copy of
> the data. And creating a unique index ensures that any attempt to
duplicate
> key values fails.
>
> As Alex suggested, it is strongly recommand to create your index at
> midnight, which will do less impact on your production server.  The
> following documents will be helpful to you, which tell you how to build
> index with less defragmentations and better performance:)
>
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>
> SQL Server Database Index Performance Checklist
> http://www.sql-server-performance.com/sql_server_performance_audit7.asp
>
> SQL Server - Indexes and Performance
> http://www.databasejournal.com/features/mssql/article.php/1467801
>
> Moreover, you could find how an index works from Books Online or MSND
Online
>
> tempdb and Index Creation
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_
> ar_da2_5nou.asp
>
> Creating an Index
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm
> _8_des_05_8185.asp
>
> Hope this helps and if you have any questions or concerns, don't hesitate
> to let me know. We are here to be of assistance!
>
> Sincerely yours,
>
> Michael Cheng
> Microsoft Online Support
> ***********************************************************
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks.
>
>

Relevant Pages

  • Re: Optimizing massive update to large table
    ... Direct FIBRE channel connection to a 3.5 TB Apple ... I don't think you have a clustered index on the ... Consider adding a table lock hint to the update to keep SQL Server from ...
    (microsoft.public.sqlserver.server)
  • Re: Bulk Insert Data in Millions - Lock Issue
    ... >> 'cannot obtain lock resources' problem, ... > a clustered index in the table. ... > then load it to the target table with BCP or BULK INSERT. ... > Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Checkpoint causes need for better IO subsystem?
    ... maybe it's not the IO and that maybe it's an indexing performance issue. ... Microsoft SQL Server MVP ... is this a monotonically increasing indexing scheme that I've created? ... the Clustered index is on multiple cols... ...
    (microsoft.public.sqlserver.tools)
  • Re: Indexes
    ... because we have a clustered index but I was expecting ... Only one row fit per page, so SQL Server ... You have 100000 rows in the table and 1000 rows fit per index page. ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Checkpoint causes need for better IO subsystem?
    ... excellent articles on clustered index selection and its impact on ... clustered index sql kimberly tripp ... Microsoft SQL Server MVP ... the random write capability of the drives comes into play, ...
    (microsoft.public.sqlserver.tools)