Original article: Click to open the link to generally say SQL Server optimization, we will first think of the stored procedure, table, field type, length and index. Today, I am not going to discuss these topics. Let's talk about the role of sp_lock and sp_who in database optimization. To ensure that only one thread is the same at a time
Original article: Click to open the link to generally say SQL Server optimization, we will first think of the stored procedure, table, field type, length and index. Today, I am not going to discuss these topics. Let's talk about the role of sp_lock and sp_who in database optimization. To ensure that only one thread is the same at a time
Original article: Click to open the link
In general, SQL Server optimization, we will first think of the stored procedure, table, field type, length, and index.
Today, I am not going to discuss these topics. Let's talk about the role of sp_lock and sp_who in database optimization.
The SQL Server database engine uses a locking mechanism to ensure that only one thread accesses objects of the same resource at a time. When the system has a large number of locks, data blocking occurs ". Therefore, your database design and programming should be scientific and reasonable, so that the number of locks involved in SQL server can be minimized. When your system is slow, you should pay attention to whether the database is blocked. Sp_lock is a system stored procedure of SQL Server 2000. EXECUTE sp_lock executes this stored procedure and can view the currently blocked data table to analyze the program and solve the problem.
When you execute sp_lock, you will get the following results:
spid dbid ObjId IndId Type Resource Mode Status51 7 0 0 DB S GRANT51 1 1131151075 0 TAB IS GRANT52 7 0 0 DB S GRANT53 7 0 0 DB S GRANT54 7 0 0 DB S GRANT56 7 0 0 DB S GRANT57 7 0 0 DB S GRANT
This program will return a large amount of lock-related information from syslockinfo in the primary database, and the primary database is a system workbench that includes all the information that allows, converts, and waits for the lock request.
From the above results, we can see the spid, dbid, objid, indid, type, resource, mode, and status fields. Spid is the process identification number used to identify the connection to the SQL server. To discover which users are connected to the spid, You need to execute the Stored ProcedureSp_whoAnd transmit the spid to the program as a parameter. Dbid is a locked database. You can find it in the sysdatabases table of the primary database. The objid field is used to display the object in which the lock occurs in the database. To view this object, you can query the specified objid in the sysobjects table of the primary database.
A single record generated on the screen above does not necessarily show what is happening in your work environment. When running this program, you want to find 500 to 1000 or more results. Each execution of sp_lock may have different results, because a new lock occurs, and some old locks have been removed. If you find that a large number of results returned by sp_lock have the same spid, it is very likely that the process is performing large-scale processing, and these locks may start to block new transactions.
When you find that a spid gets a lot of database locks, this will help you determine what stored procedures or statements are running. To achieve this, run the following DBCC command:
Check deadlock Information
Dbcc inputbuffer (spid)
This DBCC command returns information about the statements that are running in the EventInfo field, and displays the SQL commands that are being executed.
When you executeDbcc inputbuffer (spid)Then, you will get the following results:
EventType Parameters EventInfo
You can see from EventInfo which process or query takes a long time, and it is easy to generate a deadlock.
You can execute dbcc checkdb to recreate the index.
If you want to see more details:
Select * from sys. objects where object_id = 0. Check whether the process causes performance problems. It may be a constraint or table data.
Select * from sys. sysdatabases where [dbid] IN (,). This shows which database has many deadlocks.