30

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?

alt text

Thanx for listening!

Benny Skogberg
  • 10,431
  • 11
  • 53
  • 83

2 Answers2

55

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).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    Thanx Marc! That's just what I needed to know. So, the larger the data rows, the more important it is to know how many bytes it contains - to ensure well used space on SQL Server. Great answer! – Benny Skogberg Jan 11 '11 at 13:14
  • @marc_s you said : _you want to try to avoid large wasted blobs of space._ HOw can I minimize those wasted blobs ? ( sounds like defrag ALIKE command) – Royi Namir Mar 22 '13 at 15:17
  • @RoyiNamir: sorry, using `blob` here was a bad choice of words. I just meant: with an 8K page, you want to avoid having a row of 4100 bytes - that would mean, the other 3960 bytes would be wasted and cannot be used by anything. Try to get the row size down to under 4030 bytes - then you'll be able to fit two records on a single page and fill up the 8060 bytes much better than with just a single 4100 byte record. – marc_s Mar 22 '13 at 15:18
  • @marc_s Thanks for reply. But if i look at my table , how can I know the size of each row ? I know the `datalength()` command but this is for a column only.( do you mean to to summarize (all datalength()) ? - or I'm completly wrong....:-) – Royi Namir Mar 22 '13 at 16:01
  • @RoyiNamir: that's like a whole question in itself :-) See [this question & answer](http://stackoverflow.com/questions/7279211/sql-server-2005-reaching-table-row-size-limit) for starters – marc_s Mar 22 '13 at 18:23
  • 1
    @marc_s - thanks. What is the reason for storing it as a fixed size page ? Because of that, I am forced to optimize my row size. Why can't we have row structure instead of page and just chain the rows to each other, like an array list of sorts ? When do I start worrying about page related performance issues ? Can you please give me some examples ? – Erran Morad Jun 09 '14 at 02:06
  • It's 2016 now. Maybe you @marc_s now know why pages? :) – Artem Novikov Sep 09 '16 at 21:27
  • @marc_s I.e. why use pages in the first place? As Borat asked, why not just store a sequence of rows. – Artem Novikov Sep 09 '16 at 21:38
  • @ArtemNovikov That's a design choice. A data file (.mdf or .ndf) that is used by SQL Server is logically divided into pages and all of the disk I/Os work in pages. And there are extents which are basically formed by 8 pages. Now, if a record takes more than 8060 bytes, it is moved to ROW_OVERFLOW_DATA allocation unit BUT it is moved back if the size of it reduces in the future. If a record takes more than 8060, let it be imo because your bottleneck is not related to this. And note that max length limits are not the actual size of the row. – yakya Aug 11 '17 at 07:38
18

You do not need to worry about 8 KB page structure until you get in to performance issues. But in case you want to know internals of 8 KB page , here is a video of youtube http://www.youtube.com/watch?v=He8MRttysmY which demonstrates how 8 KB pages looks like.

enter image description here

1.Page header stores information about the page like page type, next and previous page if it’s an index page, free space in the page etc.

2.After the page header data row section follows. This is where you data is actually stored.

3.Row offset information is stored at the end of the page i.e. after the data row section. Every data row has a row offset and the size of row offset is 2 bytes per row. Row offset stores information about how far the row is from the start of the page.

Putting in simple words the complete page equation comes as shown below.

Page (8 KB/8192 bytes) = Page header (96 bytes) + Actual data (Whatever bytes) + Row offset (2 bytes per row).

Shivprasad Koirala
  • 27,644
  • 7
  • 84
  • 73
  • When do I start worrying about page related performance issues ? Can you please give me some examples ? – Erran Morad Jun 09 '14 at 02:08
  • 4
    -1 b/c of "You do not need to worry about 8 KB page structure until you get in to performance issues". I am currently trying to fix a production sql server with a table containing 173 million rows. Performance (and thus page size) should be considered at design time. – ray Oct 01 '14 at 21:57
  • 2
    Although this answer is informative, I don't believe it is smart to just ignore page size till a problem arises. – Musselman Feb 01 '17 at 18:54