Use SYS. sysprocesses to check the blocking and deadlock of SQL Server
Source: Internet
Author: User
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
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
