T-SQL Index Internal & Statistics

use AdventureWorks2012   
GO

select object_id   
,name     
,index_id    
,type    
,type_desc    
,is_unique    
,data_space_id    
,ignore_dup_key    
,is_primary_key    
,is_unique_constraint    
,fill_factor    
,is_padded    
,is_disabled    
,is_hypothetical    
,allow_row_locks     
,allow_page_locks     
,has_filter     
,filter_definition    
from sys.indexes where object_id=object_id('HumanResources.Employee')    
GO

select object_name=object_name(i.object_id),    
    index_name=id.name,    
    index_type=id.type_desc,    
    id.index_id,    
    column_name=c.name,    
    c.column_id     
from sys.index_columns i join sys.columns c    
  on i.object_id=c.object_id    
  and i.column_id=c.column_id    
  join sys.indexes id    
  on i.object_id=id.object_id    
  and i.index_id=id.index_id    
    where i.object_id=object_id('HumanResources.Employee')    
    order by index_id,column_id    
GO

select database_id   
,object_id   
,index_id    
,partition_number     
,index_type_desc       
,alloc_unit_type_desc         
,index_depth     
,index_level     
,avg_fragmentation_in_percent     
,fragment_count       
,avg_fragment_size_in_pages     
,page_count           
,avg_page_space_used_in_percent     
,record_count         
,ghost_record_count   
,version_ghost_record_count     
,min_record_size_in_bytes     
,max_record_size_in_bytes     
,avg_record_size_in_bytes     
,forwarded_record_count     
,compressed_page_count    
from sys.dm_db_index_physical_stats(db_id(),object_id('HumanResources.Employee'),1,DEFAULT,'DETAILED' )    
GO

select database_id   
--,object_id    
--,index_id    
--,partition_id    
--,rowset_id    
--,allocation_unit_id    
--,allocation_unit_type    
,allocation_unit_type_desc    
--,data_clone_id    
--,clone_state    
--,clone_state_desc    
,extent_file_id    
,extent_page_id    
,allocated_page_iam_file_id    
,allocated_page_iam_page_id    
,allocated_page_file_id    
,allocated_page_page_id    
,is_allocated    
,is_iam_page    
,is_mixed_page_allocation    
,page_free_space_percent    
,page_type    
,page_type_desc    
,page_level    
,next_page_file_id    
,next_page_page_id    
--,previous_page_file_id    
--,previous_page_page_id    
--,is_page_compressed    
--,has_ghost_records     
from sys.dm_db_database_page_allocations(db_id(),object_id('HumanResources.Employee'),1,DEFAULT,'DETAILED' )    
GO


DBCC IND('AdventureWorks2012','HumanResources.Employee',1)    
GO

DBCC TRACEON(3604,-1)   
GO    
DBCC PAGE(AdventureWorks2012,1,1052,3) with tableresults

sp_helptext fn_PhysLocCracker   
GO

select [NationalIDNumber], 'Location(File:Page:Slot)'=sys.fn_physLocFormatter(%%physloc%%),   
KeyHashValue=%%lockres%%    
from [AdventureWorks2012].[HumanResources].[Employee]    
GO

DBCC SHOW_STATISTICS('HumanResources.Employee','PK_Employee_BusinessEntityID')   
WITH STAT_HEADER, HISTOGRAM    
GO

SELECT OBJECT_NAME(s.object_id) AS object_name,   
    COL_NAME(sc.object_id, sc.column_id) AS column_name,    
    s.name AS statistics_name,    
    STATS_DATE=STATS_DATE(s.object_id,s.stats_id),    
    auto_created,    
    user_created    
FROM sys.stats AS s JOIN sys.stats_columns AS sc    
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id    
WHERE --s.name like '_WA%'    
sc.object_id=object_id('HumanResources.Employee')    
ORDER BY OBJECT_NAME(s.object_id),stats_date    
GO

UPDATE STATISTICS HumanResources.Employee   
-- WITH FULLSCAN    
WITH SAMPLE 50 PERCENT     
GO