SQL SERVER query performance optimization-analysis of transactions and locks (4)

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

 

Ii. Cause and related handling of deadlocks

There are many causes of deadlocks, especially when the front-end application does not properly use transactions or improperly handle errors, resulting in long-term hold of transactions and not being closed. Next we will talk about the most common deadlocks and provide possible solutions.

1. Time-consuming query transactions

2. Incorrect transaction or transaction isolation level settings

3. The transaction is not handled correctly.

4. undetected distributed deadlock

5. The locking data granularity is too high or too low

6. Compile Blocking

 

(1) time-consuming query transactions

SQL SERVER 2005/2008 dynamically manages the view sys. dm_exec_requests provides relevant information (you can also observe the sysprocesses System View). For example, if the status field is "running", wait_type is not a "NULL" value. "Running" indicates that the process is still being executed, while wait_type indicates whether the process is waiting for resources. For example.

 

You can also view related information from activity monitor> Process Information> in Microsoft SQL server Management Studio of SQL Server 2005. For example, you can observe related information between processes for the related fields circled.

 

 

 

As shown in, the process "55" is locked by "54". You can also see the relevant information in the "wait type" column.

Use the SQL profiler tool to observe "SQL StmtCompleted/SQL BatchComplete" under the "t SQL" event ", or "SP StmtCompleted/SP BatchComplete/RPC Complete" under the "Stored Procedure" event category, you can observe the SQL statement execution, and display the T-SQL statement execution content through TextData) and Duration (the time required for statement execution) field to determine whether the execution of a statement is too long, leading to the locking behavior. For example, 1. Figure 2.

 

Figure 1

There is no end time in the place circled in 1, so the SQL statement is still waiting.

 

Figure 2

2. Although the execution end time exists, the duration execution time is too long. That is to say, this SQL statement waits for such a long time and waits for other transactions to release resources.

 

If a query statement uses a large amount of system resources, resulting in a long query time, the CPU, hard disk I/O, and memory usage may be high. The Missing Column Statistics in the "error and warning" event category in SQL PROFILER tool also deserves attention when too many SP: Recompile events of the "Stored Procedure" event type are generated, the former indicates that an effective execution plan cannot be generated, and the latter indicates the Writing Method of the stored procedure and the high-speed cache execution plan cannot be provided. Hash warning and Sort warning in the "error and warning" event category indicate that there may be no good indexes available.

 

Recommended Solution

If the transaction execution time is too long, it may lock other transactions that you want to execute until the resources are locked. For example, if the transaction isolation level is set to "repeatable reading", when the execution time of the query statement (SELECT) is too long, the UPDATE statement cannot UPDATE the data, this eventually causes system paralysis. In this case, try to solve the problem as follows:

1. Add or set an appropriate index to increase the query speed

2. update statistics to avoid using old statistics for execution plans

3. Redesign of data tables, stored procedures, and other objects

4. Check whether triggers and cursors are excessively used.

If the above method cannot improve work efficiency, you may need to consider modifying the system workflow.

1. Separate the work and do not execute all the requirements at the same time.

2. Cut the work time and schedule the work to the time when the system is not busy.

3. Cut the job attribute, hand the job to another database for execution, and divide the query and update into two databases for execution.

 

 

(2) Incorrect transaction or transaction isolation level settings

SQL SERVER 2005/2008 dynamic management view sys. dm_exec_requests will provide relevant information. The status field value of this SESSION_ID is "running", wait_type is not "NULL", through sys. the transaction_isolation_level field in the dm_exec_session dynamic view shows the transaction isolation level set by the process. In addition, from the "activity monitor --" Process Information "view in Microsoft SQL Server Management Studio, the" opened transactions "Field of the process is displayed as a non-" 0 "value, indicates that the process still has transaction resources.

Use the SQL PROFILER tool to find "TextData" and check whether the front-end command contains improper transaction settings. For example, SET IMPLICIT_TRANSACTION ON), transaction isolation level, or lock setting prompt.

 

Recommended solution:

Transaction settings are mostly related to the actual business logic, and it is difficult to determine whether it is necessary. If you find incorrect transactions or slightly classified isolation settings through the tracking file, we also need to discuss with developers the necessity of setting. Especially when a transaction contains a large amount of data, you may need to study how to cut it into a small transaction, but it still needs to meet the original data integrity and business logic requirements.

 

 

(3) Incorrect Transaction Handling

 

A transaction is started, but not rolled back or not committed, forming a non-committed transaction. Its features and observation methods are the same as described above. It can be seen that the process "54" still holds transactions, but the process is stuck and does not do anything, nor does it wait for any resources, but still holds transactions, from the "activity monitor --" Process Information "view in Microsoft SQL server Management Studio Management tool of SQL Server 2005, you can further observe the" Last batch "fields and check whether the process has held resources for a period of time.

 

 

Run sample code 1 in SQL 2005 (2008.

 

Select spid process, STATUS, Logon account = SUBSTRING (SUSER_SNAME (sid),), user machine name = SUBSTRING (hostname ), locked = convert (char (3), blocked), database name = SUBSTRING (db_name (dbid),), cmd command, waittype as wait type, last_batch last batch processing time, number of uncommitted transactions in open_tran from master. sys. sysprocesses -- lists the locks of others (values in the blocked field in other processes) but does not lock itself (blocked = 0) Where spid in (select blocked from master. sys. sysprocesses) and blocked = 0

 

 

 

 

Recommended Solution

Use the transaction event category in the SQL profiler tool to record transaction events triggered by SQL SERVER. You can also use dbcc opentran ('<database> ') command to observe which program owns the longest transaction event to execute for a database. If no database name or ID is specified, the longest transaction event to be executed in the database where the current connection is located is returned, generally, uncommitted transactions may be caused by the failure to handle errors.

After the dbcc opentran command is executed, as shown in figure. UID is meaningless.

 

 

For example, when the command commit is executed, the transaction is not rolled back after the batch process is abandoned. The error handling should be as follows.

If @ trancount> 0 Rollback tran --- or Set: Set XACT_ABORT on

 

(The preceding settings mean that the SQL SERVER rolls back the transaction in case of any errors)