MSSQL数据库表索引碎片整理优化性能,索引阐述系

日期:2019-09-19编辑作者:数据大全
-- 创建聚集索引
create table [dbo].[pub_stocktest] add  constraint [pk_pub_stocktest] primary key clustered 
(
[sid] asc
)with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, 
online = off, allow_row_locks = on, allow_page_locks = on) on [primary]

-- 创建非聚集索引
 create nonclustered index [ix_model] on [dbo].[pub_stocktest]
(
    [model] asc
)
include (     [name]) with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, drop_existing = off, 
online = off, allow_row_locks = on, allow_page_locks = on, FILLFACTOR = 85) on [primary]

澳门金莎娱乐手机版 1在SQLServer数据库,通过DBCC ShowContig或DBCC ShowContig(表名)检查索引碎片情况,指导我们对其进行定时重建整理。

DBCC SHOWCONTIG是显示指定的表的数据和索引的碎片信息。

当索引所在页面的基于主关键字的逻辑顺序,和数据文件中的物理顺序不匹配时,碎片就产生了。所有的叶级页包含了指向前一个和后一个页的指针。这样就形成一个双链表。理想情况下,数据文件中页的物理顺序会和逻辑顺序匹配。整个磁盘的工作性能在物理顺序匹配逻辑顺序时将显著提升。对某些特定的查询而言,这将带来极佳的性能。当物理排序和逻辑排序不匹配时,磁盘的工作性能会变得低效,这是因为磁头必须向前和向后移动来查找索引,而不是只象某个单一方向来搜索。碎片会影响I/O性能,不过对于位于SQL Server数据缓冲内的数据页而言,碎片并不会带来任何影响。

1.1 Filefactor参数

运行结果如下:

  如上图所示。

在索引碎片整理前,请确保系统资源的一些问题,比如物理磁盘碎片,不合理的基础结构等因素会给性能带来负面影响,参看KB935089:使用 Defrag 管理器可以 such as Exchange Server 或 SQL Server 数据库服务器的卷进行碎片整理。
DBCC SHOWCONTIG是显示指定的表的数据和索引的碎片信息。当运行该命令时,要特别注意逻辑碎片(Logical Fragmentation)和页密度(Page Density)两个指标。 

  使用Filefactor可以对索引的每个叶子分页存储保留一些空间。对于聚集索引,叶级别包含了数据,使用Filefactor来控制表的保留空间,通过预留的空间,避免了新的数据按顺序插入时,需腾出空位而进行分页分隔。
  Filefactor设置生效注意,只有在创建索引时才会根据已经存在的数据决定预留的空间大小,如里需要可以alter index重建索引并重置原来指定的Filefactor值。
  在创建索引时,如果不指定Filefactor,就采用默认值0 也就是填充满,可通过sp_configure 来配置全局实例。Filefactor也只就用于叶子级分页上。如果要在中间层控制索引分页,可以通过指定pad_index选项来实现.该选择会通知到索引上所有层次使用相同的Filefactor。Pad_index也只有索引在新建或重建时有用。

DBCC SHOWCONTIG 正在扫描 'tbModule' 表...
表: 'tbModule'(1845581613);索引 ID: 0,数据库 ID: 9
已执行 TABLE 级别的扫描。
- 扫描页数.....................................: 51
- 扫描扩展盘区数...............................: 9
- 扩展盘区开关数...............................: 8
- 每个扩展盘区上的平均页数.....................: 5.7
- 扫描密度[最佳值:实际值]....................: 77.78%[7:9]
- 扩展盘区扫描碎片.............................: 77.78%
- 每页上的平均可用字节数.......................: 351.1
- 平均页密度(完整)...........................: 95.66%

  解释如下:

Page Scanned-扫描页数:如果你知道行的近似尺寸和表或索引里的行数,那么你可以估计出索引里的页数。看看扫描页数,如果明显比你估计的页数要高,说明存在内部碎片。
Extents Scanned-扫描扩展盘区数:用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高,说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。
Extent Switches-扩展盘区开关数:该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。
Avg. Pages per Extent-每个扩展盘区上的平均页数:该数是扫描页数除以扫描扩展盘区数,一般是8。小于8说明有外部碎片。
Scan Density [Best Count:Actual Count]-扫描密度[最佳值:实际值]:DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100%。低了则说明有外部碎片。

1.2 Drop_existing 参数

相关解释如下:

  Page Scanned-扫描页数:如果你知道行的近似尺寸和表或索引里的行数,那么你可以估计出索引里的页数。看看扫描页数,如果明显比你估计的页数要高,说明存在内部碎片。

Logical Scan Fragmentation-逻辑扫描碎片:无序页的百分比。该百分比应该在0%到10%之间,高了则说明有外部碎片。
Extent Scan Fragmentation-扩展盘区扫描碎片:无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0%,高了则说明有外部碎片。
Avg. Bytes Free per Page-每页上的平均可用字节数:所扫描的页上的平均可用字节数。越高说明有内部碎片,不过在你用这个数字决定是否有内部碎片之前,应该考虑fill factor(填充因子)。
Avg. Page Density (full)-平均页密度(完整):每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片。

  删除或重建一个指定的索引作为单个事务来处理。该项在重建聚集索引时格外有用,当删除一个聚集索引时,sqlserver会重建每个非聚集索引以便将书签从聚集索引键改为RID。如果再新建或者重建聚集索引,Sql server会再一次重建全部的非聚集索引,如果再新建或重建的聚集索引键值相同,可以设置Drop_existing=ON。

Page Scanned-扫描页数:如果你知道行的近似尺寸和表或索引里的行数,那么你可以估计出索引里的页数。看看扫描页数,如果明显比你估计的页数要高,说明存在内部碎片。
Extents Scanned-扫描扩展盘区数:用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高,说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。
Extent Switches-扩展盘区开关数:该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。
Avg. Pages per Extent-每个扩展盘区上的平均页数:该数是扫描页数除以扫描扩展盘区数,一般是8。小于8说明有外部碎片。
Scan Density [Best Count:Actual Count]-扫描密度[最佳值:实际值]:DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100%。低了则说明有外部碎片。

  Extents Scanned-扫描扩展盘区数:用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高,说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。

参考:Microsoft SQL Server 2000 索引碎片整理最佳实践

1.3 IGNORE_DUP_KEY

Logical Scan Fragmentation-逻辑扫描碎片:无序页的百分比。该百分比应该在0%到10%之间,高了则说明有外部碎片。
Extent Scan Fragmentation-扩展盘区扫描碎片:无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0%,高了则说明有外部碎片。
Avg. Bytes Free per Page-每页上的平均可用字节数:所扫描的页上的平均可用字节数。越高说明有内部碎片,不过在你用这个数字决定是否有内部碎片之前,应该考虑fill factor(填充因子)。
Avg. Page Density (full)-平均页密度(完整):每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片。

  Extent Switches-扩展盘区开关数:该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。

       如何将索引碎片数量降至最低

  是指如果一个update或者insert语句影响多行数据,但有一行键被发现产生重值时,整个语句就会回滚,IGNORE_DUP_KEY=on时产生重复键值时不会引起整个语句的回滚,重复的行会被舍弃其它的行会被插入或更新。


  Avg. Pages per Extent-每个扩展盘区上的平均页数:该数是扫描页数除以扫描扩展盘区数,一般是8。小于8说明有外部碎片。

1.4 Statistics_norecompute

澳门金莎娱乐手机版 2通过对扫描密度(过低),扫描碎片(过高)的结果分析,判定是否需要索引重建。

  Scan Density [Best Count:Actual Count]-扫描密度[最佳值:实际值]:DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100%。低了则说明有外部碎片。

  选项决定了是否需要自动更新索引上的统计,每个索引维护着该索引首位字段的数值分布的柱状图,在查询执行计划时,查询优化器利用这些统计信息来判断一个特定索引的有效性。当数据达到一个阀值时,统计值会变。Statistics_norecompute选项允许一个关联的索引在数据修改时不自动更新统计值。该选择覆盖了auto_update_statistics的on值。

处理方式:一是利用DBCC INDEXDEFRAG整理索引碎片,二是利用DBCC DBREINDEX重建索引。二者各有优缺点。

  Logical Scan Fragmentation-逻辑扫描碎片:无序页的百分比。该百分比应该在0%到10%之间,高了则说明有外部碎片。

1.5 ONLINE   

调用微软的原话如下:
DBCC INDEXDEFRAG 命令是联机操作,所以索引只有在该命令正在运行时才可用,而且可以在不丢失已完成工作的情况下中断该操作。这种方法的缺点是在重新组织数据方面没有聚集索引的除去/重新创建操作有效。
重新创建聚集索引将对数据进行重新组织,其结果是使数据页填满。填满程度可以使用 FILLFACTOR 选项进行配置。这种方法的缺点是索引在除去/重新创建周期内为脱机状态,并且操作属原子级。如果中断索引创建,则不会重新创建该索引。也就是说,要想获得好的效果,还是得用重建索引,所以决定重建索引。
DBCC DBREINDEX(表,索引名,填充因子)
第一个参数,可以是表名,也可以是表ID。
第二个参数,如果是'',表示影响该表的所有索引。
第三个参数,填充因子,即索引页的数据填充程度。如果是100,表示每一个索引页都全部填满,此时select效率最高,但以后要插入索引时,就得移动后面的所有页,效率很低。如果是0,表示使用先前的填充因子值。

  Extent Scan Fragmentation-扩展盘区扫描碎片:无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0%,高了则说明有外部碎片。

  值默认OFF, 索引操作期间,基础表和关联的索引是否可用于查询和数据修改操作。
  当值为ON时,能够继续对基础表和索引进行查询或更新,但在短时间内获取sch_m架构修改锁,必须等待此表上的所有阻塞事务完成,在操作期间,此锁会阻止所有其它事务。
  当值为OFF时,可以会获取共享锁,防止更新基础表,但允许读操作

  Avg. Bytes Free per Page-每页上的平均可用字节数:所扫描的页上的平均可用字节数。越高说明有内部碎片,不过在你用这个数字决定是否有内部碎片之前,应该考虑fill factor(填充因子)。

1.6 MAXDOP

--对表tbModule的所有索引进行重建,填充因子比例为80%
DBCC DBREINDEX(tbModule,'',80)  

  Avg. Page Density (full)-平均页密度(完整):每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片。

  索引操作期间替代max degree of parallelism 实例配置,默认值为0, 根据当前系统工作负荷使用实际数量的处理器。

 

1.7 包含性列(included columns)
  包含列只在叶级别中出现,不控制索引行的顺序,它作用是使叶级别包含更多信息从而覆盖索引的调优能力,覆盖索引只出现在非聚集索引中,在叶级别就可以找到满足查询的全部信息。

1.8 on [primary]

  在创建索引时 create index 最后一个子句允许用户指定索引被放置在哪里。可以指定特定的文件组或预定义的分区方案。默认存放与表文件组相同一般都是主文件组中。

澳门金莎娱乐手机版 ,1.9约束和索引

    当我们创建主键或者唯一性约束时,会创建一个唯一性索引,被创建出来支持约束的索引名称与约束名称相同。
  约束是一个逻辑概念,而索引是一个物理概念,建立索引实际是创建一个占用存储空间并且在数据修改操作中必须得到维护的物理结构。
  创建约束就索引内部结构或优化器的选择来看是没有区别的。

二 索引碎片  

  2.1 SHOWCONTIG 

--   SQLserver 2000使用SHOWCONTIG查看索引碎片 (已过时)
dbcc SHOWCONTIG (tablename,'indexname') 

  例如下面查询一个PUB_StockCollect表下的IX_StockModel索引

澳门金莎娱乐手机版 3

  (1)Page Scanned-扫描页数:如果你知道行的近似尺寸和表或索引里的行数,那么你可以估计出索引里的页数。看看扫描页数,如果明显比你估计的页数要高,说明存在内部碎片。

  (2)Extents Scanned-扫描扩展盘区数:用扫描页数除以8,四舍五入到下一个最高值。该值应该和DBCC SHOWCONTIG返回的扫描扩展盘区数一致。如果DBCC SHOWCONTIG返回的数高,说明存在外部碎片。碎片的严重程度依赖于刚才显示的值比估计值高多少。 

  (3)Extent Switches-扩展盘区开关数:该数应该等于扫描扩展盘区数减1。高了则说明有外部碎片。

  (4)Avg. Pages per Extent-每个扩展盘区上的平均页数:该数是扫描页数除以扫描扩展盘区数,一般是8。小于8说明有外部碎片。

  (5)Scan Density [Best Count:Actual Count]-扫描密度[最佳值:实际值]:DBCC SHOWCONTIG返回最有用的一个百分比。这是扩展盘区的最佳值和实际值的比率。该百分比应该尽可能靠近100%。低了则说明有外部碎片。

  (6)Logical Scan Fragmentation-逻辑扫描碎片:无序页的百分比。该百分比应该在0%到10%之间,高了则说明有外部碎片。

  (7)Extent Scan Fragmentation-扩展盘区扫描碎片:无序扩展盘区在扫描索引叶级页中所占的百分比。该百分比应该是0%,高了则说明有外部碎片。

  (8)Avg. Bytes Free per Page-每页上的平均可用字节数:所扫描的页上的平均可用字节数。越高说明有内部碎片,不过在你用这个数字决定是否有内部碎片之前,应该考虑fill factor(填充因子)。

  (9)Avg. Page Density (full)-平均页密度(完整):每页上的平均可用字节数的百分比的相反数。低的百分比说明有内部碎片。

  总结:(1)逻辑扫描碎片:越低越好 (2)平均页密度:80%左右最好,低于%60重建索引,(3)最佳计数与实际计数相差较大重建索引。

本文由澳门金莎娱乐网站发布于数据大全,转载请注明出处:MSSQL数据库表索引碎片整理优化性能,索引阐述系

关键词:

数据类型,Server数据类型char

1.电话字段设置24个Byte竟然不够,好吧设置为50的长度。 alter table 表名 alter column 字段名 新类型名(长度)举例:alter ...

详细>>

SqlServer递归查询

1 DECLARE @ParentId NVARCHAR(36); 2 SET @ParentId = '078499bf-dedd-b293-4653-7bf6a2e54cbb'; 3 4 WITH [Temp] AS 5 ( 6 SELECT [A].* FROM [dbo].[Contacts]...

详细>>

存储过程,Server基础之存储过程【澳门金莎娱乐

一、存储过程概述 SQL Server中的存储过程是使用T_SQL编写的代码段。它的目的在于能够方便的从系统表中查询信息,或...

详细>>

休息日数等日期操作,获取指定日期为本月第几

转自:http://www.maomao365.com/?p=6771 /**//**//**//// summary ///计算两个日期之间的工作日数,(星期6,星期天,不算工作日)d...

详细>>