sql server索引碎片的问题

Question

sql server如果只对日志文件进行压缩，而不对数据文件进行压缩，那也会产生索引碎片吗？

Answer 1

你好，

如果只对日志文件压缩的话， 不会对索引碎片数有影响。 但你再次向该数据库存在索引的表插入数据时， 就会增加碎片。

 -- Name of the Database and Size
 SELECT name, (size*8) Size_KB
 FROM sys.database_files
 GO
 -- Check Fragmentations in the database
 SELECT avg_fragmentation_in_percent, fragment_count
 FROM sys.dm_db_index_physical_stats (DB_ID(), null, NULL, NULL, 'LIMITED')
 GO
SELECT object_name(object_id),index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), null, NULL, NULL, 'DETAILED')
where avg_fragmentation_in_percent>10 AND avg_page_space_used_in_percent<75 
GO
-- Shrink the log file 
 DBCC SHRINKFILE (database_log, 1);
 GO
 -- Name of the Database and Size
 SELECT name, (size*8) Size_KB
 FROM sys.database_files
 GO
 -- Check Fragmentations in the database
SELECT avg_fragmentation_in_percent, fragment_count
FROM sys.dm_db_index_physical_stats (DB_ID(), null, NULL, NULL, 'LIMITED')
GO
SELECT object_name(object_id),index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), null, NULL, NULL, 'DETAILED')
where avg_fragmentation_in_percent>10 AND avg_page_space_used_in_percent<75 
GO

参考：http://blog.sqlauthority.com/2011/01/19/sql-server-shrinking-database-is-bad-increases-fragmentation-reduces-performance/　

Best Regards,

---

立刻免费下载TechNet论坛好帮手

Answer 2

sql server如果只对日志文件进行压缩，而不对数据文件进行压缩，那也会产生索引碎片吗？

It will not cause table fragmentation but shrinking log file has performance hit due to disk io, and sql will expand log file again when needed that has performance hit too.

Answer 3

正如两位大侠所说是不会产生碎片的，而我们一般都是通过备份事务日志来截断事务日志而不是通过压缩事务日志

---

给我写信： QQ我：