索引及统计信息2

于 2015-05-12 11:36:22 发布
阅读量229 收藏
点赞数

五  索引维护

5.1 索引碎片

5.1.1 产生碎片的操作
碎片的问题主要是通过 sys.dm_db_index_physical_stats来查看的。
(1)  插入操作
insert 操作在 聚集索引和 非聚集索引上都可能引起碎片

聚集索引引起碎片

  1. if object_id('dbo.Table_GUID') IS NOT NULL
  2. drop table dbo.Table_GUID;
  3. create table dbo.table_guid(
  4. rowid uniqueidentifier constraint df_guidvalue default newid(),
  5. name sysname,
  6. value varchar(2000)
  7. );
--插入数据，注意此时还没有聚集索引
  1. insert into dbo.table_guid(name,value) select name,REPLICATE('x',2000)
  2. from sys.columns

--在列上创建聚集索引
  1. create clustered index clus_usinguniqueidentifier on dbo.table_guid(rowid);

  2.
--查看平均碎片
  1. select index_type_desc,
  2. index_depth,
  3. index_level,
  4. page_count,
  5. record_count,
  6. CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
  7. fragment_count,
  8. avg_fragment_size_in_pages,
  9. CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
  10. from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.table_guid'),NULL,NULL,'DETAILED')

这时从结果可以看到，平均碎片为 0， 因为在插入后才建索引。


然后执行插入操作。并查看索引碎片。

  1. insert into dbo.table_guid(name,value) select name,REPLICATE('x',2000)
  2. from sys.columns
  3. create clustered index clus_usinguniqueidentifier on dbo.table_guid(rowid);

  5. select index_type_desc,
  6. index_depth,
  7. index_level,
  8. page_count,
  9. record_count,
  10. CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
  11. fragment_count,
  12. avg_fragment_size_in_pages,
  13. CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
  14. from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.table_guid'),NULL,NULL,'DETAILED')

碎片情况如图：

从上图可以看出，碎片在迅速增长。


非 聚集索引引起碎片

  1. create nonclustered index IX_Name on dbo.table_guid(Name) include(value);

  3. select index_type_desc,
  4. index_depth,
  5. index_level,
  6. page_count,
  7. record_count,
  8. CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
  9. fragment_count,
  10. avg_fragment_size_in_pages,
  11. CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
  12. from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.table_guid'),NULL,NULL,'DETAILED')

查看碎片


插入数据后，再次查看：

点击(此处)折叠或打开

  1. insert into dbo.table_guid(name,value) select name,REPLICATE('x',2000)
  2. from sys.OBJECTS

  4. select index_type_desc,
  5. index_depth,
  6. index_level,
  7. page_count,
  8. record_count,
  9. CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
  10. fragment_count,
  11. avg_fragment_size_in_pages,
  12. CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
  13. from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.table_guid'),NULL,NULL,'DETAILED')




(2)  更新操作
update操作也会引起碎片

点击(此处)折叠或打开

  1. if OBJECT_ID('dbo.Update_Fr') IS NOT NULL
  2. drop table dbo.Update_Fr;
  3. create table dbo.Update_Fr(
  4. rowid int identity(1,1),
  5. name sysname,
  6. value varchar(2000)
  7. );
  8. insert into dbo.Update_Fr(
  9. name,value)
  10. select name ,REPLICATE('x',1000)
  11. from sys.columns

  13. create clustered index clus_usingUniqueidentifier on dbo.update_fr(rowid);

  15. select index_type_desc,
  16. index_depth,
  17. index_level,
  18. page_count,
  19. record_count,
  20. CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
  21. fragment_count,
  22. avg_fragment_size_in_pages,
  23. CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
  24. from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.update_fr'),NULL,NULL,'DETAILED')



接下来更新数据，让长度变长。

点击(此处)折叠或打开

  1. update dbo.update_fr
  2. set value=replicate('x',2000)
  3. where rowid%5=1

  5. select index_type_desc,
  6. index_depth,
  7. index_level,
  8. page_count,
  9. record_count,
  10. CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
  11. fragment_count,
  12. avg_fragment_size_in_pages,
  13. CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
  14. from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.update_fr'),NULL,NULL,'DETAILED')



可以看出，碎片增长非常快。


创建一个非聚集索引

点击(此处)折叠或打开

  1. create nonclustered index ix_name on dbo.update_fr(name) include (value)

  3. select index_type_desc,
  4. index_depth,
  5. index_level,
  6. page_count,
  7. record_count,
  8. CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
  9. fragment_count,
  10. avg_fragment_size_in_pages,
  11. CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
  12. from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.update_fr'),NULL,NULL,'DETAILED')




通过 reverse函数把名称反转，监控更新前后的碎片情况

点击(此处)折叠或打开

  1. update dbo.update_fr
  2. set name=reverse(name)
  3. where rowid%9=1

  5. select index_type_desc,
  6. index_depth,
  7. index_level,
  8. page_count,
  9. record_count,
  10. CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
  11. fragment_count,
  12. avg_fragment_size_in_pages,
  13. CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
  14. from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.update_fr'),NULL,NULL,'DETAILED')









来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26435490/viewspace-1650047/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26435490/viewspace-1650047/

cncz1234
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值