有关重建索引的问题
现在这边遇到一个情况 数据库中的一张表 每天的数据增长量大概是8千条左右 重建索引的的周期为一周
这样的方案合适不? 是否需要增加重建索引的频率?
[解决办法]
/*
Retrieve Index Fragmentation
Description
Sample script that displays the fragmentation on the current database (most fragmented appears on top).
This script requires SQL Server 2005.
*/
SELECT DB_NAME(a.database_id) as [Database Name]
--OBJECT_NAME(so.id) as [Table Name]
,a.object_id as [Table Object ID]
,b.name as [Index Name]
,a.index_id
,a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id
JOIN sys.sysobjects AS SO
on b.object_id = SO.id
AND a.index_id = b.index_id
where a.database_id = db_id() and SO.xtype = 'U'
order by a.avg_fragmentation_in_percent DESC
GO
用这个语句检测一下,然后根据返回的结果,把那些碎片操过20%的索引重建。
可以制定一个作业执行重建过程