question

Senn-4458 avatar image
0 Votes"
Senn-4458 asked Criszhan-msft answered

How many sorted subfiles will there be after the initial pass of the sort, and how long will each subfile be?

Hi everyone,

I am just not sure where they get 10 records? Do you have an idea?

Suppose that you just finished inserting several records into a heap file and now want to sort those records. Assume that the DBMS uses external sort and makes efficient use of the available buffer space when it sorts a file. Here is some potentially useful information about the newly loaded file and the DBMS software available to operate on it:
The number of records in the file is 4500. The sort key for the file is 4 bytes long. You can assume that rids are 8 bytes long and page ids are 4 bytes long. Each record is a total of 48 bytes long. The page size is 512 bytes. Each page has 12 bytes of control information on it. Four buffer pages are available.
1. How many sorted subfiles will there be after the initial pass of the sort, and how long will each subfile be?

Assuming that the general external merge-sort algorithm is used, and that the available space for storing records in each page is 512−12 = 500 bytes, each page can store up to 10 records of 48 bytes each. So, 450 pages are needed in order to store all 4500 records, assuming that a record is not allowed to span more than one page. Given that 4 buffer pages are available, there will be 450/4 = 113 sorted runs (sub-files) of 4 pages each, except the last run, which is only 2 pages long.




sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

So what on Earth is this course or class you are taking? All I can say in the perspective of SQL Server is that this is an internals question, that I may scratch my head over if I have some critical situation where I need to know, but most of the times I just couldn't be bothered.

0 Votes 0 ·

1 Answer

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

Hi,

I am just not sure where they get 10 records? Do you have an idea?

According to the information you show:

Assuming that the general external merge-sort algorithm is used, and that the available space for storing records in each page is 512−12 = 500 bytes, each page can store up to 10 records of 48 bytes each. So, 450 pages are needed in order to store all 4500 records, assuming that a record is not allowed to span more than one page.

Because it is assumed that a record cannot span more than one page, each record is 48 bytes, then for a page (512−12 = 500 bytes), so a page can store at most 500/48=10. The extra 20 bytes is not enough store a record.


In SQL Server, A buffer is an 8-KB page in memory, the same size as a data or index page. Thus, the buffer cache is divided into 8-KB pages.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.