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 the actual development, the writing of some SQL statements will lead to the use of parallel, 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:

So-called parallelism, when SQL Server is relatively expensive to execute (this relative to your setup), if there are multiple CPUs on the database server, the SQL Server query engine takes a parallel approach, that is, multiple CPUs participate in the entire operation process, each CPU " Share "part of the computational task, and finally summarize the calculation of the merging of each CPU a behavior sometimes, the improper parallel query not only does not speed up the query, want to slow down the efficiency of the query, if the improper parallel operation, 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 specific circumstances, 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 is not to be held clear this threshold unit of the park 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 execution efficiency is good, CPU time is 622 milliseconds, the total time to execute is 130 milliseconds, here do not confuse, CPU time of 633 milliseconds, is 8 CPU total CPU time, more than the overall execution of 130 milliseconds is normal

　　

The following creates a very simple function,

[dbo]. [fn_justfunction] (@p_date date)returns dateas@p_dateend  

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 completely say that my egg hurts, I just to demonstrate the parallel suppressed phenomenon to turn over your SQL code, there is no such writing?

Then we write this query, that is, on the query condition so that the 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, 　　In 2015-1-1, the return bit is still 2015-1-1, but such a change, parallel to become serial, SQL execution period only a CPU soared up, used to reach about 80%, while the other CPUs with nothing else, not to help, or very busy Do you remember how long the parallel operation was performed? 130 milliseconds, now it looks like how much, this is 4S, that's 4000 milliseconds.

　　

As you can see, the efficiency difference between parallel operation and serial operation is still very large, the use of the CPU is not sufficient (of course, I do not emphasize that must be full of all the CPU is reasonable) again, here is not in the table field to add a function to suppress the index what, purely affect the parallel operation. 　　Of course, the suppression of parallelism is not only in the query conditions in the use of functions, actual development, the impact will be greater, because the actual business data may be larger, SQL may be more complex, this situation may be more difficult to identify. For example, connection conditions, such as the following, the use of functions on the connection condition resulting in the inability to use parallelism, but also the actual development encountered in the SELECT * from TableA a INNER join TableB B on A.id=b.id and A.column=dbo.func 　　tion (@Variable) where * * * It is not only these two ways to suppress parallel operations, but it is also possible that other similar formulations may 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.

To summarize, there are a few things that will suppress the use of parallelism (later discovered and updated):

1. Actions such as functions on the query condition

For example: Createdate>dbo.fn_justfunction (' 2015-1-1 ')

2. Actions such as functions on join conditions

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

3, actions such as strong functions are on the query column

For example: functionname (ColumnName) >55, this situation, if there is an index on the query column, not only suppress the index, but also suppress the parallel

How to handle a situation in which parallel operations are 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.

　　

For the function on the connection condition is similar, after calculating the result, save in a variable, the variable is written in the connection condition, of course there may be other methods, I have not thought for a moment.

Summarize:

In this paper, a simple example demonstrates the phenomenon of parallel operation being suppressed, and illustrates the huge difference between parallel and serial performance on a cost-significant SQL. The Query method mentioned in this article is that the query condition is suppressed by the reason of the function, which is completely different from the case of using 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.

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