索引碎片监控

----------ana_index.sql

SET NOCOUNT ON
--SET QUOTED_IDENTIFIER OFF
DECLARE @sql NVARCHAR(MAX)
DECLARE @db_name VARCHAR(30)
DECLARE @object_name VARCHAR(30)
DECLARE @db_id INT
DECLARE @object_id INT
DECLARE @index_id FLOAT
DECLARE @arg_used FLOAT
DECLARE @record_count INT
DECLARE @date varchar(10)
SET @date=CONVERT(VARCHAR(10),GETDATE(),120)
CREATE TABLE #cur_space(DB_NAME VARCHAR(30),OBJECT_NAME VARCHAR(30),INDEX_ID INT,AVG_USED FLOAT,RECORD_COUNT INT,date VARCHAR(10))

DECLARE cur CURSOR FOR SELECT database_id,OBJECT_ID,index_id,avg_page_space_used_in_percent,record_count FROM sys.dm_db_index_physical_stats
    (NULL, NULL, NULL, NULL , 'DETAILED') WHERE database_id >=5 AND avg_page_space_used_in_percent < 70.0 AND index_level =0 AND record_count > 5000 ORDER BY avg_page_space_used_in_percent DESC ;
OPEN cur
     FETCH NEXT FROM cur INTO @db_id,@object_id,@index_id,@arg_used,@record_count   
     WHILE @@FETCH_STATUS = 0
     BEGIN
     SELECT @db_name=DB_NAME(@db_id)
 SET @sql='SELECT @object_names=NAME FROM '+@db_name+'.sys.objects WHERE object_id='+CONVERT(VARCHAR,@object_id)
 EXEC sp_executesql @sql,N'@object_names varchar(30) output',@object_name output
--    PRINT @object_name
     INSERT INTO #cur_space
             ( DB_NAME ,
      OBJECT_NAME ,
               INDEX_ID ,
               RECORD_COUNT ,
               AVG_USED,
               date
             )
     VALUES  (  @db_name,
    @object_name,
    @index_id,
    @record_count,
    @arg_used,
    @date
             )
--    
     FETCH NEXT FROM cur INTO @db_id,@object_id,@index_id,@arg_used,@record_count
    
     END
    
     CLOSE cur
     DEALLOCATE cur 
     SELECT * FROM #cur_space
     SET NOCOUNT OFF
    
  DROP TABLE #cur_space
 
  SET NOCOUNT OFF

 

-------------------以上是sql语句，下面是调用这个语句的批处理程序。记得一定要把这个语句保存到指定的路径 每周可以执行一次批处理程序，查看索引碎片的情况，对于索引碎片大的表执行索引重建 或采取其他方法。

-----------------index_rebult.bat

set script_file=E:/JOB/优化资料/ana_index.sql
set temp_result=D:/temp_index.txt
set result=D:/rebult_index.txt
isql -U sa -P****** -SHS-HXDONG -n -i %script_file% -o %temp_result% -w5000 -s"|"
echo 分析时间 ************** %date:~0,19% %time:~0,8%************* >> %result%
type %temp_result% >> %result%
del %temp_result%