Oracle: Upgrade Methods

The different upgrade methods you can use to upgrade your database to the new Oracle Database 9i release or higher are: 

1) Database Upgrade Assistant (DBUA)
2) Manual Upgrade
3) Export/Import
4) Data Copying
5) Golden Gate

DBUA (Database Upgrade Assistant)

-   The DBUA automates the upgrade process by performing all of the tasks normally performed manually. 

-   The DBUA makes appropriate recommendations for configuration options such as tablespaces and redo logs.

-   This method is very easy and user friendly. But if any error occurs it will take time to diagnose the error as the upgrade process is done automatically by the upgrade assistant. 

Manual Upgrade

-   A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database release. 

-   A manual upgrade gives finer control over the upgrade process as it is done step by step manually. So if any error occurs, it is easier to diagnose the error. 

-   When manually upgrading a database, perform the following pre-upgrade steps:

• Analyze the database using the Pre-Upgrade Information Tool. The Upgrade Information Tool is a SQL script that ships with the new Oracle Database release, and must be run in the environment of the database being upgraded. 
• The Upgrade Information Tool displays warnings about possible upgrade issues with the database. It also displays information about required initialization parameters for the new Oracle Database release. 
• Prepare the new Oracle Home. 
• Perform a backup of the database.

Depending on the release of the database being upgraded, you may need to perform additional pre-upgrade steps (adjust the parameter file for the upgrade, remove obsolete initialization parameters and adjust initialization parameters that might cause upgrade problems). 


Note: 
-   DBUA can be used only if the source & target Oracle homes are on the same Server.
-   But manual upgrade will work even if the source and target Home are on different servers provided that the Hardware architecture and operating system on source and target Home are the same.


Example :
- You cannot manually upgrade a database from an AIX Operating System to Solaris Operating System.
- You cannot manually upgrade a database from a Solaris X86-64 Machine to Solaris SPARC 64-bit.




Export/Import:

-   The Export/Import upgrade method does not change the current database, which enables the database to remain available throughout the upgrade process. However, if a consistent snapshot of the database is required (for data integrity or other purposes), then the database must run in restricted mode or must otherwise be protected from changes during the export procedure. 

-   Most importantly, the Export/Import operation results in a completely new database. Although Export/Import creates an identical copy of the database, other factors, such as disk placement of data and unset tuning parameters, may cause unexpected performance problems. 

-   Upgrading an entire database by using Export/Import can take a long time, especially compared to using the DBUA or performing a manual upgrade. Therefore, you may need to schedule the upgrade during non-peak hours or make provisions for propagating to the new database any changes that are made to the current database during the upgrade. 

Data Copying:

-   You can copy data from one Oracle Database to another using database links. For example, you can create new tables and fill the tables with data by using the INSERT INTO statement and the CREATE TABLE ... AS statement. 

-   Copying data and Export/Import offer the same advantages for upgrading. Using either method, you can defragment data files and restructure the database by creating new tablespaces or modifying existing tables or tablespaces. In addition, you can copy only specified database objects or users. 

-   Copying data, however, unlike Export/Import, enables the selection of specific rows of tables to be placed into the new database. Copying data is a good method for copying only part of a database table. In contrast, using Export/Import, you can copy only entire tables 

GoldenGate:


-   Oracle GoldenGate is a comprehensive software package for enabling the replication of data in heterogeneous data environments.
-    The product set enables high availability solutions, real-time data integration, transactional change data capture, data replication, transformations, and verification between operational and analytical enterprise systems.

Source: Different Upgrade Methods For Upgrading Your Database [ID 419550.1]

SQL Server: Memory Architecture

-   SQL Server dynamically acquires and frees memory as required.

-   Typically, an administrator does not have to specify how much memory should be allocated to SQL Server, although the option still exists and is required in some environments.

-   SQL Server supports Address Windowing Extensions (AWE) allowing use of physical memory over 4 gigabytes (GB) on 32-bit versions of Microsoft Windows operating systems. Up to 64 GB of physical memory is supported. Instances of SQL Server that are running on Microsoft Windows 2000 use static AWE memory allocation, and instances that are running on Microsoft Windows Server 2003 use dynamic AWE memory allocation.

-   Support for AWE is available only in the SQL Server Enterprise, Standard, and Developer editions and only applies to 32-bit operating systems. Analysis Services cannot take advantage of AWE mapped memory. If the available physical memory is less than the user mode virtual address space, AWE cannot be enabled.


-   SQL Server builds a buffer pool in memory to hold pages read from the database. Much of the code in SQL Server is dedicated to minimizing the number of physical reads and writes between the disk and the buffer pool. SQL Server tries to reach a balance between two goals:

• Keep the buffer pool from becoming so big that the entire system is low on memory.
  
• Minimize physical I/O to the database files by maximizing the size of the buffer pool.

PostgreSQL Basics

1        What is PostgreSQL?

-        PostgreSQL is an object-relational DBMS

-        Free and the complete source code is available.

-        Developed by volunteer developers spread throughout the world and communicating via the Internet.

-        It is a community project and is not controlled by any company.

-        No central committee, or controlling company

-        Allows users to do anything they want with the code, including reselling binaries without the source code. The only restriction is that you not hold us legally liable for problems with the software. There is also the requirement that this copyright appear in all copies of the software.

-        It is fully ACID compliant

-        Has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages).

-        It includes most SQL: 2008 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP.

-        It also supports storage of binary large objects, including pictures, sounds, or video.

-        It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.

-        An enterprise class database, PostgreSQL boasts sophisticated features such as

o   Multi-Version Concurrency Control (MVCC),

o   point in time recovery, tablespaces,

o   asynchronous replication,

o   nested transactions (savepoints),

o   online/hot backups,

o   a sophisticated query planner/optimizer,

o   write ahead logging for fault tolerance.

-        It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting.

2       What platforms does PostgreSQL support?

-        It runs on all major operating systems, including

o   Linux

o   UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64)

o   Windows.

  

3        Where can I get support?

-        The PostgreSQL community provides assistance to many of its users via email. The main web site to subscribe to the email lists is http://www.postgresql.org/community/lists/.

4        How do I find out about known bugs or missing features?

-        PostgreSQL supports an extended subset of SQL:2008. See TODO list

( http://wiki.postgresql.org/wiki/Todo ) for known bugs, missing features, and future plans.

-        In practice, bugs don't last very long in the software, and bugs that affect a large number of users are fixed rapidly.

-        A feature request usually results in one of the following replies:

o   The feature is already on the TODO list

o   The feature is not desired because:

§  It duplicates existing functionality that already follows the SQL standard

§  The feature would increase code complexity but add little benefit

§  The feature would be insecure or unreliable

o   The new feature is added to the TODO list

5        What is Versioning policy?

-        Major release

-        Minor release

Major Release:  

o   include new features and occur roughly once every year

o   numbered by increasing either the first or second part of the version number, e.g. 8.2 to 8.3.

o   change the internal format of system tables and data files.

o   These changes are often complex, so we do not maintain backward compatibility of all stored data.

o   A dump/reload of the database or use of the pg_upgrade module is required for major upgrades.

o   may require changes to your app

Minor release:

o   numbered by increasing the third part of the version number, e.g. 8.1.5 to 8.1.6.

o   The PostgreSQL team only adds bug fixes to minor releases. All users should upgrade to the most recent minor release as soon as possible.

o   While upgrades always have some risk, PostgreSQL minor releases fix only frequently-encountered, security and data corruption bugs to reduce the risk of upgrading. The community considers not upgrading to be riskier than upgrading.

o   does not require a dump and restore; merely stop the database server, install the updated binaries, and restart the server.

o   For some releases, manual changes may be required to complete the upgrade, so always read the release notes before upgrading.

  

A bug I'm encountering is fixed in a newer minor release of PostgreSQL, but I don't want to   upgrade. Can I get a patch for just this issue?

-        NO. No need to do that.

7    How does PostgreSQL use CPU resources?

-        The PostgreSQL server is process-based (not threaded), and uses one operating system process per database session.

-        A single database session (connection) cannot utilize more than one CPU.

-        Of course, multiple sessions are automatically spread across all available CPUs by your operating system. Client applications can easily use threads and create multiple database connections from each thread.

-        A single complex and CPU-intensive query is unable to use more than one CPU to do the processing for the query. The OS may still be able to use others for disk I/O etc, but you won't see much benefit from more than one spare core.

Some general PostgreSQL limits are included in the table below.

Limit	Value
Maximum Database Size	Unlimited
Maximum Table Size	32 TB
Maximum Row Size	1.6 TB
Maximum Field Size	1 GB
Maximum Rows per Table	Unlimited
Maximum Columns per Table	250 - 1600 depending on column types
Maximum Indexes per Table	Unlimited

8       

           What replication solutions are available?

-        Master/slave replication allows a single master to receive read/write queries, while slaves can only accept read/SELECT queries. The most popular freely available master-slave PostgreSQL replication solution is Slony-I.

-        Multi-master replication allows read/write queries to be sent to multiple replicated computers. This capability also has a severe impact on performance due to the need to synchronize changes between servers. PGCluster is the most popular such solution freely available for PostgreSQL.

  

9          Is possible to create a shared-storage postgresql server cluster?

-        PostgreSQL does not support clustering using shared storage on a SAN, SCSI backplane, iSCSI volume, or other shared media. Such "RAC-style" clustering isn't supported. Only replication-based clustering is currently supported. 

SQL Server: Transaction Log Architecture

--------------------------------------- Logical Architecture --------------------------------------

-   Transaction log guarantees the data integrity of the database and data recovery.

-   Transaction log is a string of log records. Each log record is identified by a log sequence number (LSN).

-   Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it.
-   Log records are stored in a serial sequence as they are created. Each log record contains the ID of the transaction that it belongs to. For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.

-   Log records for data modifications record either the logical operation performed or they record the before and after images of the modified data. The before image is a copy of the data before the operation is performed; the after image is a copy of the data after the operation has been performed.

-   The steps to recover an operation depend on the type of log record:

• Logical operation logged
• To roll the logical operation forward, the operation is performed again.
• To roll the logical operation back, the reverse logical operation is performed.

• Before and after image logged
  
  • To roll the operation forward, the after image is applied.
    
  • To roll the operation back, the before image is applied.

Many types of operations are recorded in the transaction log. These operations include:

• The start and end of each transaction.
  
  
• Every data modification (insert, update, or delete). This includes changes by system stored procedures or data definition language (DDL) statements to any table, including system tables.
  
  
• Every extent and page allocation or deallocation.
  
  
• Creating or dropping a table or index.

Rollback operations are also logged. Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. The amount of space reserved depends on the operations performed in the transaction, but generally is equal to the amount of space used to log each operation. This reserved space is freed when the transaction is completed.
The section of the log file from the first log record that must be present for a successful database-wide rollback to the last-written log record is called the active part of the log, or the active log. This is the section of the log required to do a full recovery of the database. No part of the active log can ever be truncated.

----------------------------------------- Physical Architecture -------------------------------------

-   The transaction log is used to guarantee the data integrity of the database and for data recovery.

-   The transaction log in a database maps over one or more physical files. Conceptually, the log file is a string of log records. Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log.

-   The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files.  The size or number of virtual log files cannot be configured or set by administrators.

-   The only time virtual log files affect system performance is if the log files are defined by small size and growth_increment values. If these log files grow to a large size because of many small increments, they will have lots of virtual log files. This can slow down database startup and also log backup and restore operations. We recommend that you assign log files a size value close to the final size required, and also have a relatively large growth_increment value

-   The transaction log is a wrap-around file. For example, consider a database with one physical log file divided into four virtual log files. When the database is created, the logical log file begins at the start of the physical log file. New log records are added at the end of the logical log and expand toward the end of the physical log. Log truncation frees any virtual logs whose records all appear in front of the minimum recovery log sequence number (MinLSN). The MinLSN is the log sequence number of the oldest log record that is required for a successful database-wide rollback.

--------------------- Checkpoints and the Active Portion of the Log ----------------------

-   Checkpoints flush dirty data pages from the buffer cache of the current database to disk. This minimizes the active portion of the log that must be processed during a full recovery of a database. During a full recovery, the following types of actions are performed:

• The log records of modifications not flushed to disk before the system stopped are rolled forward.
  
• All modifications associated with incomplete transactions, such as transactions for which there is no COMMIT or ROLLBACK log record, are rolled back.
  

Checkpoint Operation

A checkpoint performs the following processes in the database:

• Writes a record to the log file, marking the start of the checkpoint.
  
• Stores information recorded for the checkpoint in a chain of checkpoint log records.
  
  One piece of information recorded in the checkpoint is the log sequence number (LSN) of the first log record that must be present for a successful database-wide rollback. This LSN is called the Minimum Recovery LSN (MinLSN).

• If the database uses the simple recovery model, marks for reuse the space that precedes the MinLSN. 
• Writes all dirty log and data pages to disk.
  
• Writes a record marking the end of the checkpoint to the log file.
  
• Writes the LSN of the start of this chain to the database boot page.

The checkpoint records also contain a list of all the active transactions that have modified the database.

Activities That Cause a Checkpoint

Checkpoints occur in the following situations:

• A CHECKPOINT statement is explicitly executed. A checkpoint occurs in the current database for the connection.
  
  
• A minimally logged operation is performed in the database; for example, a bulk-copy operation is performed on a database that is using the Bulk-Logged recovery model.
  
  
• Database files have been added or removed by using ALTER DATABASE.
  
  
• An instance of SQL Server is stopped by a SHUTDOWN statement or by stopping the SQL Server (MSSQLSERVER) service. Either action causes a checkpoint in each database in the instance of SQL Server.
  
  
• An instance of SQL Server periodically generates automatic checkpoints in each database to reduce the time that the instance would take to recover the database.
  
  
• A database backup is taken.
  
  
• An activity requiring a database shutdown is performed. For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.

------------------------------ Write-Ahead Transaction Log ------------------------------------

-   SQL Server uses a write-ahead log (WAL), which guarantees that no data modifications are written to disk before the associated log record is written to disk. This maintains the ACID properties for a transaction.

-   SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification must be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.


-   At the time a modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.

SQL Server: Snapshot Database

-   A database snapshot provides a read-only, static view of a source database as it existed at snapshot creation, minus any uncommitted transactions. Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created (transactions in the database are not affected).

-   Database snapshots are dependent on the source database.

-   The snapshots of a database must be on the same server instance as the database. Furthermore, if that database becomes unavailable for any reason, all of its database snapshots also become unavailable.

-   Snapshots can be used for reporting purposes.

-   Also, in the event of a user error on a source database, you can revert the source database to the state it was in when the snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.

-   Also, creating a database snapshot can be useful immediately before making a major change to a database, such as changing the schema or the structure of a table.


-   Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page do not affect the contents of the snapshot. The same process is repeated for every page that is being modified for the first time. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken.

-   To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file.


-   Because database snapshots are not redundant storage, they do not protect against disk errors or other types of corruption. Taking regular backups and testing your restore plan are essential to protect a database. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.

-   If your source database is fairly large and you are concerned about disk space usage, at some point you should replace an old snapshot with a new snapshot. The ideal lifespan of a snapshot depends on its growth rate and the disk space that is available to its sparse files. 

SQL Server: File and Filegroups Architecture

-   SQL Server maps a database over a set of operating-system files.
-   Data and log information are never mixed in the same file, and individual files are used only by one database.
-   Filegroups are named collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.

---------------------------------------- Database Files ----------------------------------------------

SQL Server databases have three types of files:

Primary data files 

-   The primary data file is the starting point of the database and points to the other files in the database. 

-   Every database has one primary data file. The recommended file name extension is .mdf. 

Secondary data files 

-   Secondary data files make up all the data files, other than the primary data file. 

-   Some databases may not have any secondary data files, while others have several secondary data files. 

-   The recommended file name extension for secondary data files is .ndf. 

Log files

-   Log files hold all the log information that is used to recover the database. 

-   There must be at least one log file for each database, although there can be more than one. 

-   The recommended file name extension for log files is .ldf. 

--------------------------------Logical and Physical File Names----------------------------

SQL Server files have two names:

logical_file_name

-   The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. 

-   The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

os_file_name

-   The os_file_name is the name of the physical file including the directory path. 

-   It must follow the rules for the operating system file names.

SQL Server data and log files can be put on either FAT or NTFS file systems. We recommend using the NTFS file system because the security aspects of NTFS. Read/write data filegroups and log files cannot be placed on an NTFS compressed file system. Only read-only databases and read-only secondary filegroups can be put on an NTFS compressed file system.

--------------------------------------------- File Size ------------------------------------------------

-   SQL Server files can grow automatically from their originally specified size. 

-   When you define a file, you can specify a specific growth increment. Every time the file is filled, it increases its size by the growth increment. If there are multiple files in a filegroup, they will not autogrow until all the files are full. Growth then occurs in a round-robin fashion.

-   Each file can also have a maximum size specified. If a maximum size is not specified, the file can continue to grow until it has used all available space on the disk. 

Database Snapshot Files

The form of file that is used by a database snapshot to store its copy-on-write data depends on whether the snapshot is created by a user or used internally:

• A database snapshot that is created by a user stores its data in one or more sparse files. Sparse file technology is a feature of the NTFS file system. At first, a sparse file contains no user data, and disk space for user data has not been allocated to the sparse file.
  
  
• Database snapshots are used internally by certain DBCC commands. These commands include DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKFILEGROUP. An internal database snapshot uses sparse alternate data streams of the original database files. Like sparse files, alternate data streams are a feature of the NTFS file system. The use of sparse alternate data streams allows for multiple data allocations to be associated with a single file or folder without affecting the file size or volume statistics.

------------------------------------ Database Filegroups -----------------------------------------

Database objects and files can be grouped together in filegroups for allocation and administration purposes. There are two types of filegroups:

Primary

-   The primary filegroup contains the primary data file and any other files not specifically assigned to another filegroup. 

-   All pages for the system tables are allocated in the primary filegroup.

User-defined

-   User-defined filegroups are any filegroups that are specified by using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

-   Log files are never part of a filegroup. Log space is managed separately from data space.

-   No file can be a member of more than one filegroup. 

-   Tables, indexes, and large object data can be associated with a specified filegroup. In this case, all their pages will be allocated in that filegroup, or the tables and indexes can be partitioned. The data of partitioned tables and indexes is divided into units each of which can be placed in a separate filegroup in a database. 

-   One filegroup in each database is designated the default filegroup. When a table or index is created without specifying a filegroup, it is assumed all pages will be allocated from the default filegroup. 

-  Only one filegroup at a time can be the default filegroup. 

-   Members of the db_owner fixed database role can switch the default filegroup from one filegroup to another. If no default filegroup is specified, the primary filegroup is the default filegroup.

File and Filegroup Example

The following example creates a database on an instance of SQL Server. The database has a primary data file, a user-defined filegroup, and a log file. The primary data file is in the primary filegroup and the user-defined filegroup has two secondary data files. An ALTER DATABASE statement makes the user-defined filegroup the default. A table is then created specifying the user-defined filegroup.

USE master;

GO

-- Create the database with the default data

-- filegroup and a log file. Specify the

-- growth increment and the max size for the

-- primary data file.

CREATE DATABASE MyDB

ON PRIMARY

  ( NAME='MyDB_Primary',

    FILENAME=

       'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_Prm.mdf',

    SIZE=4MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB),

FILEGROUP MyDB_FG1

  ( NAME = 'MyDB_FG1_Dat1',

    FILENAME =

       'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_1.ndf',

    SIZE = 1MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB),

  ( NAME = 'MyDB_FG1_Dat2',

    FILENAME =

       'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_2.ndf',

    SIZE = 1MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB)

LOG ON

  ( NAME='MyDB_log',

    FILENAME =

       'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB.ldf',

    SIZE=1MB,

    MAXSIZE=10MB,

    FILEGROWTH=1MB);

GO

ALTER DATABASE MyDB 

  MODIFY FILEGROUP MyDB_FG1 DEFAULT;

GO

-- Create a table in the user-defined filegroup.

USE MyDB;

CREATE TABLE MyTable

  ( cola int PRIMARY KEY,

    colb char(8) )

ON MyDB_FG1;

GO

SQL Server: Pages and Extents Architecture

--------------------------------------------Pages ------------------------------------------------------

-   The page is the fundamental unit of data storage in SQL Server. 
-   The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.
-   In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.
-   When the total row size of all fixed and variable columns in a table exceeds the 8 KB limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width.
-   Log files do not contain pages; they contain a series of log records.

------------------------------------------- Extent ------------------------------------------------------

-   An extent is a collection of eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.
-   Extents help efficiently manage pages. All pages are stored in extents.
-   SQL Server has two types of extents:

• Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
• Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

-   A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations.

Managing Extent Allocations

Global Allocation Map (GAM) : GAM pages record what extents have been allocated. Each GAM covers 64,000 extents, or almost 4 GB of data. The GAM has one bit for each extent in the interval it covers. If the bit is 1, the extent is free; if the bit is 0, the extent is allocated.

Shared Global Allocation Map (SGAM) : SGAM pages record which extents are currently being used as mixed extents and also have at least one unused page. Each SGAM covers 64,000 extents, or almost 4 GB of data. The SGAM has one bit for each extent in the interval it covers. If the bit is 1, the extent is being used as a mixed extent and has a free page. If the bit is 0, the extent is not used as a mixed extent, or it is a mixed extent and all its pages are being used.

Tracking Free Space

Page Free Space (PFS) pages record the allocation status of each page, whether an individual page has been allocated, and the amount of free space on each page. The PFS has one byte for each page, recording whether the page is allocated, and if so, whether it is empty, 1 to 50 percent full, 51 to 80 percent full, 81 to 95 percent full, or 96 to 100 percent full.