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

I. use tools to observe and analyze lock information in the database

For concurrent systems and business systems with a large number of read/write database operations, when multiple people access the database at the same time, one of the most complicated situations is that a large number of transactions are entangled with resources, locks and waits for each other, also known as deadlocks. When there are many locks in the database, the system will not be able to provide normal services in an instant. Observe the usage of system resources, and you will find that the CPU usage is not high, the memory usage is not high, there are many unused memory, the network bandwidth is sufficient, and the hard disk is not busy, if you use the database management tool to query the database, the data in SQL SERVER is normal. However, when you use the system to access the database, you have to wait for a long time. If you use more, the connection times out, the database has no response.

This phenomenon is similar to a large number of vehicles entering the highway, but only one or two vehicles are opened at the toll gate. The traffic at the toll gate is congested, but there are a large number of vehicles in the rear. At this time, all the vehicles will slow down, and drivers will slow down and stop each other and wait for each other, in the end, the rear car will be forced to stop to form a highway parking lot. (Just like the first holiday when the highway is free, that is, the 2012 National Day highway congestion, the highway into a parking lot)

If the database is designed improperly and the data table is designed to be overheated, that is, the access behavior of all applications is related to a certain data table, and even some fields are accessed in a centralized manner, as in the preceding example, all vehicles are driving on the highway at the same location (toll gate on the highway. At this time, the time is mixed with the two fast access behaviors, just like a big truck and a car fighting for a lane, it is easy to lock each other, forming a deadlock and moving.

This kind of performance delay occurs, especially when the data is used for a long time. When the new system is launched, or the SQL statement is not well written, for example, if Select * from SaleInfo does not have the where condition, because the data volume is small in the initial stage, the result can be instantly retrieved without affecting others. However, when the system is used for a period of time, the number of databases increases, the number of users increases, and the usage method changes. Poor SQL statements are slow to run, and the database begins to accumulate the need to wait for processing, that is, mutual interference causes locks. After interaction, the entire system may be paralyzed instantly. When you observe the locks of the current database, you may find thousands of locks, which is difficult to figure out.

This article first discusses which tools can be used to observe and analyze the lock information in SQL SERVER. Of course, all performance tuning requires first understanding the situation, preferably repeated scenarios, such as at what time every day, how many users are online, and what features are executed, etc. First understand the situation through the disk, then evaluate the problem of SQL server, and then further observe the SQL SERVER.

Step 1: Use the graphical interface tool provided by SQL SERVER to observe which processes are locked.

1) SQL server 2005 can use Microsoft SQL Server Management Studio's "Management --" activity monitor "--" right-click "--" process information ", the connection and lock details are displayed here. For example, 1.

 

Figure 1

 

 

2) Information in the graphic interface tool provided by Microsoft SQL server Management Studio of SQL Server 2005 is not automatically updated. To refresh the content on the "activity monitor" page, you must manually click the "refresh" button on the toolbar above. For example, 2 is the content displayed before the "refresh" button is clicked. Figure 3 shows the content displayed after the "refresh" button.

Figure 2

Figure 3

 

2) You can use the settings in SQL SERVER 2005 to set the automatic update time period. For example, 4.

 

 

Figure 4

 

 

4) "activity monitor -- Process Information" in Microsoft SQL server Management Studio of SQL Server 2005 provides a considerable amount of information, the "Wait type" and "blocking" can display the relationship between processes and the status information of processes.

If you want to query the details of the "Lock, you can right-click the "activity monitor --" process-based lock "and" activity monitor -- "Object-based lock" tags in the window to observe.

Activity monitor-locks classified by Process

For example, 5.

 

Figure 5

Activity monitor-locks classified by objects

For example, 6.

 

Figure 6

 

The following describes how to use the activity monitor in SQL SERVER 2008:

1) The activity monitor of SQL SERVER 2008 is not under management. 1. Click the icon on the toolbar. 2. Right-click the database server, such as 7. Then there will be a graphical interface tool of 8.

 

 

Figure 7

 

Figure 8

 

2) SQL SERVER 2008 is automatically updated by default. The interval between Automatic Updates is 10 seconds. You can set it to the automatic update time period through the settings in. For example, 9. Right-click "Overview", select "Refresh Interval", and select the interval at which the activity monitor obtains new instance information.

 

 

Figure 9

 

 

3) The "activity monitor-" process "in Microsoft SQL server Management Studio of SQL Server 2008 only provides some basic information, the "Wait type" and "blocking" can display the relationship between processes and the status information of processes. The provided locked process information is not detailed in SQL SERVER 2005.

 

Figure 10