1

I know that a database management system like MySQL can make use of multiple storage engines - i.e. InnoDB, MyISAM, etc.

And reading into how SQL Server stores data (Understanding Pages and Extents), I can summarize (hopefully correctly):

  1. SQL Server data storage consists of 'pages' (the smallest unit of data that SQL Server handles),
  2. 8 contiguous pages makes a structure called an 'Extent' (SQL Server accesses an Extent per read according to this article)
  3. Extents can either be dedicated to a single object or shared by as many as 8 objects (a page can only belong to a single object). Uniform extents are preferred and mixed extents are refactored to be uniform when an object becomes large enough.
  4. Objects include pretty much every everything in SQL Server (table, types, functions, indexes, etc. etc.)
  5. An extent == 8 pages = 64KB == 64 000 bytes == 512 000 bits = 2 extents per MB
  6. Log files do not use pages / extents

Questions:

  1. Does SQL Server write to disk an extent at a time? (since an extent is the smallest 'read' unit)?
  2. Are SQL Server metaphors such as 'pages', 'objects' and 'extents' applicable to other DB engines.
  3. Alternatively, are pages and extents at a different abstraction level to talking about 'engines' such as InnoDB and MyISAM? If so, would these engines use something other than blocks of 8 * 8KB storage units (what I surmise pages and extents to be)?

Also, why is Google's conversion of KB to bytes different to MS? i.e. Google converts 8KB to 8000 bytes, MS converts 8KB to 8192 bytes.

  • An page is the smallest unit for both reads and writes. During scans, SQL server can read full extents (one or more) to reduce IO whereas singe-page reads are typically done for singleton row seeks. Writes done by the lazywriter and checkpoint processes in sets of pages, contiguous when possible. See technet.microsoft.com/en-us/library/aa337560.aspx. – Dan Guzman Jul 20 '17 at 11:31
  • 1
    "Pages" is pretty much universal in the RDBMS world I think. Oracle also uses the terminology of "extents" although they are managed quite differently, especially when ASM storage comes into play. Postgres uses a very different file system layout (many smaller files instead of a few very big files). They use the term "segment" when extending the storage for a single table instead of "extent". – a_horse_with_no_name Jul 20 '17 at 11:58
  • 1
    8KiB = 8000 bytes. 8KB == 8192 bytes: en.wikipedia.org/wiki/Kibibyte – a_horse_with_no_name Jul 20 '17 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.