SQL Server parallel Operations optimization avoids the suppression of parallel operations and affects the execution efficiency of SQL _mssql

Why should I also say SQL Server parallelism:

These days in the garden to write about SQL Server parallel to a lot of articles, no matter what, let people have a deeper understanding of parallel operations.

What I want to say is that while parallel operations may (and not necessarily) exist, we cannot deny parallelism and still take advantage of parallelism.

However, in actual development, the writing of some SQL statements can result in no parallelism, thus affecting the execution efficiency of SQL

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

About SQL Server parallelism:

The so-called parallelism, refers to SQL Server for those with a relatively high execution cost (this relative to your setup) of the SQL, if the database server has more than one Cpu,sql server query engine will be in parallel, that is, the use of multiple CPUs to participate in the entire operation process, each CPU " Sharing "Part of the computing task, the final summary of the calculation of the merging of each CPU a behavior sometimes, improper parallel query not only will not speed up the query, want to reverse the efficiency of the query, if the use of improper parallel operation, even affect the stability of the entire server.

So SQL Server at what cost to enable parallel, is configured, this configuration can be modified according to the specific situation, some people say that the unit of the value is "seconds", seemingly did not see the authority of the information said exactly what the unit is, here is not to investigate

Have a clear this threshold unit of the garden friendship at the enlighten, thank you

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

But not all of the execution costs are large SQL can be used in parallel operations, in actual development, some of the writing of SQL will be suppressed to parallel operations, resulting in the entire SQL statement (stored procedures) inefficient.

Here is an example of how to do this.

How parallel queries have become serial:

The following is a very simple query operation, which, by default, turns on parallelism and, as you can see, opens up 8 threads to compute the SQL statement.

Of course, this SQL is a good execution efficiency, CPU time is 622 milliseconds, the total execution time is 130 milliseconds,

Do not confuse here, CPU time of 633 milliseconds, 8 CPU total CPU time consumed, greater than the overall execution of 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 does not have any practical meaning, execution is also very simple, pass in a time, return this time,

Of course, this is just for the following Operation demo, you can completely say that my egg hurts, I just to demonstrate the parallel suppressed phenomenon

Flipping through your SQL code, is there a similar notation?

And then we write this query, which is to deal with createdate>dbo.fn_justfunction (' 2015-1-1 ') on the query condition (note that it is not a table column, but a function on the query condition), notice 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 into the serial, SQL execution only one CPU was racing up, used to reach about 80%, at the same time the other CPU with nothing to do with people, also do not come to help, Still very busy remember the above parallel operation mode execution time is how much? 130 milliseconds, now what does it look like, this is 4S, which is 4000 milliseconds. I can't figure out how many times I'm poor at math.

As you can see, the efficiency of parallel operations and serial operations is still very large, the utilization of the CPU is not sufficient (of course, I do not emphasize that must be full of all the CPU to be reasonable)

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

Of course, the suppression of parallel writing 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 real business is likely to be larger and SQL may be more complex, it may be more difficult to discern.

For example, the connection conditions, such as the use of the function on the join conditions can not use the parallel situation, but also in the actual development encountered

SELECT * FROM TableA a INNER join TableB B on A.id=b.id and A.column=dbo.function (@Variable) where * *

Of course, there are not only two ways to suppress the parallel operation, but also the possibility that other similar writing will affect the parallel query.

This requires us to write SQL, not only to pay attention to the field can not use the function (the index on the field), as well as the query condition as far as possible do not use functions, it is possible to affect the parallel operation.

If you are dealing with a situation where parallel operations are suppressed:

What if you want to solve a problem like these? In fact, it is also very simple, we suggest that the query conditions after the function of the assignment to a variable, using variables to query as a query condition.

Again began a pleasant parallel, enjoying the pleasure of parallelism.

Similar to the function processing on the join condition, the result is computed, stored in a variable, and the variable is written in the join condition.

There may be other ways, of course, that I haven't thought of yet.

Summarize:

This article demonstrates the suppression of parallel operations by a simple example, illustrating the significant difference in performance of parallel and serial execution on a more expensive SQL

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

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

In order to make good use of parallelism, when writing SQL, we must pay attention to prevent parallel operations from being suppressed and to have an impact on performance.

SQL optimization is a difficult and iterative process, and even so, it is enjoyable.

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

The understanding of parallelism is not enough, there are wrong places to hope that you reader point out, thank you.

Described above is a small set of SQL Server parallel operation optimization to avoid parallel operation is suppressed and affect the execution efficiency of SQL, I hope to help everyone, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!