SQL Tuning Diary--the principle and troubleshooting of parallel waits

Original: SQL Tuning diary--the principle and troubleshooting of parallel waits

Overview

Dealing with projects today, customer response database at a certain time period, the response is particularly slow. We need to provide some optimization suggestions.

Phenomenon

Because it is a specific time period is slow, it is more convenient to troubleshoot. View the wait for this time period database directly. The view wait type discovers a large number of cxpaket wait types and waits longer.

Some crossing may know that when this wait is similar, the maximum degree of parallelism can be properly reduced to resolve. But why did you do it? Will reducing the degree of parallelism be able to solve the problem?

Cxpaket principle

Then what is Cxpaket waiting for? SQL Server chooses to perform parallel execution when the cost of the Database engine analysis query exceeds the set threshold. The database engine creates multiple tasks for this request. Each task processes a subset of the data. Each task can be executed on a separate cpu/core. Requests primarily use the production-consumption queue to interact with these tasks. If the queue is empty (that is, the producer does not push any data into the queue). This consumer must pause and wait. The corresponding wait type is the Cxpacket wait type. The request showing this wait type indicates that the task should be provided, but no (or enough) data is provided to consume. These producer tasks may in turn pause, waiting for some other type of wait.

For example, an index scan is an action that executes in parallel.

Make an analogy.

The client program is the boss, the database engine is the department leader, the boss sends a request to view the sales data for the last year. Leadership a look at the task of heavy workload, a person too slow, to find se years. Decisive decision to send a few more people. How many siege lions can be sent at a time? (depending on the maximum degree of parallelism) here is assumed to be 4. This assigns 4 people Xiao Li, Xiao Wang, Xiao Zhang, Xiao Chen to complete. What about the assignment for the year? We will elaborate later. For various reasons, others can do it, Xiao Wang has not finished. Leaders can not take the data of semi-finished products to find the boss, only to wait for Xiao Wang. This is cxpacket.

Investigation

Understand the principle of cxpacket, then how can we find out how to troubleshoot such problems? First of all, Xiao Wang is not lazy, his ability to work and other people are the same. So, we need to find out why Xiao Wang is slow,

Use the following script:

Select r.session_id,
Status
Command
R.BLOCKING_SESSION_ID,
R.wait_type As[request_wait_type],
R.wait_time As[request_wait_time],
T.wait_type As[task_wait_type],
T.wait_duration_ms As[task_wait_time],
T.BLOCKING_SESSION_ID,
T.resource_description
From Sys.dm_exec_requests R
Left JOIN Sys.dm_os_waiting_tasks t
On r.session_id = t.session_id
where r.session_id >=50
and r.session_id <> @ @spid;

Through the above statement we find that the parallel wait is waiting for lck_m_s. Indicates that the query was blocked by another operation. The above problem is caused by a write statement. This statement is a very simple insert action, why the write is so slow. You can view the disk response time, the disk queue

The discovery was surprisingly high.

Suggestions

The problem appears to be caused by the disk itself. Here are some suggestions for solutions:

1. Replace the disk with faster read and write speed

2. Current data files and log files on the same physical disk, separated

3. Proceed from the business. After communicating with the customer, it is found that this table is the Operation log table. Logs are logged each time a business operation is made. So special big.

corresponding to such a table, you can set up a separate folder group, file, and put the table on a separate disk, relieve IO pressure

4. For example, traditional mechanical disk iops are often bottlenecks, and throughput is not, so the size of the disk-formatted cluster is more important, and larger clusters can reduce the IOPS bottleneck.

5. For the log table, if you can change the program, the front-end program to merge the write, or if circumstances allow to open trace flag 610 to minimize the log write
6. Defragment the disk and merge & Delete the index of the log table to reduce write overhead can also play a role

SQL Tuning Diary--the principle and troubleshooting of parallel waits