彩世界平台-彩世界时时app-彩世界开奖app苹果下载

热门关键词: 彩世界平台,彩世界时时app,彩世界开奖app苹果下载

您的位置:彩世界平台 > 工作委员会 > sql server 索引演讲系列五 索引参数与零散

sql server 索引演讲系列五 索引参数与零散

发布时间:2019-09-05 22:27编辑:工作委员会浏览(110)

    一 . dm_db_index_physical_stats 重要字段说明

      1.1 内部碎片:是avg_page_space_used_in_percent字段。是指页的填充度,为了使磁盘使用状况达到最优,对于没有很多随机插入的索引,此值应接近 100%。 但是,对于具有很多随机插入且页很满的索引,其页拆分数将不断增加。 这将导致更多的碎片。 因此,为了减少页拆分,此值应小于 100%。

      1.2 外部碎片:也叫逻辑碎片是avg_fragmentation_in_percent字段。是分页的逻辑顺序和物理顺序不匹配或者索引拥有的扩展不连续时产生。当对表中定义的索引进行数据修改(INSERT、UPDATE 和 DELETE 语句)的整个过程中都会出现碎片。 由于这些修改通常并不在表和索引的行中平均分布,所以每页的填充度会随时间而改变。 对于扫描表的部分或全部索引的查询,这种碎片会导致额外的页读取。 这会妨碍数据的并行扫描。

      1.3 使用查看dm_db_index_physical_stats索引碎片 (SQL server 2005以上)。

    SELECT OBJECT_NAME(sys.indexes.OBJECT_ID) AS tableName,
     sys.indexes.name,   
     page_count,
     (page_count*8.0)AS 'IndexSizeKB',
     avg_page_space_used_in_percent,
     avg_fragmentation_in_percent,
     record_count,avg_record_size_in_bytes,
    index_type_desc,
    fragment_count 
    from sys.dm_db_index_physical_stats(db_id('dbname'),object_id('tablename'), null,null,'sampled') 
     JOIN sys.indexes  ON   sys.indexes.index_id = sys.dm_db_index_physical_stats.index_id
     AND sys.indexes.object_id = sys.dm_db_index_physical_stats.object_id
    

        下面还是接着上一篇查询PUB_StockCollect表下的索引

    图片 1

      (1) avg_fragmentation_in_percent(外部碎片也叫逻辑碎片):最重要的列,索引碎片百分比。
        val >10% and val<= 30% -------------索引重组(碎片整理) alter index reorganize )
        val >30% --------------------------索引重建 alter index rebulid with (online=on)
        avg_fragmentation_in_percent:大规模的碎片(当碎片大于40%),可能要求索引重建
      (2) page_count:索引或数据页的总数。
      (3) avg_page_space_used_in_percent(内部碎片):最重要列:页面平均使用率也叫存储空间的平均百分比, 值越高(以80%填充度为参考点) 页存储数据就越多,内部碎片越少。
      (4) avg_record_size_in_bytes:平均记录大小(字节)。
      (5) index_type_desc列:索引类型-聚集索引或者非聚集索引等。
      (6) record_count:总记录数,相当于行数。
      (7) fragment_count: 碎片数。

    -- 创建聚集索引
    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]
    

    二. 解决碎片方法

    -------------sqlserver 2000 碎片解决--------------
    -- 索引重建 充填因子80
    dbcc dbreindex(PUB_StockCategory,'PK_PUB_StockCategory',80)
    -- 索引重组
    DBCC INDEXDEFRAG(dbname,PUB_StockCategory,'PK_PUB_StockCategory')
    

     

    ------------sqlserver 2005以上碎片解决--------
    -- 重新组织表中单个索引 
     ALTER INDEX ix_pub_stock_2 ON dbo.PUB_Stock REORGANIZE  
     -- 重新组织表中的所有索引
     ALTER INDEX ALL ON dbo.PUB_Stock REORGANIZE  
     -- 重新生成表中单个索引 (重点:重建索引用)
     ALTER INDEX ix_pub_stock_2 ON dbo.PUB_Stock REBUILD
     -- 重新生成表中的所有索引 
     ALTER INDEX ALL  ON dbo.PUB_Stock  
     REBUILD  WITH(FILLFACTOR=80, SORT_IN_TEMPDB=ON ,STATISTICS_NORECOMPUTE = ON )
    

    1.1 Filefactor参数

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

    1.2 Drop_existing 参数

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

    1.3 IGNORE_DUP_KEY

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

    1.4 Statistics_norecompute

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

    1.5 ONLINE   

    本文由彩世界平台发布于工作委员会,转载请注明出处:sql server 索引演讲系列五 索引参数与零散

    关键词:

上一篇:T-SQL—驾驭CTEs

下一篇:没有了