SQL Server parallel operation optimization prevents parallel operations from being restrained and affects SQL Execution efficiency and SQL Execution Efficiency

SQL Server parallel operation optimization prevents parallel operations from being restrained and affects SQL Execution efficiency and SQL Execution Efficiency

Why should I also say SQL Server Parallelism:

I have written many articles about SQL Server parallelism in the garden over the past few days. Whatever the case, it gives people a deeper understanding of parallel operations.

What I want to say is: although parallel operations may (not necessarily) have one or another problem, we cannot deny that parallel operations should still be well utilized.

However, in actual development, some SQL statement writing methods will not be used in parallel, thus affecting the SQL Execution efficiency.

Therefore, what we want to express in this article is: we should make good use of parallelism, and do not let some SQL writing problems "Suppress" parallelism, so that we cannot enjoy the pleasure of parallelism.

SQL Server Parallelism:

The so-called parallel processing refers to the SQL Server for those SQL statements with relatively high execution costs (this is related to your settings), if the database Server has multiple CPUs, the SQL Server query engine uses the parallel method, that is, multiple CPUs are used in the entire computing process, and each CPU "shares" a part of the computing tasks, finally, it is a kind of behavior to merge computing of various CPUs. Sometimes, improper parallel queries will not accelerate the query speed, but will slow down the query efficiency. If improper parallel operations are used, it may even affect the stability of the entire server.

So at what cost does SQL Server enable parallelism? This configuration can be modified according to the specific situation. Some people say that the unit of this value is "seconds ", it seems that I have never seen any authoritative information about what the organization is.

Thank you for your understanding of the park friendship of this threshold unit.

Although parallel operations may be like this, we can't take advantage of parallel operations because of the disadvantages.

However, not all SQL statements with a high execution cost can use parallel operations. In actual development, some SQL writing methods will suppress parallel operations and results, resulting in the entire SQL statement (Stored Procedure) efficiency.

The following is an example.

How does parallel query become serial:

The following is a very simple query operation. By default, parallel statements are enabled. We can see that a total of eight threads are enabled to compute SQL statements.

Of course, the SQL Execution efficiency is quite good. The CPU time is 622 milliseconds, and the total execution time is 130 milliseconds,

Do not confuse it here. The CPU time is 633 milliseconds, which is the total CPU time consumed by eight CPUs. It is normal to execute more than 130 milliseconds.

Create a very simple function,

CREATE function [dbo].[fn_justFunction](@p_date date)returns dateasbeginreturn @p_dateend

This function has no practical significance. It is also very simple to execute. It is passed in a time and returns this time,

Of course, this is just for the following Operation demonstration. You can say that I am a pain point. I just want to demonstrate the phenomenon of parallel suppression.

Is there a similar way of writing your SQL code?

Then we write this query so that we can process CreateDate> dbo in the query condition. fn_justFunction ('2014-1-1 ') (note that the function is not a table column but a query condition). Note that this function does not affect any query results. The input value is 2015, the returned bits are still, but with this change, the parallel processing becomes serial. During SQL Execution, only one CPU crashes, and the number reaches about 80% ,, at the same time, the other CPUs are the same as nobody else, and don't come up with help. I am still very idle. Do you still remember the parallel operation execution time above? 130 milliseconds. What does the rough look like now? Here is 4 seconds, that is, 4000 milliseconds. How many times is the difference? I cannot calculate it if I am not good at mathematics.

We can see that the efficiency of parallel operations and serial operations is still very different, and the CPU utilization is not sufficient (of course, I do not emphasize that it is reasonable to use all the CPUs)

Once again, this does not mean adding a function to the table's fields to suppress the index or something, but purely affects parallel operations.

Of course, writing a statement to suppress parallelism is not just about using functions in the query conditions, but will have a greater impact in actual development,

Because the actual business data may be larger, and SQL may be more complex, this situation may be more difficult to identify.

For example, in the connection condition, the following is the case where the concurrent connection condition cannot be used due to the use of functions, which is also encountered in actual development.

select * from TableA a inner join TableB b on a.id=b.id and a.Column=dbo.function(@Variable) where ***

Of course, it is not only these two types of writing that can suppress parallel operations, but other similar writing may also affect parallel queries.

This requires that when writing SQL statements, we should not only pay attention to the inability to use functions on the field (the index on the field cannot be used), but also try not to use functions on the query conditions, it may affect parallel operations.

If the processing of parallel operations is restrained:

What should I do if I want to solve these problems? In fact, it is also very simple. We recommend that you assign a value to a variable after a function operation for the query condition.

We started a pleasant parallel operation and enjoyed the pleasure of parallel operation.

The function processing on the connection condition is similar. After the result is calculated, it is saved in a variable and written into the connection condition,

Of course there may be other methods, which I haven't thought of yet.

Summary:

This article demonstrates the suppression of parallel operations through a simple example, and illustrates the huge difference in the performance of parallel and serial SQL Execution at a high cost.

The query method mentioned here is that the parallel query is restrained due to function reasons, which is completely different from the case where the function is used to suppress the index on the query column.

Parallel query can fully mobilize CPU resources to complete queries in an efficient manner, and reasonably utilize parallel queries to greatly improve SQL Execution efficiency.

In order to make good use of parallelism, you must pay attention to it when writing SQL statements to prevent parallel operations from being restrained and affecting the performance.

SQL optimization is a difficult and repetitive process. Even so, it is a pleasure.

In the face of complicated SQL statements, we must not only have excellent technologies, but also have enough patience to see the essence of things.

We do not have enough understanding of parallelism. If anything is wrong, I hope you can point it out. Thank you.

The preceding section describes the optimization of SQL Server parallel operations to avoid the impact of parallel operations on SQL Execution efficiency, if you have any questions, please leave a message and the editor will reply to you in time. Thank you very much for your support for the help House website!