Will adding a nonclustered index lock my table?

Question

I have a table with ~2 million records in and I need to add a new nonclustered index to a uniqueidentifier to improve query performance.

Will adding a nonclustered index lock the table or otherwise degrade performance significantly while it's being applied?

There's lots of information out there about the benefits/pitfalls of indexing, but I can't find anything that tells me that happens during an indexing operation

I'm running SQL Server 2008 R2 (on Windows Server 2008 if that's important)

EDIT: It's the Enterprise Edition

I thought adding indexes to uniqueidentifier/guid fields weren't utilized?...I guess I answered my own question: http://stackoverflow.com/questions/13803326/use-of-non-clustered-index-on-guid-type-column-in-sql-server — ganders, Feb 28 '14 at 14:00

score 29 · Answer 1 · answered Dec 20 '16 at 22:39

For those of us not running "Expensive Edition" (Enterprise), the answer is thus:

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.

So basically it renders the target table "read only" while the index is built. This may or may not be a problem for your overlaying applications -- check with your dev teams and users!

PS: The question of whether or not, or why, to apply such an index, is an entirely different conversation. The SQL community and its legion of professional bloggers & SMEs are your friends.

Ben Thul · Accepted Answer · 2014-02-28T17:10:23.847

In Enterprise Edition, you gain the ability to do online index operations. It looks something like this:

create index MyIDX on MyTable (MyColumn) with (online = on)

Note that the operation does still take some locks during the process (at the beginning and end, IIRC), but doesn't lock the table for the duration of the index creation. If you're concerned, fire up an extended events session in a non-production environment and trace what locks are created and how long they exist for while creating your index.

Update: The documentation has a pretty good exposition about what locks are held when for both online and offline operations.

score 0 · Answer 3 · answered Feb 28 '14 at 12:02

Depends (on a lot of things) as a rule of thumb adding the index ill improve selects and degrade inserts. A uniqueidentifier is basically a size fixed random phrase and thanks to it that indexes ill can get fragmented fast.

For inserts it ill get a exclusive lock at row level (that's ok since that row is being "build") and soft locks at page level or table level (i'm not 100% sure about it, try the documentation but you got the idea).

That soft locks are not a issue depending on your isolation level (and hints applied to the selects). It can be a issue if you use a very restritive isolation level (that locks ill be honored to the letter)

Anyway I suggest you to hit the books and do some acid tests. Its a very complex topic and depends a lot on your particular scenario.

Will adding a nonclustered index lock my table?

3 Answers3