SQL Server parallel Operations optimization to prevent parallel operations from being suppressed and affecting SQL execution efficiency

Why do I also say parallel to SQL Server:

These days the garden write about SQL Server parallel articles a lot, regardless of, let people have a deeper understanding of parallel operations.

What I want to say is that although parallel operations may (and do not necessarily) have such or such problems, we cannot deny parallelism and still use good parallelism.

However, in real development, the writing of some SQL statements can lead to the use of parallelism, which affects the efficiency of SQL execution

So, this article to express is: we want to use good parallelism, do not let some SQL writing problem "suppress" the parallel, so that we can not enjoy the pleasure of parallel

About parallel SQL Server:

The so-called parallelism, when SQL Server is relatively expensive to execute (this relative to your setup), if the database server has more than one CPU,

The SQL Server query engine takes a parallel approach, with multiple CPUs participating in the entire computing process, each CPU "sharing" a portion of the computational task, and finally summarizing a behavior that merges the computations of each CPU

Sometimes, improper parallel query not only will not speed up the query, want to slow down the efficiency of the query, if the improper parallel operation, and even affect the stability of the entire server.

So how much the SQL Server at the cost of enabling parallel, is configured, this configuration can be modified according to the specific situation, some people say that the value of the unit is "seconds", seemingly did not see the authority of the data said exactly what the unit is, here temporarily do not pursue

Have clear this threshold unit of the garden friendship at the enlighten, thanks

Although the parallel operation may exist such a problem, but we can not unworthy, the use of good parallelism, often always outweigh the disadvantages.

But not all of the implementation costs of large SQL can be used in parallel operations, in real development, some of the syntax of SQL will be suppressed to parallel operations, resulting in the entire SQL statement (stored procedure) is not efficient.

Here's an example.

How the parallel query becomes serial:

The following is a very simple query operation, which, by default, turns on parallelism, and you can see that 8 threads are opened to calculate the SQL statement.

　　

Of course, this SQL is very efficient execution, CPU time is 622 milliseconds, the total time to execute is 130 milliseconds,

Do not confuse here, CPU time of 633 milliseconds, is 8 CPU total CPU time consumed, greater than the overall execution 130 milliseconds is normal

　　

The following creates a very simple function,

CREATE function [dbo]. [fn_justfunction] (@p_date  date) returns Date  as begin    return @p_date End

This function has no practical significance, and execution is very simple, passing in a time, returning this time,

　　

　　

Of course this is just for the following operation demonstration, you can say that my egg hurts, I just to demonstrate the phenomenon of parallel suppression

Do you have a similar syntax for flipping through your SQL code?

Then we write this query, that is, in the query condition so that processing createdate>dbo.fn_justfunction (' 2015-1-1 ')(note is not a table column, but the function on the query condition),

Note that this function does not affect any query results, the incoming 2015-1-1, the return bit is still 2015-1-1, but this change, the parallel becomes serial,

During SQL execution only one CPU soared, used to reach around 80%, while the other CPUs were nothing like people, not up to help, or very busy

Do you remember how long the parallel operation was performed? 130 milliseconds, now the size looks like, here is 4S, that is, 4000 milliseconds. How many times has it been so bad that I can't figure out math?

　　

　　

As you can see, the efficiency difference between parallel operation and serial operation is still very large, and the utilization of CPU is not enough (of course I do not emphasize that it is reasonable to use full CPU)

Again, it's not that the addition of a function to the table field suppresses the index or anything, it's purely a parallel operation.

Of course, the suppression of parallelism is not only in the query conditions in the use of functions, the actual development, the impact will be greater,

Because the data in the actual business is likely to be larger and SQL may be more complex, this situation can be even more difficult to discern.

For example, connection conditions, such as the following, the use of functions on the connection condition resulting in the inability to use the parallel situation, is also the actual development encountered

SELECT * from TableA a inner joins TableB B on A.id=b.id and a.column=dbo.function (@Variable) where * * *

Of course, there are not only these two ways of suppressing parallel operations, but there is also the potential for other similar formulations to affect parallel queries.

This requires us to write SQL, not only to note that the function can no longer be used on the field (the index on the field can not be used), as well, the query condition as far as possible do not use the function, it may affect the parallel operation.

If the processing of parallel operations is suppressed:

What should I do if I want to solve a problem like these? In fact, it is also very simple, we recommend that the query criteria be assigned to a variable after the function operation, using variables to query the query criteria.

Once again began the pleasant parallel, enjoying the pleasure of parallel brings.

　　

It is similar to the function processing on the join condition, after calculating the result, save in a variable, write the variable in the join condition,

Of course there may be other ways that I haven't thought of yet.

Summarize:

In this paper, a simple example demonstrates the suppressed behavior of parallel operations, illustrating the huge difference between parallel and serial performance on a cost-significant SQL.

The Query method mentioned here is that the query condition suppresses parallelism because of the reason of the function, which is completely different from using the function to suppress the index on the query column.

Parallel query can fully mobilize CPU resources, to complete the query in an efficient way, reasonable utilization of parallelism will greatly improve the efficiency of SQL execution.

In order to take advantage of good parallelism, when writing SQL, it is important to pay attention to prevent the parallel operation from being suppressed, which will affect the performance.

SQL optimization is a difficult and repetitive process, and even so, it's fun.

In the face of complex SQL, not only must have the perfect technology, but also have enough patience, to see the essence of things.

The understanding of parallelism is not sufficient, there is no place to hope that you crossing point, thank you.

Source: http://www.cnblogs.com/wy123/p/5661848.html

SQL Server parallel Operations optimization to prevent parallel operations from being suppressed and affecting SQL execution efficiency