This is a half-baked draft for two reasons.
First, I got distracted by an unexpected finding. At lower number of rows such as 1023 (for values 1 to 11), the table I created below had 1023 data pages, or 2^10 - 1 (the expected number, 1 data page per row). But at 65535 total rows, the table seems to had 65527 data pages according to sys.dm_db_database_page_allocations. I'm not sure why 8 fewer data pages than expected were reported. I'll investigate in another blog post and link here. Already found some interesting stuff while investigating and have a theory. 😁
Second... well, while I'm sharing the table I'll use for investigation... uhhhh... I'm still working on the queries. When I've got test queries with the type of behavior I'm looking for I'll include them here, and when I've got interesting findings posted I'll change the status of this post to "fully baked."
The SQL below will create a table with 2^(n-1) rows with num value n, for each integer n between 1 and 16. The total number of rows in the table is 2^15 - 1 or 65535.
The code isn't elegant or necessarily very efficient. It took 8 minutes and 11 seconds to complete on the test vm. But a table with this structure should be very valuable in experiments to learn about how hash distribution of rows among parallel threads works. Will also hopefully lead to leaning about how the parallel page supplier works.
Joe Obbish read my code above and had some comments:
- that code is so confusing to read
- if change it like so it'll finish in 2 seconds
- still not the way I'd probably do it if starting from scratch
Thanks, Joe!! Sure enough, tried his code and it finished in 2 seconds. So I increased the expected total number of rows from 65535 to 131071 which is where I wanted to be anyway. That took four seconds. To create 131071 rows my code would have taken... a *lot* longer. 😁
Here's what this looks like with Joe's help.
Much faster, much nicer. So here's the idea: since each integer value n 1 to 17 is represented in the table with 2^n rows, when rows are distributed to parallel threads by hash the number of rows for the thread will tell me which values each thread got, too.
Now, this should also allow me to make observations about the parallel page supplier. The threads of a parallel scan get their page sets from the parallel page supplier... but there's not much info beyond that publicly available. A few reliable test cases (which I haven't invented yet) should help me to see whether the page sets for a clustered index scan are determined based on index key values. Should also be able to suss out whether the page sets given to threads are affected by table stats or strictly by "whoever asks next gets the next set I'm ready to hand out."
Later I thought "hey, I'll save myself some thought if I do this in base 10 rather than base 2." And I wanted to see how Joe Obbish would have approached creating this type of table. I asked, and he taught me a new trick (which I'll likely have to keep coming back to here otherwise I won't remember how it worked).
This isn't exactly how Joe did it, but its as close as I get from memory.
This query show how it works.
So here's how it comes together to make my table for exploring.