1

I remember that I once had a missing index on a table and one query was taking ages. In the execution plan I saw that a table scan and a merge was done. Statistics from IO showed me that every core did a table scan, or better that I got 6 table scans. Now I wonder if every core does a full scan, or does every core roughly do 1/6 table scan? I know for sure that if I had a proper index, I either just get a seek or it would be split for every core.

I hope you understand what I ment here, unfortunately I can't provide any information, since the question just came up and my problem was solved years ago.

4

To be upfront with you, I'm not exactly sure what you mean by "a merge was done". Are you talking about a merge join? Perhaps you mean a parallelism operator? At the very least I can answer the question about parallel table scans.

Statistics from IO showed me that every core did a table scan, or better that I got 6 table scans.

I assume what you mean by this is that you ran SET STATISTICS IO ON before running your query and part of the output included something like this:

Table 'your_table'. Scan count 6, ...

The label "scan count" is a bit misleading. You should not conclude that if STATISTICS IO reported 6 scans that all of the rows in the table were scanned 6 times. Considering the following simple example query on a heap table called heap_table:

SELECT TOP 1 *
FROM heap_table
OPTION (MAXDOP 1);

For that query STATISTICS IO should report a scan count of 1, right? But SQL Server clearly didn't need to read all of the rows in the table. Looking at the definition for the scan count label is also helpful:

Number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output.

...

Scan count is N when N is the number of different seek/scan started towards the left or right side at the leaf level after locating a key value using the index key.

So if your query did a parallel scan I would expect to see a scan count of at least 6, but that doesn't necessarily imply that all of the rows from the table were read six times. How can you tell how the rows were distributed amongst your CPU cores?

The easiest way is to just look at an actual execution plan. If you look at the details for a parallel scan SQL Server will show you how many rows were processed by each CPU thread. Below is a picture of what you might see borrowed from Paul White's article Parallel Execution Plans – Branches and Threads:

thread row count example

As you said you encountered this query years ago so that method won't help you. Instead we need to look at the techniques available to SQL Server for parallel plan processing. Craig Freedman has a series of blog posts on the subject. From the Parallel Scan article:

How does parallel scan work?

The threads that compose a parallel scan work together to scan all of the rows in a table. There is no a-priori assignment or rows or pages to a particular thread. Instead, the storage engine dynamically hands out pages to threads. A parallel page supplier coordinates access to the pages of the table. The parallel page supplier ensures that each page is assigned to exactly one thread and, thus, is processed exactly once.

Well, there you have it. Like I said earlier you can easily test this by running a query with a parallel scan and checking out the details for the parallel scan operator in the actual execution plan.

For another way to look at it, try thinking of a scenario in which it's beneficial for SQL Server to do a full table scan per core.

Suppose that you just so happened to have a UNION ALL query that referenced your table six times. In principle, SQL Server could do each table scan with one core independently and combine the results at the end. However, SQL Server will not do this because it will not do pipeline parallelism. Even if it could, I personally can't think of an advantage of doing that here, other than avoiding some of the overhead associated with parallelism.

You could read about the broadcast type of parallel execution and wonder if in that scenario SQL Server could do six complete scans of a table, one with each core. For the broadcast type of exchange SQL Server sends all rows to all consumer threads. However, this can be accomplished with a serial scan on the table followed by a Distribute Streams type of parallel exchange. Indeed, that is what you see in the hash join example. I can't really think of a benefit to doing that scan in parallel, especially when the broadcast type is only used for relatively small tables.

One case in which I suppose this could happen is if you had a parallel nested loop join with an outer table that contained 6 rows and with a table scan on the inner side of the join. In that case, I believe that the table scans will be completed by independent serial threads, so effectively you would get each core doing its own table scan. Of course, such a query probably performs very poorly and is not something to aim for, especially when the outer table has more than six rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.