11

We have a table with a lot of 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+.

|improve this question
  • 3
    You can't create an index and place zero locks on the underlying table. You can get pretty close with ONLINE = ON (only certain metadata locks will be taken at the beginning and end), but this requires Enterprise Edition. – Aaron Bertrand♦ Sep 17 '15 at 18:04
  • 1
    You said can't be locked not even for a second this is not possible always. Even online index rebuild takes 2 sort term locks. So if you dont want any locks disturbing you, OFFLINE index rebuild is only option. – Shanky Sep 17 '15 at 18:08
  • @Shanky The OP is not rebuilding, they need to create from scratch. – Aaron Bertrand♦ Sep 17 '15 at 18:12
  • @AaronBertrand Aaron thanks, yes we have enterprise edition. I will try to simulate this in my test environment and see what I got. – Racer SQL Sep 17 '15 at 18:12
12

There is no such option as 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.

|improve this answer
  • Aaron thank you. I will accept it as an answer because it helped me a lot, we lost some data in the process ( testing in my test environtment ). But without it, we lost a lot more data. – Racer SQL Sep 17 '15 at 18:36
  • 1
    @Rafael I suspect your application has very low timeouts then, because these locks should not really interfere that much. You could consider adding retry logic or increasing the timeout values. – Aaron Bertrand♦ Sep 17 '15 at 18:55
  • we can't use a bigger timeout time. It's something like this. we have a @aaron Third-party software making a LOT of calls per second. what out SQL does is to select and update really fast to get every data of that Third-party software. if we lost 1 second of data, it will be like 15+ calls lost that wasnt updated in our databases. I'm not the developer so, this is what he said to me, and a little bit what I understand. – Racer SQL Sep 17 '15 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.