Read Previous Article: Special
Storage Series – I (Checking the 8K (8192) size of page)
There are two special formats
to store data that doesn’t fit on 8k data page that is if row size increases to
more than 8KB.
Resticted-Length Object
Data
UnResticted-Length Object
Data
Here in this article we will
see Restricted-Length Object Data, this is one way to store the data in
variable length column when the size of data crosses the limit of 8060 bytes.
SQL Server stores these over exceeded data in special Row-Overflow pages.
Let’s take a look at a table
with variable length data type and size of more than 8k; here we are creating a
table with rows that have a maximum defined length that is much longer than
8060 byte.
create table Tab_VKM_BigTable
(col1 varchar(3000),
col2 varchar(3000),
col3 varchar(3000),
col4 varchar(3000))
Command(s) completed successfully.
Our table is ready whose
maximum record size to store is greater than 8060 byte. Lets insert data with
size more than 8K byte.
insert into Tab_VKM_BigTable
select REPLICATE('a',2250),REPLICATE('b',2250),
REPLICATE('c',2250),REPLICATE('d',2250)
(1 row(s) affected)
Now we have record whose
length is more than 8060 bytes. You can query the table to see the record
select * from
Tab_VKM_BigTable
Now we will introspect the
table storage detail, ie in what all kind of pages data of table are getting
stored by querying System Catalog sys.partitions and sys.allocation_units.
sys.partitions
will hold a single entry irrespective
of whether you apply partitioning on table or not. It will give Partition Id
and Partition Number and number of rows in the table. Partiton number is 1 for
Non Partitioned Table.
sys.allocation_units
will hold information about how data
are allocated on various pages like page Type, number of Pages etc.
select OBJECT_NAME(p.object_id) as TableName,
p.partition_id,p.partition_number,p.rows,
au.total_pages as Pages,
au.type_desc as
PageType
from sys.partitions p, sys.allocation_units au
where p.partition_id=au.container_id
and p.object_id=OBJECT_ID('Tab_VKM_BigTable')
TableName partition_id parti_no rows Pages PageType
-------------------------------------------
---- ----- ----------------------
Tab_VKM_BigTable
72057594042056704 1 1
2 IN_ROW_DATA
Tab_VKM_BigTable
72057594042056704 1 1 2
ROW_OVERFLOW_DATA
(2 row(s) affected)
Now from the output we can see
there are two pages for each Page Type. We can fire DBCC IND query to and see
the detail of each pages.
Let’s create a table which will hold the data from DBCC
IND command.
create table TAB_VKM_TablePages
(PageFID tinyint,
PagePID int,
IAMDID tinyint,
IAMPID int,
ObjectID int,
IndexID tinyint,
PartitionNumber
tinyint,
PartitionID
bigint,
iam_chain_type
varchar(30),
PageType tinyint,
IndexLevel tinyint,
NextPageFID
tinyint,
NextPagePID
int,
PrevPageFID
tinyint,
PrevPagePID int)
Command(s) completed successfully.
Here we are executing DBCC IND for ‘Tab_VKM_BigTable' and storing the output
in out custom made TAB_VKM_TablePages table.
insert into TAB_VKM_TablePages
exec ('dbcc
ind (''Sparsh'',''Tab_VKM_BigTable'',-1)')
select PageFID,PagePID,OBJECT_NAME(objectId) as [Table],
iam_chain_type,PageType from TAB_VKM_TablePages
PageFID
PagePID Table
iam_chain_type PageType
-------
------- ----------------------------------- --------
1 353
Tab_VKM_BigTable In-row data
10
1 352
Tab_VKM_BigTable In-row data
1
1 343
Tab_VKM_BigTable Row-overflow data
10
1 342
Tab_VKM_BigTable Row-overflow data
3
(4 row(s) affected)
From the output we can observe
two pages for In-row data and two pages for Row-overflow data. The
description for page type are
PageType = 1, Data Page
PageType = 2, Index Page
PageType = 3, LOB, Row-Overflow page, TEXT_MIXED
PageType = 4, LOB, Row-Overflow page, TEXT_DATA
PageType = 10, IAM Page
Here we can make out there are
one data page and one IAM page for both in-row-data and Row-overflow-data.
People can’t position Programmer exclusive
of logic, brainy their created codes are; brainy they are ;)
Post Reference: Vikram Aristocratic Elfin Share
No comments:
Post a Comment