Architecture

Rajesh

Components of Database Engine

Issue QueryQuery saved in T-LogAffected pages

loaded into RAMPages modified in

RAMEventually…Changed pages

written to disk and T-Log marked with Checkpoint

CheckpointWrites all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.

SyntaxCHECKPOINT [ checkpoint_duration ]

Dirtypage

Buffer Pool

Dirtypage

Dirtypage Disk

Recovery Interval

Events That Cause Checkpoints• Before a database backup - Database Engine automatically performs a

checkpoint so that all changes to the database pages are contained in the backup.

• Checkpoints occur automatically when either of the following conditions occurs:

• The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recovery interval server configuration option.

• The log becomes 70 percent full, and the database is in log-truncate mode.

• A database is in log truncate mode when both these conditions are TRUE: the database is using the Simple recovery model, and, after execution of the last BACKUP DATABASE statement that referenced the database, one of the following events occurs:

• A minimally logged operation is performed in the database, such as a minimally logged bulk copy operation or a minimally logged WRITETEXT statement is executed.

• An ALTER DATABASE statement is executed that adds or deletes a file in the database.

• Also, stopping a server issues a checkpoint in each database on the server.

• Checkpoint frequency depends on the recovery interval of the database. http://msdn.microsoft.com/en-us/library/ms189573.aspx

Lazy WriterThe job of the lazy writer is to find dirty pages in the buffer pool and write them out to disk and drop those pages from cache. It does this to keep a certain amount of free pages available within the buffer pool for data that may be requested by other queries. The pages that it writes out are ‘older’ pages, ones that haven’t been used for a while.If there’s lots of available buffers, the lazy writer will be doing relatively little work and the number of pages written to disk will be quite low. If the lazy writer’s consistently writing lots of data, it may indicate that there’s a memory bottleneck.  Database Recovery

PhasesAnalysis

Undo

Redo

Physical Database ArchitectureThe physical database architecture contains· Pages and Extents· Physical Database File and File groups· Space Allocation and Reuse· Table and Index Architecture· Transaction Log Architecture

PagesIn SQL Server, the page size is 8 KB.

Page type Contents

Data Data rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON.

Index Index entries.

Text/Image Large object data types:•text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml dataVariable length columns when the data row exceeds 8 KB:•varchar, nvarchar, varbinary, and sql_variant

Global Allocation Map, Shared Global Allocation Map Information about whether extents are allocated.

Page Free Space Information about page allocation and free space available on pages.

Index Allocation Map Information about extents used by a table or index per allocation unit.

Bulk Changed Map Information about extents modified by bulk operations since the last BACKUP LOG statement per allocation unit.

Differential Changed Map Information about extents that have changed since the last BACKUP DATABASE statement per allocation unit.

Types of Pages

Managing Extent Allocations and Free Space

The SQL Server 2005 data structures that manage extent allocations and track freespace have a relatively simple structure.SQL Server uses two types of allocation maps to record the allocation of extents:· 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.

Managing Space Used by Objects – IAM (Index Allocation Map)An IAM (Index Allocation Map) page tracks approximately 4GB worth of space in a single data file used by an allocation unit. An allocation unit is one of three types:• IN_ROW_DATA Holds a partition of a heap or index.• LOB_DATA Holds large object (LOB) data types, such as xml, varbinary(max), and varchar(max).• ROW_OVERFLOW_DATA Holds variable length data stored in varchar, nvarchar, varbinary, or sql_variant columns that exceed the 8,060 byte row size limit.An IAM page covers a 4-GB range in a file and is the same coverage as a GAM or SGAM page. If the allocation unit contains extents from more than one file, or more than one 4-GB range of a file, there will be multiple IAM pages linked in an IAM chain. Therefore, each allocation unit has at least one IAM page for each file on which it has extents. There may also be more than one IAM page on a file, if the range of the extents on the file allocated to the allocation unit exceeds the range that a single IAM page can record.

When the SQL Server Database Engine has to insert a new row and no space is available in the current page, it uses the IAM and PFS pages to find a page to allocate, or, for a heap or a Text/Image page, a page with sufficient space to hold the row. The Database Engine uses the IAM pages to find the extents allocated to the allocation unit. For each extent, the Database Engine searches the PFS pages to see if there is a page that can be used. Each IAM and PFS page covers lots of data pages, so there are few IAM and PFS pages in a database. This means that the IAM and PFS pages are generally in memory in the SQL Server buffer pool, so they can be searched quickly. For indexes, the insertion point of a new row is set by the index key. In this case, the search process previously described does not occur.http://technet.microsoft.com/en-us/library/ms187501.aspx

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.

After an extent has been allocated to an object, the Database Engine uses the PFS pages to record which pages in the extent are allocated or free. This information is used when the Database Engine has to allocate a new page. The amount of free space in a page is only maintained for heap and Text/Image pages. It is used when the Database Engine has to find a page with free space available to hold a newly inserted row. Indexes do not require that the page free space be tracked, because the point at which to insert a new row is set by the index key values.

A PFS page is the first page after the file header page in a data file (page number 1). This is followed by a GAM page (page number 2), and then an SGAM page (page 3). There is a PFS page approximately 8,000 pages in size after the first PFS page. There is another GAM page 64,000 extents after the first GAM page on page 2, and another SGAM page 64,000 extents after the first SGAM page on page 3. The following illustration shows the sequence of pages used by the Database Engine to allocate and manage extents.

Extents

Extents are the basic unit in which space is managed. An extent is eight physicallycontiguous pages, or 64 KB. This means SQL Server databases have 16 extents permegabyte.

Physical Database Files and File groupsSQL Server 2005 maps a database over a set of operating-system files. Data andlog information are never mixed in the same file, and individual files are used onlyby one database. File groups are named collections of files and are used to help withdata placement and administrative tasks such as backup and restore operations.Database Files

SQL Server 2005 databases have three types of files:· Primary data files· Secondary data files· Log files

Database File groupsDatabase objects and files can be grouped together in filegroups for allocation andadministration purposes. There are two types of filegroups:PrimaryThe primary filegroup contains the primary data file and any other files notspecifically assigned to another filegroup. All pages for the system tables areallocated in the primary filegroup.User-definedUser-defined filegroups are any filegroups that are specified by using theFILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

System databases

Master

MsdbTempModel Resource

Other System Databases

Report Server Temp DB

Report Server

Distribution

Replication Reporting Services