-----------------------------------------------------------------------------
/*
SQL Server در Execution Plan آشنایی با مفهوم  
Site:        http://www.NikAmooz.com
Instagram:   https://instagram.com/nikamooz/
Telegram:	 https://telegram.me/nikamooz
Created By:  Iman Bagheri
*/
-----------------------------------------------------------------------------

/****** Drop Database TestDB ******/ 
 DROP DATABASE if exists TestDB
GO
/****** Create Database TestDB ******/
 

CREATE DATABASE TestDB
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB.mdf' , SIZE = 10240KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'TestDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\TestDB_log.ldf' , SIZE = 10240KB , FILEGROWTH = 65536KB )
GO


/****** Create Table  ******/ 
DROP TABLE IF EXISTS Tbl_TestPage
USE TestDB 
GO
CREATE TABLE [dbo].[Tbl_TestPage](
	[Name] [char](4000) NULL,
	[Family] [char](4000) NULL
) ON [PRIMARY]
GO

-- Maximum storage volume on pages = 8000 byte
/****** درج رکورد در جدول ******/

insert into [Tbl_TestPage]
(
[Name],
[Family]
)
values ('Iman','Bagheri')
go 


select * from TestDB..[Tbl_TestPage]
 
/****** تعداد پیج هایی که به ازای یک جدول استفاده می شود ******/
 
SELECT  
    t.NAME AS TableName, 
    p.rows AS RowCounts,
    SUM(a.total_pages) AS TotalPages, 
    SUM(a.used_pages) AS UsedPages, 
    (SUM(a.total_pages) - SUM(a.used_pages)) AS UnusedPages
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME  = 'Tbl_TestPage' 
    AND t.is_ms_shipped = 0
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

 
 --1 extend= 8 pages * 8 KB = 64 KB


-- TotalPages= 1 extend +  1 IAM Page = 9 Pages


/****** درصد استفاده از هر پیج به ازای یک جدول ******/

SELECT  [page_count], [record_count], [avg_page_space_used_in_percent] 
FROM sys.dm_db_index_physical_stats (DB_ID (N'TestDB'), OBJECT_ID (N'Tbl_TestPage'), 0,  DEFAULT, 'DETAILED');
GO 

/****** نمایش نام و مشخصات پیج ها ******/

SELECT DB_NAME(PA.database_id) [DataBase], 
    OBJECT_NAME(PA.object_id) [Table], SI.Name [Index], 
    is_allocated, allocated_page_file_id [file_id], 
    allocated_page_page_id [page_id], page_type_desc, 
    page_level, previous_page_page_id [previous_page_id], 
    next_page_page_id [next_page_id],
	page_free_space_percent,
	page_type 
FROM sys.dm_db_database_page_allocations 
    (DB_ID('TestDB'), 
         OBJECT_ID('Tbl_TestPage'),NULL, NULL, 'DETAILED') PA
         LEFT OUTER JOIN sys.indexes SI 
        ON SI.object_id = PA.object_id 
                   AND SI.index_id = PA.index_id
ORDER BY   page_id



 
