M_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0 If DBCC printed error messages, contact your systemīUF = 0x20672000 bhash = 0x00000000 bpageno = (1:5798) (For more information on using DBCC PAGE, see Chapter 33.)ĭBCC execution completed. You can determine this by examining the page header for the corresponding page using the DBCC PAGE command and passing it the dbid, the file ID, and the page number. When the IndId in the sp_lock output is 1 and the Type is KEY, it is not clear whether the lock is on a nonleaf index row or a data row. To identify the name of the table, you can use the object_name() function, running it in the database in which the object resides: Process 52 has an Exclusive (X) lock granted on the same key resource (Resource = (37005ad7376d)). Spid dbid ObjId IndId Type Resource Mode Statusĥ2 8 1685581043 1 KEY (37005ad7376d) X GRANTĥ5 8 1685581043 1 KEY (37005ad7376d) S WAITįrom this output, you can see that process 55 is waiting for a Shared (S) lock on the key for the object whose ID is 1685581043 and whose index ID is 1 (indicating that this is a data or index row in a clustered index). To determine what table, page, or rows are involved, and at what level the blocking is occurring, you can use the sp_lock stored procedure: In the previous example, you can see process 52 blocking process 55. If the value is anything non-zero, the session is being blocked and the number in the blk column is the spid of the process that is causing the blocking. If the value in the blk column is 0, then no blocking is occurring for that session. Spid ecid status loginame hostname blk dbname cmdĥ2 0 sleeping sa RRANKINSA20P 0 bigpubs2000 AWAITING COMMANDĥ5 0 sleeping sa RRANKINSA20P 52 bigpubs2000 SELECT To identify whether a process is being blocked, you can examine the blk column in the output from sp_who: The output from these commands can be easily translated to what you would see in EM. The examples in the rest of this section will use sp_who and sp_lock. Also, when sp who and sp lock are run in Query Analyzer, it is easier and faster to refresh the resultsets than it is to refresh the display in Enterprise Manager. The sp_who and sp_lock system procedures, as well as querying the syslockinfo table directly, will still work. Sometimes, because of the additional information that Enterprise Manager is trying to display, it can become blocked by locking activity in tempdb and not display anything. I prefer to use sp_who and sp_lock because they tend to be faster than using Enterprise Manager.
To identify locking contention between processes, you can use Enterprise Manager as discussed earlier in this chapter in the "Monitoring Lock Activity in SQL Server" section, or the system-stored procedures sp_who and sp_lock. When a client application appears to freeze after submitting a query, it often is due to locking contention. Locking contention between two processes. Because this is not a deadlock scenario (which will be covered in the "Deadlocks" subsection later in this section), SQL Server takes no action. Until Process 2 commits or rolls back its transaction and releases the lock on :341, the lock will continue to be held. Before Process 1 can acquire the lock that it needs on page 1:341 to complete its transaction, Process 2 acquires an exclusive lock on page 1:314. Process 1 has initiated a transaction and acquired an exclusive lock on page 1:325. Locking contention is noticed in the client application by the apparent lack of response from SQL Server.įigure 38.11 demonstrates an example of locking contention. By default, the process will wait indefinitely for the lock resource to become available. Locking contention occurs when a transaction requests a lock type on a resource that is incompatible with an existing lock type on the resource. It is further compounded by increasingly complex or long-running transactions. Whereas the first two problems will result in poor application performance regardless of the number of users on the system, locking contention becomes more of a performance problem the greater the number of users. In the grand scheme of things, the most likely culprits of SQL Server application performance problems are typically poorly written queries, poor database and index design, and locking contention.
0 kommentar(er)
