Indexes In depth Knowledge
--CREATE DATABASE HeapsDB
use HeapsDB
go
CREATE TABLE NumbersTable(NumberValue bigint not null, BiggerNumber bigint not null, CharacterColumn char(50))
DBCC IND('HeapsDB',NumbersTable ,-1)
We have around 14 types of pages
Page type 1 is a datpage
and Page type 10 is a IAM page
The Page is of 3 Parts.
1. Header
2. Data
3. Row Offset
Header for 96 bytes
Data depends on DataType + 7 Bytes(Identify the Nulls)
Row Offset is for Address of Every Record
One page = 8192 Bytes
DataPages --> Actual data Get Stored.
IAM page --> Locates the DataPages.
DBCC TRACEON(3604)
DBCC PAGE('DATABASENAME',1,PAGEID,1)
GO
insert into NumbersTable
select
NumberValue,
NumberValue + 5000000,
LEFT(replicate((cast(NumberValue as varchar(50))),50),50)
from
(
select NumberValue = ROW_NUMBER() over( order by newid() asc) from
master..spt_values a
cross apply master..spt_values b where a.type = 'P' and a.number <= 200 and a.number>0 and b.type = 'P' and b.number <= 200 and b.number>0
) a;
select page_count, index_depth,page_Level = index_level,page_count,record_count,
* from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID('NumbersTable'),NULL, NULL, 'DETAILED')
SELECT DB_NAME([ddips].[database_id]) AS [DatabaseName]
, OBJECT_NAME([ddips].[object_id]) AS [TableName]
, [i].[name] AS [IndexName] FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, NULL) AS ddips
INNER JOIN [sys].[indexes] AS i
ON [ddips].[index_id] = [i].[index_id]
AND [ddips].[object_id] = [i].[object_id]
--Page Allocation
SELECT allocated_page_iam_page_id ,allocated_page_page_id, next_page_page_id,previous_page_page_id
FROM sys.dm_db_database_page_allocations (db_id(),OBJECT_ID('NumbersTable'),NULL, NULL, 'DETAILED')
WHERE page_type_desc is not null and page_type_desc = 'data_page'
--Create a Clustered Index
create clustered index CIX_NumbersTable on NumbersTable(NumberValue) with (maxdop=1)
-- Look into the data and Index pages
SELECT allocated_page_iam_page_id ,allocated_page_page_id, next_page_page_id,previous_page_page_id , page_level,page_type_desc
FROM sys.dm_db_database_page_allocations (db_id(),OBJECT_ID('NumbersTable'),NULL, NULL, 'DETAILED')
WHERE page_type_desc is not null order by page_level desc
--and page_type_desc = 'index_page'
