1

When I create more than 5 to 6 non-clustered indexes on a single table in SQL Server it will cause a locking problem.

Is this right or wrong? And if your answer is "right" and my actual requirement is more than 5-6 non-clustered indexes then what shall I do?

4

It's hard to give you a complete answer without knowing what you mean by "locking problem". All queries take locks of one type or another and this is normal and correct. I'm going to assume you mean that queries are getting blocked? Where you are running one query and another will have to wait for resources?

I can tell you the following:

  1. While you create the indexes you might (depending on how you create it) see some blocking.
  2. Every index you add creates overhead for write operations. This means that your writes will take longer and could increase the amount of blocking that you see.

In and of themselves no, creating 5-6 indexes shouldn't be causing you a noticeable problem. Now if you had 50-60 indexes we might expect one. If you are seeing a problem you will have to give us quite a bit more detail (table structures, indexes, queries that are having a problem, and most importantly what the actual problem is) before we can give you a more specific answer.

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.