Use SYS. sysprocesses to check the blocking and deadlock of SQL Server

SYS. sysprocesses is an important system view. It is mainly used to locate and solve SQL Server blocking and deadlock.
Msdn: contains information about processes running on an SQL server instance. These processes can be client processes or system processes.

Main fields in the View:
1. spid: SQL servr session ID
2. kpid: Windows Thread ID
3. Blocked: The session ID that is blocking the request. If this column is null, the request is not blocked.
4. waittype: ID of the currently connected waiting resource, indicating whether to wait for the resource. 0 or null indicates no need to wait for any resource.
5. waittime: The current waiting time, in milliseconds. 0 indicates no waiting.
6. dbid: ID of the database currently being used by the Process
7. uid: User ID for executing commands
8. login_time: the time when the client process logs on to the server.
9. last_batch: the time when the stored procedure or execute statement was last executed. For system processes, the start time of SQL Server is stored.
10. open_tran: Number of opened transactions of the process. If there is a nested transaction, it will be greater than 1
11. status: process ID status; dormant = indicates that the session is being reset; running = indicates that one or more batch processes are running; background = indicates that a background task is running; rollback = the session is processing transaction rollback; pending = the session is waiting for work to become available; runnable = the task in the session is waiting for the executable queue to be run by obtaining scheduler; spinloop = the task in the session is waiting for the spin lock to become available; susponded = the session is waiting for the event to complete
12. hostname: name of the client workstation to which the link is established
13. program_name: ApplicationProgramIs the application name specified in the connection string.
14. hostprocess: ID of the process in the client workstation of the Connected Application
15. CMD: The command being executed
16. loginame: Login Name

Application instance:
1. Check whether the database is blocked
The blocked field of the link is not 0. For example, the blocked field of spid53 is not 0, but 52. When blocked of spid 52 is 0, we can conclude that blocking occurs, and 53 is blocked by 52. If you find that the value of a connected blocked field is equal to that of itself, it indicates that the connection is being read/written to a disk and it has to wait for its own I/O.

2. Find the database with the link
Check dbid. To obtain the dbid, run the following query to obtain the Database Name: Select name, dbid from Master. SYS. sysdatabases

Find your first blog: Use SYS. sysprocesses to check the blocking and deadlock of SQL Server