MS SQL blockers

Sql starts to execute slowly, because long sql executions block many other sqls.

Check approach: 

There are reports that can be generated from within management studio for blocking transactions and long running sql.

Solutions:

Find the blocker sql with sql below:

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id =tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address =wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id =tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id =tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id =wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2

Then try to reduce the execution time of the sql

Reference link: 

http://blog.sqlauthority.com/2010/10/06/sql-server-quickest-way-to-identify-blocking-query-and-resolution-dirty-solution/

Reference content:

SQL SERVER – LCK_M_XXX – Wait Type – Day 15 of 28

Locking is a mechanism used by the SQL Server Database Engine to synchronize access by multiple users to the same piece of data, at the same time. In simpler words, it maintains the integrity of data by protecting (or preventing) access to the database object.

From Book On-Line:

LCK_M_BU
Occurs when a task is waiting to acquire a Bulk Update (BU) lock.

LCK_M_IS
Occurs when a task is waiting to acquire an Intent Shared (IS) lock.

LCK_M_IU
Occurs when a task is waiting to acquire an Intent Update (IU) lock.

LCK_M_IX
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock.

LCK_M_S
Occurs when a task is waiting to acquire a Shared lock.

LCK_M_SCH_M
Occurs when a task is waiting to acquire a Schema Modify lock.

LCK_M_SCH_S
Occurs when a task is waiting to acquire a Schema Share lock.

LCK_M_SIU
Occurs when a task is waiting to acquire a Shared With Intent Update lock.

LCK_M_SIX
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock.

LCK_M_U
Occurs when a task is waiting to acquire an Update lock.

LCK_M_UIX
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock.

LCK_M_X
Occurs when a task is waiting to acquire an Exclusive lock.

LCK_M_XXX Explanation:

I think the explanation of this wait type is the simplest. When any task is waiting to acquire lock on any resource, this particular wait type occurs. The common reason for the task to be waiting to put lock on the resource is that the resource is already locked and some other operations may be going on within it. This wait also indicates that resources are not available or are occupied at the moment due to some reasons. There is a good chance that the waiting queries start to time out if this wait type is very high. Client application may degrade the performance as well.

You can use various methods to find blocking queries:

• EXEC sp_who2
• SQL SERVER – Quickest Way to Identify Blocking Query and Resolution – Dirty Solution
• DMV – sys.dm_tran_locks
• DMV – sys.dm_os_waiting_tasks

Reducing LCK_M_XXX wait:

• Check the Explicit Transactions. If transactions are very long, this wait type can start building up because of other waiting transactions. Keep the transactions small.
• Serialization Isolation can build up this wait type. If that is an acceptable isolation for your business, this wait type may be natural. The default isolation of SQL Server is ‘Read Committed’.
• One of my clients has changed their isolation to “Read Uncommitted”. I strongly discourage the use of this because this will probably lead to having lots of dirty data in the database.
• Identify blocking queries mentioned using various methods described above, and then optimize them.
• Partition can be one of the options to consider because this will allow transactions to execute concurrently on different partitions.
• If there are runaway queries, use timeout. (Please discuss this solution with your database architect first as timeout can work against you).
• Check if there is no memory and IO-related issue using the following counters:

Checking Memory Related Perfmon Counters

• • SQLServer: Memory ManagerMemory Grants Pending (Consistent higher value than 0-2)
  • SQLServer: Memory ManagerMemory Grants Outstanding (Consistent higher value, Benchmark)
  • SQLServer: Buffer ManagerBuffer Hit Cache Ratio (Higher is better, greater than 90% for usually smooth running system)
  • SQLServer: Buffer ManagerPage Life Expectancy (Consistent lower value than 300 seconds)
  • Memory: Available Mbytes (Information only)
  • Memory: Page Faults/sec (Benchmark only)
  • Memory: Pages/sec (Benchmark only)
• Checking Disk Related Perfmon Counters
  • Average Disk sec/Read (Consistent higher value than 4-8 millisecond is not good)
  • Average Disk sec/Write (Consistent higher value than 4-8 millisecond is not good)
  • Average Disk Read/Write Queue Length (Consistent higher value than benchmark is not good)

Read all the post in the Wait Types and Queue series.

Note: The information presented here is from my experience and there is no way that I claim it to be accurate. I suggest reading Book OnLine for further clarification. All the discussion of Wait Stats in this blog is generic and varies from system to system. It is recommended that you test this on a development server before implementing it to a production server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)