of 33

SQL Server 2005 Architecture | Database Index | Microsoft Sql Server

All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
SQL Server 2005 Physical Database Architecture All the contents of this file are based on the information published in MSDN, TechNet and SQL Server 2005 Online Tutorials. Page1 Introduction SQL Server 2005 is a set of components that work together to meet the data storage and analysis needs of the largest Web sites and enterprise data processing systems. The following components work together to manage data effectively in SQL server 2005. Physical Database Architecture Describes the logical c
          P      a       g         e         1 SQL Server 2005Physical DatabaseArchitecture All the contents of this file are based on the information published in MSDN, TechNet and SQL Server 2005 Online Tutorials.          P      a       g         e         1 Introduction SQL Server 2005 is a set of components that work together to meet the datastorage and analysis needs of the largest Web sites and enterprise data processingsystems. The following components work together to manage data effectively inSQL server 2005. Physical Database Architecture Describes the logical components defined in SQL Server databases and howthey are physically implemented in database files. Relational Database Engine Architecture Describes the features of the database engine that make it efficient atprocessing large numbers of concurrent requests for data from many users. Physical Database Architecture  The physical database architecture contains ã Pages and Extents ã Physical Database File and Filegroups ã Space Allocation and Reuse ã  Table and Index Architecture ã  Transaction Log Architecture Pages and Extends  The fundamental unit of data storage in SQL Server is the page. The disk spaceallocated to a data file (.mdf or .ndf) in a database is logically divided into pagesnumbered contiguously from 0 to n. Disk I/O operations are performed at the pagelevel. That is, SQL Server reads or writes whole data pages.Extents are a collection of eight physically contiguous pages and are used toefficiently manage the pages. All pages are stored in extents. Pages In SQL Server, the page size is 8 KB. This means SQL Server databases have 128pages per megabyte. Each page begins with a 96-byte header that is used to storesystem 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 theobject that owns the page. The following table shows the page types used in the data files of a SQL Serverdatabase. Page TypeContents DataData rows with all data, except text, ntext, image,nvarchar(max), varchar(max), varbinary(max), and xmldata, when text in row is set to ON.IndexIndex entries. Text/ImageLarge object data types:          P      a       g         e         1 ã text, ntext, image, nvarchar(max), varchar(max),varbinary(max), and xml dataVariable length columns when the data row exceeds 8KB: ã varchar, nvarchar, varbinary, and sql_variantGlobal Allocation Map,Shared Global AllocationMapInformation about whether extents are allocated.Page Free SpaceInformation about page allocation and free spaceavailable on pages.Index Allocation MapInformation about extents used by a table or index perallocation unit.Bulk Changed MapInformation about extents modified by bulk operationssince the last BACKUP LOG statement per allocationunit.Differential Changed MapInformation about extents that have changed since thelast BACKUP DATABASE statement per allocation unit.Note: Log files do not contain pages; they contain a series of log records.Data rows are put on the page serially, starting immediately after the header. A rowoffset table starts at the end of the page, and each row offset table contains oneentry for each row on the page. Each entry records how far the first byte of the rowis from the start of the page. The entries in the row offset table are in reversesequence from the sequence of the rows on the page. Large Row Support Rows cannot span pages in SQL Server 2005, however portions of the row may bemoved off the row's page so that the row can actually be very large. The maximumamount of data and overhead that is contained in a single row on a page is 8,060bytes (8 KB). However, this does not include the data stored in the Text/Image pagetype. In SQL Server 2005, this restriction is relaxed for tables that contain varchar,nvarchar, varbinary, or sql_variant columns. When the total row size of all fixed and          P      a       g         e         1 variable columns in a table exceeds the 8,060 byte limitation, SQL Serverdynamically moves one or more variable length columns to pages in theROW_OVERFLOW_DATA allocation unit, starting with the column with the largestwidth. This is done whenever an insert or update operation increases the total sizeof the row beyond the 8060 byte limit. When a column is moved to a page in theROW_OVERFLOW_DATA allocation unit, a 24-byte pointer on the srcinal page in theIN_ROW_DATA allocation unit is maintained. If a subsequent operation reduces therow size, SQL Server dynamically moves the columns back to the srcinal data page. 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. To make its space allocation efficient, SQL Server does not allocate whole extents totables with small amounts of data. SQL Server has two types of extents: ã Uniform extents are owned by a single object; all eight pages in the extentcan only be used by the owning object. ã Mixed extents are shared by up to eight objects. Each of the eight pages inthe extent can be owned by a different object.A new table or index is generally allocated pages from mixed extents. When thetable or index grows to the point that it has eight pages, it then switches to useuniform extents for subsequent allocations. If you create an index on an existingtable that has enough rows to generate eight pages in the index, all allocations tothe index are in uniform extents. Physical Database Files and Filegroups SQL 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. Filegroups 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  The primary data file is the starting point of the database and points to the
Related Search
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks