ITPub博客

首页 > 数据库 > SQL Server > 索引及统计信息2

索引及统计信息2

原创 SQL Server 作者:starive 时间:2015-05-12 11:36:22 0 删除 编辑

五  索引维护

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);

--查看平均碎片
  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);

  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')

碎片情况如图:

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


聚集索引引起碎片

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

  2. select index_type_desc,
  3. index_depth,
  4. index_level,
  5. page_count,
  6. record_count,
  7. CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
  8. fragment_count,
  9. avg_fragment_size_in_pages,
  10. CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
  11. 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

  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')




(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

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

  13. select index_type_desc,
  14. index_depth,
  15. index_level,
  16. page_count,
  17. record_count,
  18. CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
  19. fragment_count,
  20. avg_fragment_size_in_pages,
  21. CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
  22. 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

  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.update_fr'),NULL,NULL,'DETAILED')



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


创建一个非聚集索引

点击(此处)折叠或打开

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

  2. select index_type_desc,
  3. index_depth,
  4. index_level,
  5. page_count,
  6. record_count,
  7. CAST(avg_fragmentation_in_percent as decimal(6,2)) as avg_fragmentation_in_percent,
  8. fragment_count,
  9. avg_fragment_size_in_pages,
  10. CAST(avg_page_space_used_in_percent as decimal(6,2)) as avg_page_space_used_in_percent
  11. 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

  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.update_fr'),NULL,NULL,'DETAILED')









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

上一篇: 索引及统计信息1
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2014-01-18

  • 博文量
    143
  • 访问量
    492247