What are few ways to fix locking and blocking in SQL Server?

  1. Have enough indexes to make queries go fast but not so many that they make our queries go slow. That wont fix the 5000 rows problem. SQL server will escalate to table level lock if it needs to access/lock more than 5000 rows.
  2. Keep your transactions as short as possible. That helps but you have modify the query to make that happen.
  3. Last is changing your default isolation level -> RCSI. This is a server level setting and you need account for extra tempdb usage to keep track of all those transactions.

As long as you de-dupe and eliminate the indexes, then you add indexes that are desperately needed to help the queries go faster. Just the above talked point almost eliminates blocking problem.

Typically, you see users have blocking issues, they either had no indexes whatsoever or they had dozens of indexes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: