已经介绍了如何使用索引,当一个索引创建时,以B-Tree格式存放数据,拥有根节点、中间节点、叶子节点。叶子节点是最底层的节点,在聚集索引中,包含了实际数据,而每个数据页有8KB。
当表中的数据的增删改发生时,会尝试把数据插入到合适的数据页中。比如有一个聚集索引在SSN上,当插入一个新的SSN数时。SQLServer会尝试把数据插入到合适的数据页,假设SSN从2开始,此时在最后的数据页中找到这个页面是以SSN开始的,SQLServer将会仅仅在这个页中插入新行。如果你的8K页面已经满了,或者没有足够的空间来存放新的SSN,此时就会发生分页,数据页会一分为二,或者分成更多的页,分页的数量根据新数据的大小。如果新数据不大,那么新数据将只会使数据页拆分成两个,且每个页只有一半是有数据的。否则,将要动用更多的页。SQLServer会尝试在同一级别的数据页中,优先从右边插入新数据。
当有足够的空间存放新数据时,进程不需要等待页面拆分和I/O完成。这里有一个很重要的开销,页面拆分会锁住整个页,拒绝其他操作使用这个页,直到进程完成。
此时,填充因子就成为关注点。填充因子决定你的页在初始化情况。假设你填10,那么你的数据页将在10%填满的时候就会分页。当你超过10%时,会保持90%的页面空余,然后创建新页给其他数据。
现在,当你插入新数据时,不用担心由于也分裂导致I/O开销,因为你有90%的空间用于存放新数据,所以如果你的填充因子是一个小值,可以降低由于页分裂而造成的I/O开销,有助于你更快地写数据。
总之,填充因子是一个双刃剑,合理的使用能提高性能。对写入操作的性能提升往往会对读取操作的性能有降低效果。填充因子仅仅在创建或重建一个索引时有影响,在日常的DML操作中,页依然会100%填满。
准备工作:
在学习索引的填充因子如何设置钱,有必要找出当前的填充因子值,以便决定当前的填充因子是否适用和应该使用什么新的值来代替,这些需要根据实际环境而定。这里有两部分需要检查的:
1、 sys.indexes:这个目录视图可以用于查看当前填充因子:
- --查看索引的填充因子
- SELECT OBJECT_NAME(object_id) AS TableName ,
- name AS IndexName ,
- type_desc ,
- fill_factor
- FROM sys.indexes
- WHERE type_desc <> 'HEAP'
2、 sys.configurations:这个目录视图可以用于查看服务器上的默认填充因子的值:
- SELECT [Description] ,
- value_in_use
- FROM sys.configurations
- WHERE name = 'fill factor (%)'
如果默认值为0,代表不会保留任何空余空间在页上。
分析:
当你决定填充因子需要改变的时候,可以使用以下脚本来更改:
- --把填充因子修改成%
- ALTER INDEX [idx_refno] ON [dbo].[ordDemo]
- REBUILD WITH (FILLFACTOR=80)
- GO
- --修改服务器的默认填充因子的值:
- sp_configure 'show advanced options',1
- GO
- RECONFIGURE
- GO
- --改成%
- sp_configure 'fill factor',90
- GO
- RECONFIGURE
- GO
当填充因子为90的时候,在数据页初始化的时候,会有90%的空间初始化填满,只保留10%的空间供以后使用,如果执行DML命令时,会找到空间给新数据插入,从而避免分页的I/O开销。