1

Microsoft documentation on the allocation of pages and extents includes the following sentence:

The SQL Server Database Engine allocates a new extent to an allocation unit only when it cannot quickly find a page in an existing extent with sufficient space to hold the row being inserted.

I was trying to find more concrete information about this behavior, specifically:

  1. What does "quickly" means in this context? Is this wall time? Is it the number of pages checked?
  2. What are some scenarios in which this can happen?

In general, does this mean that in some scenarios my tables might keep growing even if there are pages with enough free space?

Improve this question
1
  • What version of SQL Server you are referring you to? The article you referred to is version-specific.
    – SqlWorldWide
    Oct 22, 2021 at 11:24

2 Answers 2

Reset to default
1

when it cannot quickly find a page in an existing extent with sufficient space to hold the row being inserted.

What are some scenarios in which this can happen?

This behavior only applies to heaps. For BTrees, including Clustered Indexes, the target page is forced by the index sort order.

Improve this answer
3
  • My initial question was triggered by a behavior seen for a heap. The size of the heap is 30GB with ~50M rows. Data is accessed using 3 types of transactions: A, B, and C. A first interest a small row, than updates it to a larger size. B updates a row back to the initial size. Finally, C removes the row altogether. There is a constant load of A, B, and C. What I've seen is that the heap size would grow while there would be empty pages newer being reused. Could that be one of these scenarios?
    – Tomek Masternak
    Oct 23, 2021 at 15:39
  • The update will leave a forwarding pointer on the original page and and after the delete there’s no guarantee the space will be reused.
    – David Browne - Microsoft
    Oct 23, 2021 at 19:29
  • When checking forwarded_records_count withsys.dm_db_index_physical_stats I haven't seen that many - tens at most.
    – Tomek Masternak
    Oct 24, 2021 at 19:37
0

This is a very hard core example of "it depends" to get to anything approaching a correct answer. So, let's say you're inserting data. It's a clustered index. SQL Server will go to the extend where the key indicates that the data should go. If there is room, it adds the data. If not, you get new allocations. Simple.

Now, let's assume the same scenario, but it's an update. It goes to the key, looks at the page and the data, there's no room. New allocations.

Quickly simply means, it's right there. If you have pages with really odd fragmentation where, with a lot of work, data could be added to the page, in little chunks all over the place... No, that's not what happens. It needs to have contiguous space to write. If not, it goes and gets more.

Each scenario is going to be different based on the type of operation, the indexes involved, the size & amount of data, the type of data, distribution of the data across the page, in short, the details matter. However, the general behavior is very much exactly as defined. If there's room, it uses it. If there's not, it doesn't.

Improve this answer

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.