What are the best practices for creating the non-clustered index

Question

What are the best practices for creating the non-clustered index

deepak.a

SSCertifiable

Points: 5330
More actions
November 25, 2011 at 2:17 am

#390954

Hi All,
What are the best practices for creating the non-clustered index in production servers without having down time.
Is there any restriction ?
Is the create index will lock the table ?
Is the create index on live server cause dead lock , live lock or blocking ?
Thanks & Regards
Deepak

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Yes it will lock the table. For a nonclustered index, should be a shared lock, so just blocks writers. Unless you have Enterprise edition and can build the index online.

Do you know the difference between deadlock, live locks and blocking?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 2

You can use ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCK Options when you are creating the non clustered index.

when both of these options are on (Default) the Database Engine chooses the appropriate lock and can escalate the lock from a row or page lock to a table lock.

When both of these are OFF, then only a table-level lock is allowed when accessing the index.

You can check the below article for more information

http://msdn.microsoft.com/en-us/library/ms177442.aspx">

http://msdn.microsoft.com/en-us/library/ms177442.aspx

deepak.a SSCertifiable Points: 5330 More actions · Answer 3

GilaMonster (11/25/2011)
Yes it will lock the table. For a nonclustered index, should be a shared lock, so just blocks writers. Unless you have Enterprise edition and can

If we set the online option to ON then also it wil place shared lock ?

Regards -Deepak

deepak.a SSCertifiable Points: 5330 More actions · Answer 4

GilaMonster (11/25/2011)
Do you know the difference between deadlock, live locks and blocking?

Deadlock:- A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.

A Live lock is one, where a request for exclusive lock is denied continuously because a series of overlapping shared locks keeps on interfering each other and to adapt from each other they keep on changing the status which further prevents them to complete the task but in deadlock both the processes wait on each other.

blocking : when a SQL Server process is waiting for a resource that another process has yet to release