Skip to main content
SQL Server Pages and Extents

 Q. What is the basic storage unit in SQL Server database?
Page is the basic unit of data storage in SQL Server. Disk space allocated to the data files (.mdf or .ndf)  are logically divided into pages. Log files do not contain pages, they contain log records.

Q. What is the size of a data page?
Size of a page in SQL Server is 8KB or 8192 Bytes (1024 bytes * 8KB)

Out of 8192 Bytes,
Header takes 96 Bytes
Row offset takes 36 Bytes.
So, 8192 Bytes - 96 Bytes- 36 Bytes = 8060 Bytes is available to store the actual data.

Q. What happens if the data to be inserted exceeds maximum size of a data page, that is 8KB?
(Row-Overflow Data Exceeding 8 KB)
The maximum amount of data that a page can occupy is 8060 bytes. If the total size of a row exceeds 8060 bytes, SQL Server moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit and keeps a pointer to this new page in the original data page.

Q. What are the different types of pages and what are their use?
Different Page types are:
Data, Index, Text/Image, Page Free Space,Global Allocation Map, Shared Global Allocation ,Map, Index Allocation Map, Bulk Changed Map and Differential Changed Map.

1. Data - Use to store all data rows except text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data.
2. Index - Used for index entries.
3. Text/Image - Used for text,ntext,varchar(max),nvarchar(max),varbinary(max),image and xml data
4. Page free space - Used to store information about free space available on pages and page allocation details.
5. Global Allocation Map, Shared Global Allocation Map -Information about extents.
6.Index Allocation Map- Information about Index per alocation unit.
7. Bulk Changes Map
8. Differential Changed Map.

Q. What is the structure of a Page.
A Page contain Header, Data rows and Row offset table.
Data rows- Data pages are stored sequentially just after header.
Row offset- Row offset table is stored at the end of the page and contain one entry for each data records which says how far the first bytes of the row from the start of the page.

Q. What is an extent?
An Extent is a set of 8 pages.

Q. What is the size of an Extent?
Since 1 extent occupy 8 pages size of 1 extent is 8KB*8 =64KB.

Q. What are the type of extents
SQL server has 2 types of extents:
Uniform extents and Mixed Extents

Uniform Extents: Uniform extents are owned by single objects. That means all 8 pages in the extents can only used by the owning object.
Mixed Extents: Mixed extents are shared by upto 8 objects.









Ref:  msdn.microsoft.com
Understanding Pages and Extents
http://msdn.microsoft.com/en-us/library/ms190969%28v=sql.105%29.aspx



Comments

Post a Comment

Popular posts from this blog

SQL Server script Error: Insufficient result space to convert uniqueidentifier value to char.

Error:    G etting below error while trying to copy data from one table to another: Msg 8170, Level 16, State 2, Line 1004  Insufficient result space to convert uniqueidentifier value to char.   Fix: UserID column which would be having Unique identifier data require more space (36 character). Size is specified as 15 here. To fix it just specify 36 as varchar length or specify Uniqueidentifier as data type for UserID  column.
Post a Comment
Read more

While executing Select query to pull data from a different server, SQL Server is showing following error:

Msg 7202, Level 11, State 2, Line 1 Could not find server 'LAPTOP-09O6NE3K\SQLEXPRESS03' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Completion time: 2024-02-05T17:18:24.6869756+05:30 Solution: Step1:  Check if the server is available in the sys.servers table SELECT name from sys.servers Step2:  If no, add the new server to the sys.servers table EXEC sp_addlinkedserver @server = 'server name' Eg" EXEC sp_addlinkedserver @server = 'LAPTOP-09O6NE3K\SQLEXP RESS03' Step3:  Recheck if the new server name is added to the sys.servers table. If yes, try then rerunning the select query which throwed the error. Now it should pull data from the table in the new server successfully.    
Post a Comment
Read more

Overcoming Performance Issues with Memory Optimized tables - SQL Server 2014 CPT2

Overcoming Performance Issues with Memory Optimized tables - SQL Server 2014 CPT2 1.        Problem Tuning    databases so that they provide the best possible performance is one of the most important responsibilities of database administrators (DBAs).   SQL Server was originally designed at a time when it could be assumed that main memory was very expensive, so data needed to reside on disk except when it was actually needed for processing.   As the amount data    within an organization increases, accumulation of data affects the productivity of DBAs, who must spend considerable time and effort on achieving the high levels of database performance. 2.        Solution Nowadays memory is much cheaper and a working data set can be stored entirely in memory. The assumption that main memory was very expensive is no longer valid as memory prices have dropped enormously. Memory-opti...
Post a Comment
Read more