Physical architecture of SQL server

Components:

    ➣  Page
    ➣  Extents
    ➣  Table / Index
    ➣  Database File
    ➣  Database File Group
    ➣  Transaction Log file

1 . Page:-

• Page is the smallest unit of storage in SQL server.
• Page size is 8 KB.
• Page begins with a 96 byte header that stores page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page. After header, data rows are stored one after another. At end of the page a row offset table exists; one row for   each data row in page.
• Following is the lay out of page 
  

2. Extents :- 

• An extents consists of 8 adjacent pages.
• There are two types of extents –

                       ➣  Uniform Extents

                       ➣  Mixed extents

• Extent type Contents Uniform Extent All 8 pages in the extent are owned and used by a single object. Each of the 8 pages in the extent may be owned and used by Mixed Extent different objects.
• 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.
• While creating an index on an existing table that has enough rows to generate eight pages in the index, index is allocated in uniform extents.

3.Table :-

• A table is contained in one or more partitions and each partition contains data rows in either a heap or a clustered index structure.
• The pages of the heap or clustered index are managed in one or more allocation units, depending on the column types in the data rows.

                    ➣  Partition – is a user-defined unit of data organization that resides in single file group.

                    ➣  Heap – is a table without clustered index.

                    ➣  Clustered Table – is a table with clustered index.

4. Index:-

• SQL Server uses B-tree data structure for index.
• The top node of the B-tree is called root node. The bottom level nodes are called leaf nodes. Any index levels in between are known as intermediate levels.
• Index are of two types –

               ➣  Clustered Index – The leaf layer of a clustered index contains the data pages of the underlying table
               ➣  Non-clustered Index – The leaf layer of a non-clustered index contains index pages instead of data pages.

5. Database File:-

• SQL Server uses three types of files to map a database to file system of operating system 
• Primary Data File (.mdf) – It is the starting point of the database and points to the other files in the database. Every database has one primary data file. 
• Secondary Data File (.ndf) – Any data file other than primary data file. A database may or may not have secondary data files. 
• Log file (.ldf) - 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.

6. Database file Organisation :-

7. Database File Group :-

• Database files can be grouped into file groups for allocation and administration purposes.
• File groups are of two types – 

                    Primary – It contains primary data files and pages of system tables. Any other files are allocated in primary file 

                    group if no other file group is specified while  creating. 
                    User-defined – These are created by database users.

• One file can be part of only one file group.
• For large objects e.g. tables and indexes created in a file group, all their pages will also be part of the same file group or objects can be partitioned.

8.Transaction Log File :- 

• DB engine uses transaction logs to maintain integrity of database and for data recovery.
• Transaction log file consists of log records of operations performed and are stored sequentially.
• Following types of operations are logged – 

                 ➣ The start and end of each transaction. 
                 ➣ Every data modification (insert, update, or delete) 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

• Either of below two is put in a log record for an operation- 

                 ➣  The logical operation performed 

                 ➣  The before and after images of the modified data

Reference:
https://www.slideshare.net/divya071/physical-architecture-of-sql-server
https://technet.microsoft.com/en-IN/library/ms179276(v=sql.90).aspx