Unit of data in Buffer Pool

August 8, 2017 Internals 1003 Views

/_include/blog-images/article-images/buffer-pool-unit-of-data.jpg

SQL Server updates 8KB of data when you update one single bit of data.



What is unit of data in Buffer Pool

The unit of data in Buffer Pool is Page. It means that the minimum amount of data that SQL Server reads from/to Buffer Pool is one Page. Knowing that every page is 8KB, it can be said that the minimum amount of data that is read from/to Buffer Pool is 8KB. If you don't know what Page is, read my post about Pages and Extents


What implications can it have?

There is an intersting point on the unit of data in Buffer Pool. Any change you make in data results in the relevant whole page be persisted back on disc.

Let's see an example. Imagine the query below which updates the Discontinued field of the People table in the Northwind database. The field is of type BIT, therefore only one single bit of data is being modified.

UPDATE dbo . People
SET Discontinued = 0
WHERE ProductId = 8
Although the query is updating one single bit of data, SQL Server modifies the relevant Page and the whole Page of data is written back to disc . So, as a result of one single bit of change in data, 8KB is updated by SQL Server. This weird behaviour is because the unit of data in Buffer Pool is Page.

How do you rate this topic?


Further reading:
SQL Server Pages and Extents
Pages in Buffer Pool fall in to two different types.......
Some SQL functions are constants
Why does SQL Server consume so much memory?