SQL Server blocking queries

Original: SQL Server blocking query

In the production environment, sometimes the company customer service reflects the Web page half a day, in addition to the browser according to F12 network response to troubleshoot, to determine the Web server after the failure. You need to check if the database is blocked

At that time, the database production environment in the main table data volume of more than 2000w, sub-table data volume of more than 100 million, and updated and new frequently. Combined with a synchronous image, it consumes resources.

This is the time to create a new session, you probably need to know the following:

1. What is the current active session volume?

2. Session run time?

3. Are there any blocking between sessions?

4. Blocking time?

There are many ways to query blocking. sp_lock with SQL 2000, with a DMV of SQL 2005 and above

One. Blocking Query sp_lock

Perform exec sp_lock The following column key fields

The SPID refers to the process ID, which filters out the system process and only shows the user process spid>50.

Dbid refers to which database under the current instance, using the Db_name () function to identify the database

Type Request locked Mode

Request Status of Mode lock

GRANT: The lock has been acquired.

CNVRT: The lock is transitioning from another mode, but the conversion is blocked by another process that holds the lock (the pattern is conflicting).
WAIT: The lock is blocked by another process that holds the lock (mode conflict).

Summary: When mode is not in the grant state, you need to know the mode of the current lock and find the current SQL statement through the process ID

For example, if the current process ID is 416, and the mode status is wait, view the way DBCC InputBuffer (416)

The amount of information displayed with sp_lock queries is very small and it is difficult to see who is blocked by WHO. Therefore, it is not recommended when the database version is 2005 and above.

two. Blocking Query dm_tran_locks

1 SELECT 2 T1.resource_type,3 t1.resource_database_id,4 t1.resource_associated_entity_id,5 T1.request_mode,6 t1.request_session_id,7 t2.blocking_session_id8  fromSys.dm_tran_locks asT19 INNER JOINSys.dm_os_waiting_tasks asT2Ten  onT1.lock_owner_address=t2.resource_address;

The above query shows only blocked sessions, followed by blocking_session_id, which is the blocked session ID, and also uses DBCC INPUTBUFFER to query SQL statements

Three. Blocking Query sys.sysprocesses

1 SELECT 2 spid,3 Kpid,4 blocked,5Waittime as 'Waitms', 6 lastwaittype,7 db_name(dbid) asDB,8 Waitresource,9 Open_tran,TenHostname[program_name], One Hostprocess,loginame, A [Status] -  fromSys.sysprocesses with(NOLOCK) - WHEREKpid>0   and  [Status]<>'Sleeping'   andspid> -

Sys.sysprocesses can show how much the session process is, how long it waits, how many transactions the Open_tran has, and how many are blocking sessions. The overall content is more detailed.
Key field Description:

SPID session ID (process ID), SQL internal to a connection number, generally less than 50

Kipid thread ID
Blocked: Blocked process ID with a value greater than 0 for blocking, and a value of itself process ID represents IO operation
Waittime: The current wait time, in milliseconds.
Open_tran: Number of open transactions for the process
Hostname: Name of the client workstation that established the connection
The name of the Program_name application.
Hostprocess Workstation Process ID number.
Loginame login name.
[Status]
Running = Session is running one or more batches
Background = Session is running a background task, such as deadlock detection
Rollback = Session has a transaction that is being processed rolled back
Pending = Session is waiting for worker threads to become available
runnable = The task in the session is waiting, in the executable queue that is run by scheduler. Important
Spinloop = The task in the session is waiting for an adjustment lock to become available.
Suspended = Session is waiting for events (such as I/O) to complete. Important
Sleeping = Connection Idle

Wait resource format is Fileid:pagenumber:rid (5:1:8235440)

Kpid=0, waittime=0 idle connection

Kpid>0, waittime=0 running status
Kpid>0, waittime>0 need to wait for a resource to continue execution, typically suspended (wait io)
Kpid=0, waittime=0 But it is still the source of the blockage, view open_tran>0 transaction is not submitted in time.

If blocked>0, but waittime time is short, indicating that the blocking time is not long, not serious
If there are several runnable state tasks on the status, they need to be treated seriously. CPU overload does not process user's concurrent requests in time

SQL Server blocking queries