SQL Server Tuning Series basics (Parallel operations Summary chapter II)

Objective

In the previous article we described how to view the parallel running of a query plan.

In this article we then analyze the parallel operations of SQL Server.

Gossip less, directly into the topic of this article.

Technical preparation

As in the previous few, the SQL SERVER2008R2 version was used to parse a more concise case library (Northwind) from Microsoft.

Content

Before the article begins, let's review the parallel operations described in the previous article, and look at the parallel operation statements presented at the end of the article:

SELECT B1. [KEY],B1. Data,b2. DATA from BigTable B1 JOIN BigTable2 b2on B1. [KEY]=B2. [KEY] WHERE B1. data<100

The above is a detailed execution plan, from the right to the left, there is a very interesting place, is the data obtained after the clustered index scan, and re-used the task of re-allocation of the process

(repartition Streams), which is to reassign the 100 rows of data fetched to each thread in parallel.

In fact, this can be directly in the index scan 100 rows of data directly into the nested loop execution. It is here to reassign the task is to the back of the nested loop parallel execution, the maximum use of hardware resources!

But this is another drawback is that after the nested loop is executed, the results need to be rolled back, which is the following (Gather sreams) operator.

Let's take a look at that statement if the execution plan is not parallel

SELECT B1. [KEY],B1. Data,b2. DATA from BigTable B1 JOIN BigTable2 b2on B1. [KEY]=B2. [KEY] WHERE B1. Data<100option (MAXDOP 1)

This is an authentic serial execution plan.

Compared to the parallel execution plan above, you will find that SQL Server is not very handsome when it makes full use of hardware resources to form a parallel plan.

If you don't yet feel the charm of SQL Server parallel execution plan, let's take another example to see the following statement

SELECT Big_top. [Key],big_top. Data,b2. Datafrom (   SELECT TOP b.[key],b.data from   BigTable B   ORDER by DATA) Big_top,bigtable2 B2where big_top.[ Key]=b2. [KEY]

First to analyze the above statement, this statement we added the appearance of the top 100.....ORDER by the data keyword, the keyword is very interesting ....

Because we know that this statement is to get sorted according to the data keyword, and then get the meaning of the first 100 lines ...

1. Sort by data ..... Ya multi-threaded I see how you sort? Each thread arranges its own? Then you're all lined up and together ... Wouldn't that have to be re-ordered!!

2, get the first 100 rows of data, ya multithreading how to get? What if I had 4 threads scanning each thread for 25 data? What's the result of this?

3, our goal is to let the appearance and the above 100 rows of data also parallel nested loop connection, because this can make full use of resources, how does this happen?

Above these questions, let's see how powerful SQL Server will be for us to generate robust execution plans

The above implementation plan has solved the three problems we have described above, and in turn we analyze the solutions to these problems

The first question, about the juxtaposition sort problem

Preferred to fetch data from a table in a parallel manner based on a clustered index scan

Then, in parallel, based on the data in each thread, to get the first few columns of values, we know that our goal is to get the first 100 rows, and the way it gets here is redundant, which means each thread sorts its own data

Then we get the previous data and exchange it in a round robin way to get some data.

Second question, about getting the first 100 rows of data in parallel

We know that in order to get the first 100 rows of data, we have to summarize the data of each thread together and then get the first 100 rows of data by comparing it, so in this step SQL Server re-aggregates the data together

The third problem, the next step is to connect the 100 rows of data and appearances, to obtain the results, the use of nested loops in the way, in order to make full use of resources, improve performance, SQL Server has to divide the 100 rows of data into the various threads to execute, So here again, a split-task operator distribution flow (distribute Sreams) task

So after this step, the hardware resources of the system are fully utilized, and then the next step is to talk about the nested loop to get the results, then re-summarize the results, and then output

We can see a process above, SQL Server after: Split (parallel scan)--"and then parallel (get top 100 ...)--" and then split (for parallel nesting loops)--"and then parallel (in order to merge the results)

In short, when the SQL Server runs the statement, after various evaluations, the use of various split, various summaries, the purpose is to fully utilize the hardware resources, to achieve a performance optimization of the way! This is the essence of SQL Server parallel operations.

Of course, there are pros and cons, we use this statement to compare the advantages and disadvantages of serial and parallel in SQL Server

is a serial execution plan:

SELECT Big_top. [Key],big_top. Data,b2. Datafrom (   SELECT TOP b.[key],b.data from   BigTable B   ORDER by DATA) Big_top,bigtable2 B2where big_top.[ Key]=b2. [Key]option (MAXDOP 1)

Execution plan for serial execution: Simple, atmospheric, without complex various splits, various summaries and parallel.

To compare the differences between the two, compare each parameter value of a T-SQL statement first:

The former is serial, the latter is parallel

Serial compilation Consumes Cpu:2, parallel compilation consumes Cpu:10

Serial compilation Consumes Memory: 184, parallel compilation consumes memory: 208

Serial compilation Time: 2, parallel compilation time: 81

Above is the disadvantage of taking parallel: 1, more CPU, 2, compile more memory, 3, compile time longer

Let's take a look at the advantages of parallelism:

In serial memory usage (1024), parallel memory (448)

The advantage is that parallel execution consumes less memory

Of course there is one more important advantage: faster execution!

With parallel execution, the execution time is increased from 218 milliseconds to 187 milliseconds! Small amount of data, my machine performance is poor, so the elevation is not obvious!

During the execution of parallel operations, there is also an operator that often encounters the bitmap operator, which we will introduce in passing

As an example:

SELECT B1. [KEY],B1. Data,b2. [KEY] From BigTable B1 JOIN BigTable2 b2on B1. Data=b2. Datawhere B1. [key]<10000

Here we get the data of the key column in the large table less than 10000 rows.

The execution of the above statement, the introduction of bitmap calculation.

In fact, the goal of bitmap computing is very simple: pre-filtering , because our statement requires the result item to compare more than 10000 rows of data, in the thread behind us to take the parallel scan of the way to obtain the data. Due to the large amount of data, the various threads in the process of the completion of data acquisition time is different, in order to avoid the slow execution of a thread, resulting in overall congestion, the index introduced a bitmap operation, the first obtained partial results filtered output to the previous hash match, complete execution.

For more detailed reference to bitmap operators: http://msdn.microsoft.com/zh-cn/library/bb510541

Conclusion

This article first of all, this is a continuation of the previous parallel operation, two articles about the principle of parallel operation in SQL Server and how to use it, about parallel operation This is it, the next we add to SQL Server on the use of the index and dynamic index content, About indexes I believe that many people who understand database products are familiar with it, but the way in which some statements in SQL Server take advantage of the index may not be clear, our next analysis of this piece, to understand the indexing method and optimization techniques, interested in advance attention, about SQL Server performance tuning involves a wide range of content, followed by an analysis in the next article.

If you have any questions, you can leave a message or private messages, and look forward to an in-depth study of your child's shoes with SQL Server. Learn together and progress together.

At the end of the article, we give a link to the previous article

SQL Server Tuning Series Basics

SQL Server Tuning Series Basics (Summary of common operators)

SQL Server Tuning Series Basics (Union operator summary)

SQL Server Tuning Series basics (Parallel operations Summary)

SQL Server Tuning Series basics (Parallel operations Summary chapter II)