patternsqlModerate
Creating an index on a really busy table
Viewed 0 times
reallycreatingbusyindextable
Problem
We have a table with a lot of
What is the best way to create an index on a table that can't be locked not even for a second?
If I create the index, i'm sure it will lock the requests, and I can't do it.
It's a big table with 1 million rows+.
insert\select every time ( like 100 machines inserting\updating every second).What is the best way to create an index on a table that can't be locked not even for a second?
If I create the index, i'm sure it will lock the requests, and I can't do it.
It's a big table with 1 million rows+.
Solution
There is no such option as
The best you're going to do is
This will significantly reduce the impact of building the index on your applications, but there is no practical way to completely eliminate that impact. Also, this feature is not free: it requires Enterprise Edition.
CREATE INDEX WITH (NOLOCK) (and even NOLOCK on a query takes locks, just fewer than without the hint).The best you're going to do is
WITH (ONLINE = ON), which still takes locks at the beginning and end of the operation (both relating to recompiling plans related to the table - see this blog post by Paul Randal for more details). This will significantly reduce the impact of building the index on your applications, but there is no practical way to completely eliminate that impact. Also, this feature is not free: it requires Enterprise Edition.
Context
StackExchange Database Administrators Q#115381, answer score: 12
Revisions (0)
No revisions yet.