Menu
I've come across the page in SQL Server 2008, and is somewhat confused about it. I'm currently reading MCTS Self-Paced Training Kit (Exam 70-433): Microsoft SQL Server 2008-Database Development, where authors discuss the concept, but in a limited way.
From MSDN "Understanding Pages and Extents" I get an answer which doesn't really help. The web-page describes the size (8kb) of a page and how rows are stored in the page, and how columns will be moved automatically (by SQL Server) if the rows doesn't fit in the page.
But still, I wonder if the page-level is something I should pay attention to designing a database with ER-diagrams, tables and data types? Or, should I simply rely on that SQL Server handles pages automatically and in the best way possible?
Thanx for listening!
602
The page is the fundamental unit of data storage in SQL Server. An extent is a collection of eight physically contiguous pages. Extents help efficiently manage pages. This guide describes the data structures that are used to manage pages and extents in all versions of SQL Server.
A page is a unit of storage whose size is configurable on a system-wide, database-wide, or conglomerate-specific basis. By default, a conglomerate grows one page at a time until eight pages of user data (or nine pages of total disk use, which includes one page of internal information) have been allocated.
This is called low page density (or internal) fragmentation. Sparsely populated data pages increase cache size because caching is done at the page level.
Page count is the number of pages that the data in the table takes up, each page is 8kb. The only reliable way to decrease that is to delete data. Don't fuss over indexes with 3 pages, they won't defrag. The general recommendation is to worry about fragmentation once a table is over 1000 pages or so.
YES ! A page is the most basic element of storage in SQL Server.
Of the 8192 bytes on a page, approx. 8060 are available to you as a user. If you can manage to fit your data rows onto the page nicely, they'll take up a lot less storage.
If your data row e.g. is 4100 bytes long, only a single row will be stored on a page (and the rest of the page - 3960 bytes - is wasted space). The important point is: those pages aren't just relevant on disk, but also in SQL Server main memory --> you want to try to avoid large areas of space that cannot hold any useful information on a page.
If you can manage to reduce your row to take up 4000 bytes, then suddenly you can store two rows on a page, and thus significantly reduce the overhead of wasted space (down to 60 bytes per page).
95
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
