--查看索引碎片 select db_name(database_id) as '数据库名', object_name(t.object_id) as '表名', t.index_id as '索引id', t1.index_name as '索引名称', t1.type_desc as '索引类型', t1.column_name as '索引列名', t.partition_number as '当前索引所在分区', t.page_count as '页统计', t.avg_page_space_used_in_percent as '页使用率' , t.record_count as '页行记录数', t.avg_record_size_in_bytes as '平均每条记录大小(B)', t.avg_fragmentation_in_percent as '索引碎片比率', t.fragment_count as '索引中的碎片数量', t.avg_fragment_size_in_pages as '一个索引中碎片的平均页数' from sys.dm_db_index_physical_stats(db_id('db_tank'),NULL,NULL,NULL,NULL) t join (select distinct t3.object_id,t3.index_id,object_name(t3.object_id) as 'table_name',t3.name as 'index_name',t3.type_desc, t1.name as 'column_name' from sys.columns t1 join sys.index_columns t2 on t1.column_id = t2.column_id AND t1.object_id = t2.object_id join sys.indexes t3 on t2.index_id = t3.index_id AND t2.object_id = t3.object_id) t1 on t1.object_id = t.object_id AND t1.index_id = t.index_id where object_name(t.object_id) = 'sys_users_goods' --查看所有表中对应的索引名与索引列 select t3.object_id,object_name(t3.object_id) as 'table_name',t3.name as 'index_name',t3.type_desc, t1.name as 'column_name' from sys.columns t1 join sys.index_columns t2 on t1.column_id = t2.column_id AND t1.object_id = t2.object_id join sys.indexes t3 on t2.index_id = t3.index_id AND t2.object_id = t3.object_id where object_name(t3.object_id) = 'sys_users_goods' --查看表中所有索引 SELECT name,type_desc FROM sys.indexes WHERE object_id=object_id('O_Orders') --根据索引名称查看对应的列 DBCC SHOW_STATISTICS(O_Orders,idx_order_status_2) DBCC SHOW_STATISTICS(O_Orders,IX_O_OrdersUID) --查找碎片率大于40%的 SELECT object_name(object_id) ,index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent, fragment_count,avg_fragment_size_in_pages,page_count,record_count, avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('DBNAME'), OBJECT_ID(''),NULL,NULL,'Sampled') WHERE avg_fragmentation_in_percent>40
1. 删除并重建索引 2. 使用DROP_EXISTING子句重建索引 3. 执行DBCC DBREINDEX 4. 执行DBCC INDEXDEFRAG dbcc showcontig('Log_FairBattlePrestige') dbcc dbreindex('Log_FairBattlePrestige')
alter table index_name on table_name rebuild with(online=off);
ALTER INDEX ALL ON Employee REBUILD
WITH (FILLFACTOR = 60, SORT_IN_TEMPDB = ON,STATISTICS_NORECOMPUTE = ON);
-------------------------
STATISTICS_NORECOMPUTE = {ON | OFF}
指定是否重新计算分布统计信息。默认为OFF。
SORT_IN_TEMPDB = {ON | 关闭 }
适用于：SQL Server（从SQL Server 2008开始）和SQL数据库。
指定是否将排序结果存储在tempdb中。默认为OFF。
ON
用于构建索引的中间排序结果存储在tempdb中。如果tempdb与用户数据库位于不同的磁盘集上，则可能会减少创建索引所需的时间。但是，这会增加索引构建期间使用的磁盘空间量。
OFF
中间排序结果与索引存储在同一数据库中。
如果不需要排序操作，或者可以在内存中执行排序，则忽略SORT_IN_TEMPDB选项。
有关更多信息，请参阅索引的SORT_IN_TEMPDB选项。
参考：联机丛书alter index :https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-2017
----------------------------------------------------------------
核心：sys.dm_db_index_physical_stats (db_id(‘数据库名’),null,null,null,’DETAILED’ )
SELECT object_name(dt.object_id) Tablename,si.name IndexName,
dt.avg_fragmentation_in_percent AS ExternalFragmentation,
dt.avg_page_space_used_in_percent AS InternalFragmentation FROM
( SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (db_id(‘数据库名’),null,null,null,’DETAILED’ )
WHERE index_id <> 0) AS dt INNER JOIN sys.indexes si ON si.object_id=dt.object_id
AND si.index_id=dt.index_id
AND dt.avg_fragmentation_in_percent>10 AND
dt.avg_page_space_used_in_percent<75 ORDER BY avg_fragmentation_in_percent DESC
（1）avg_fragmentation_in_percent
此列中所返回的值可确定索引的逻辑碎片（堆的区碎片）。逻辑碎片是在索引的叶级别中次序混乱页面的百分比，而区碎片是在索引的叶级别中次序混乱区的百分比。由于磁头只有左右跳动才能按照顺序读取页面，因此，逻辑碎片和区碎片会因为其需要额外的 I/O 和磁头运动而影响索引的性能。尽量保证逻辑碎片和区碎片均接近于零。
（2）avg_page_space_used_in_percent
此列可以确定索引页的填充度。为了正确配置该数字以使其尽量接近 100%，请在调整索引填充因子的同时观察所出现的页面分割数量。在某一刻，页面分割的数量会开始急剧增加，这表明您设置的索引填充因子数值高于其应有的数值。调整索引的填充因子需要花费一定的时间并需要进行测试，而且必须在事先进行合理规划。（如果未向索引中随意插入，则可以将索引填充因子设置为 100，且不必担心增加的页面分割数。）
外部碎片对于性能的影响上面说过，主要是在于需要进行更多的跨区扫描，从而造成更多的IO操作.
而内部碎片会造成数据行分布在更多的页中，从而加重了扫描的页树，也会降低查询性能.
下面通过一个例子看一下,我们人为的为刚才那个表插入一些数据造成内部碎片:
通过查看碎片，我们发现这时碎片已经达到了一个比较高的程度:
通过查看对碎片整理之前和之后的IO，我们可以看出，IO大大下降了:
3、对于碎片的解决办法
基本上所有解决办法都是基于对索引的重建和整理，只是方式不同
1.删除索引并重建
这种方式并不好.在删除索引期间，索引不可用.会导致阻塞发生。而对于删除聚集索引，则会导致对应的非聚集索引重建两次(删除时重建，建立时再重建).虽然这种方法并不好，但是对于索引的整理最为有效
2.使用DROP_EXISTING语句重建索引
为了避免重建两次索引，使用DROP_EXISTING语句重建索引，因为这个语句是原子性的，不会导致非聚集索引重建两次，但同样的，这种方式也会造成阻塞
3.如前面文章所示，使用ALTER INDEX REBUILD语句重建索引
使用这个语句同样也是重建索引，但是通过动态重建索引而不需要卸载并重建索引.是优于前两种方法的，但依旧会造成阻塞。可以通过ONLINE（rebuild index会复制旧索引来新建索引--基于快照，此时旧的索引依然可以被读取和修改，但是所以在旧索引上的修改都会同步更新到新索引下。中间会有一些冲突解决机制，在新旧索引借还的时候，索引不可用）关键字减少锁，但会造成重建时间加长.
4.使用ALTER INDEX REORGANIZE
这种方式不会重建索引，也不会生成新的页，仅仅是整理，当遇到加锁的页时跳过，所以不会造成阻塞。但同时，整理效果会差于前三种.
4、理解填充因子
重建索引固然可以解决碎片的问题.但是重建索引的代价不仅仅是麻烦，还会造成阻塞。影响使用.而对于数据比较少的情况下，重建索引代价并不大。而当索引本身超过百兆的时候。重建索引的时间将会很让人蛋疼.
填充因子的作用正是如此。对于默认值来说，填充因子为0（0和100表示的是一个概念）,则表示页面可以100%使用。所以会遇到前面update或insert时，空间不足导致分页.通过设置填充因子，可以设置页面的使用程度:
如何设置填充因子的值并没有一个公式或者理念可以准确的设置。使用填充因子虽然可以减少更新或者插入时的分页，但同时因为需要更多的页，所以降低了查询的性能和占用更多的磁盘空间.如何设置这个值进行trade-off需要根据具体的情况来看.
具体情况要根据对于表的读写比例来看,我这里给出我认为比较合适的值:
1.当读写比例大于100：1时，不要设置填充因子，100%填充
2.当写的次数大于读的次数时，设置50%-70%填充
3.当读写比例位于两者之间时80%-90%填充
上面的数据仅仅是我的看法，具体设置的数据还要根据具体情况进行测试才能找到最优.
三、DBCC状态、验证、其他语句
状态语句
DBCC INPUTBUFFER 显示从客户端发送到MS SQL Server 的最后一个语句
DBCC OPENTRAN 如果在指定数据库内存在最旧的活动事务和最旧的分布和非分布式复制事务，
则显示与之相关的信息。只有当存在活动事务或数据库包含复制信息时，
才显示结果。如果没有活动事务，就显示信息性消息
DBCC OUTPUTBUFFER 以十六进制或 ASCII 格式返回指定系统进程 ID (SPID) 的当前输出缓冲区
DBCC PROCCACHE 以报表形式显示有关过程高速缓存的信息
DBCC SHOWCONTIG 显示指定的表的数据和索引的碎片信息
DBCC SHOW_STATISTICS 显示指定表上的指定目标的当前分布统计信息
DBCC SQLPERF 提供有关所有数据库中的事务日志空间使用情况的统计信息
DBCC TRACESTATUS 显示跟踪标记的状态
DBCC USEROPTIONS 返回当前连接的活动（设置）的 SET 选项
验证语句
DBCC CHECKALLOC 检查指定数据库的磁盘空间分配结构的一致性
DBCC CHECKCATALOG 检查指定数据库中的系统表内及系统表间的一致性
DBCC CHECKCONSTRAINTS 检查指定表上的指定约束或所有约束的完整性
DBCC CHECKDB 检查指定数据库中的所有对象的分配和结构完整性
DBCC CHECKFILEGROUP 检查指定文件组中的所有表（在当前数据库中）的分配和结构完整性
DBCC CHECKIDENT 检查指定表的当前标识值，如有必要，还对标识值进行更正DBCC CHECKTABLE
检查指定表或索引视图的数据、索引及 text、ntext 和 image 页的完整性
DBCC NEWALLOC 检查数据库的扩展结构内的每个表的数据和索引页的分配
其他语句
DBCC dllname (FREE) 从内存中卸载指定的扩展存储过程动态链接库 (DLL)
DBCC HELP 返回指定的 DBCC 语句的语法信息
DBCC PINTABLE 将表标记为驻留，这表示MS SQL Server不从内存中刷新表页
DBCC ROWLOCK 在MS SQL Server 6.5 版中使用，对表启用插入行锁定 (IRL) 操作
DBCC TRACEOFF 禁用指定的跟踪标记
DBCC TRACEON 打开（启用）指定的跟踪标记
DBCC UNPINTABLE 将表标记为不在内存驻留。将表标记为不在内存驻留后，
可以清空高速缓存中的表页