SQL Server query performance optimization-analysis of transactions and locks (III)

SQL Server query performance optimization-analysis of transactions and locks (2)

 

Next let's take a lookSp_who2If you query the stored proceduresSource code, You can find that the storage process of this system is organizedMaster. SYS. sysprocessesSystem View. Used hereSp_who2Next.

Step 1: Execute Example 2 and Example 3 in the query AnalyzerCode. (Is the previous articleArticleSQL Server query performance optimization-analysis of transactions and locks (2)Examples in)--Example 2

Step 2: open a query analyzer interface and enterExec sp_who2For example, in this interface, you can easily observe the association between the lock and see the process"56.53 "lock.

 Use  Test  Go  Begin   Tran  Update Book Set Name =  '  Ms SQL 2008  ' Where Bookid =  1  --  -Switch to another query interface and run the following code:  --  Example 3  Use  Test  Go  Select   *   From Book Where Bookid =  1 Go  

 

 

 

 

You can use DBCC inputbuffer (53) to view the query statements executed by the process "53. Such as 1 and 2.

 

Wbk_pde_list table in SQL 2008

 

 

Figure 1

Book table

 

Figure 2

 

Of course, if you use SQL Server 2005, you can also useMicrosoft SQL Server Management Studio"Activity monitor" in--"Process Information" double-click a process to view the query statements executed by the process. For example, 3.

 

Figure 3

You can alsoSp_lockSystem stored procedures to observe the process"53 "and"56. Run the following command:

Exec sp_lock 53

Exec sp_lock 56

The result is as follows:

Book table

 

Figure 4

 

The execution result of the preceding statement is the same as that in SQL Server 2005.Microsoft SQL Server Management Studio"Activity monitor" in--"The locks by process are similar.

Sqls 2005

 

Figure 5

Of course, in SQL 2008, only the following SQL statements can be executed.

Exec sp_lock 54

Exec sp_lock 55

 

Figure6

 

As shown above, type in Figure 6If the field isPAG, ThenResourceIndicates the number of files on which the page is stored in the database. And the page number. We can useDBCC pageTo observe the distribution.

If indidIs1Indicates a clustered index.DBCC pageThe details of the entire page are queried. IfIndidGreater1Indicates a non-clustered index.DBCC pageThe index key value and hash value are queried.For example, 7.

DBCC traceon (3604)

DBCC page (, 3)

 

Book

 

Figure7

 

Figure 5Object ID and descriptionAnd figure 7InKeyhashvalueBy comparing fields, we can further see what kind of record is locked.

You can also combineCombined with figure 6InResourceAnd figure 7InKeyhashvalueBy comparing fields, we can further see what kind of record is locked.

Note: Figure 7 here is not a detail of Figure 6.

 
Select Db_name(28) Database Name,Object_name(117575457) Table Name ,(SelectNameFromSYS. IndexesWhere Object_id=117575457 AndIndex_id=54) Index name
 

 

 

 

 

You can also openSQL profilerObserve the interaction between multiple users.

 

 

To sum up, you can observe from the following aspects whether the database is running due to the lock and lock.

1. Microsoft SQL Server Management StudioOrSp_who2System stored procedures to check whether many processes in the database are locked.

2. ObserveMaster. SYS. sysprocessesIn the system viewWaittimeWhether the field value is abnormal or not.

3. SQL profilerMany of the results recorded by the toolAttentionEvent, representingSQLThe statement has not responded after being executed for a long time, and the front-endProgramDiscard execution.

4. SQL ServerThe server is not busy. For example,CPUThe usage of memory, hard disk, network and other hardware resources is not very high, but the system efficiency is not high, or on the contrary, the above resources are continuously used for a certain operation, however, this operation cannot be completed, and the resources it holds cannot be released.

5. PassMicrosoft SQL Server Management StudioPerformance Monitor,SQL profilerAnd perform cross-analysis to verify each other.