How do you resolve a block issue?

How do you resolve a block issue?

Steps in troubleshooting:

1. Identify the main blocking session (head blocker)
2. Find the query and transaction that is causing the blocking (what is holding locks for a prolonged period)
3. Analyze/understand why the prolonged blocking occurs.
4. Resolve blocking issue by redesigning query and transaction.

How do you troubleshoot blocking caused by compile locks?

Blocking that is caused by compile locks can be detected by using standard blocking troubleshooting methods. If your stored procedure name starts with the sp_ prefix and is not in the master database, you see sp_cache_miss before the cache hit for each execution even if you owner-qualify the stored procedure.

What is blocking and how would you troubleshoot it?

Blocking occurs when two or more rows are locked by one SQL connection and a second connection to the SQL server requires a conflicting on lock on those rows. This results in the second connection to wait until the first lock is released.

What is blocking in SQL server?

Blocking in SQL servers happens when a connection to SQL server blocks one or more query, and another connection to SQL server requires a conflicting lock type on query, or query locked by the primary connection. This leads to the another connection waiting until the primary connection releases its locks.

How to solve blocker and waiters on tempdb?

Both the blocker and the waiters (10 of them) has PAGELATCH_EX wait type on database tempdb and all the sessions’ (blocker and the waiters) waitresource is “2:1:122”. I checked the SQL codes and saw that table variables are created in most of the sessions and I suspect that might be the culprit.

Why is SQL server blocking page number in tempdb?

Since the pages that SQL is most frequently waiting on are in the tempdb database (page number in dbid 2), you may be facing tempdb allocation latch contention.

What’s the optimal number of tempdb data files?

The optimal number of tempdb data files depends on the degree of contention seen in tempdb. As a starting point, you can configure tempdb to be at least equal to the number of logical processors that are assigned for SQL Server. For higher-end systems, the starting number could be eight (8).

How can I improve the concurrency of tempdb?

To improve the concurrency of tempdb, try the following methods: Increase the number of data files in tempdb to maximize disk bandwidth and reduce contention in allocation structures. As a rule, if the number of logical processors is less than or equal to eight (8), use the same number of data files as logical processors.