• 根节点、中间节点行结构
• 系统头部信息（2字节）+Key+&+PageId
• 叶子节点
• 参见行数据结构

1. 聚集索引键值不能超过900字节，因为生成keyhashvalue时，如果大于900字节性能会有很大影响。Keyhashvalue用于查询页的数据行

2. 聚集索引键值尽量保持短，每页只有8096字节可用。减少中间节点的层数。

3. 聚集索引键值采用递增原则，有利于数据页连续性，减少BTree调整。

DBCC IND ( { 'dbname' | dbid }, { 'objname' | objid }, { nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )

• Dbname:数据库名
• Dbid：数据库Id
• Objname：表名
• Objid：表ID
• nonclustered indid:非聚集索引ID，-2 根节点 -1 中间节点 Branch Nodes 0 叶子节点、1 所有节点

Select * from sys.dm_db_database_page_allocations(DB_ID(), object_id('TestData8000'),NULL,NULL,'DETAILED')

sys.dm_db_database_page_allocations(@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)

• @DatabaseId：数据库Id
• @TableId:表名
• @indexId：
• @PartionId：分区Id

DBCC PAGE
(
['database name'|database id], -- can be the actual name or id of the database
file number, -- the file number where the page is found
page number, -- the page number within the file
print option = [0|1|2|3] -- display option; each option provides differing levels of information
)

--dbcc show_statistics ([tablename], [indexname])

--dbcc show_statistics (TestDataUnIndex, PK_TestDataUnIndex)

-- 打开IO开销统计 set STATISTICS io ON

-- 打开执行时间统计 set STATISTICS TIME ON

-- Select * from Table

SELECT TOP  

total_worker_time/execution_count AS [Avg CPU Time], 

(SELECT SUBSTRING(text,statement_start_offset/, 

(CASE WHEN statement_end_offset = - then LEN(CONVERT(nvarchar(max), text)) *  ELSE statement_end_offset end -statement_start_offset)/) 

FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, * 

FROM sys.dm_exec_query_stats 

ORDER BY [Avg CPU Time] DESC

select request_session_id,OBJECT_NAME(resource_associated_entity_id) tableName from 

sys.dm_tran_locks 

where resource_type='OBJECT' 

use master 

go 

--检索死锁进程 

select spid, blocked, loginame, last_batch, status, cmd, hostname, program_name 

from sysprocesses 

where spid in 

( select blocked from sysprocesses where blocked <> 0 ) or (blocked <>0) 

select request_session_id,OBJECT_NAME(resource_associated_entity_id) tableName from 

sys.dm_tran_locks 

where resource_type='OBJECT'