手机
当前位置:查字典教程网 >编程开发 >mssql数据库 >SQL SERVER 2008 R2 重建索引的方法
SQL SERVER 2008 R2 重建索引的方法
摘要:参考sys.dm_db_index_physical_stats检查索引碎片情况1.SELECT2.OBJECT_NAME(object_i...

参考sys.dm_db_index_physical_stats

检查索引碎片情况

1.SELECT 2.OBJECT_NAME(object_id) as objectname, 3.object_id AS objectid, 4.index_id AS indexid, 5.partition_number AS partitionnum, 6.avg_fragmentation_in_percent AS fra 7.FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED') 8.WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; 9. 10.使用脚本中的 sys.dm_db_index_physical_stats 重新生成或重新组织索引 (来源于联机帮助) 11. 12.SET NOCOUNT ON; 13.DECLARE @objectid int; 14.DECLARE @indexid int; 15.DECLARE @partitioncount bigint; 16.DECLARE @schemaname nvarchar(130); 17.DECLARE @objectname nvarchar(130); 18.DECLARE @indexname nvarchar(130); 19.DECLARE @partitionnum bigint; 20.DECLARE @partitions bigint; 21.DECLARE @frag float; 22.DECLARE @command nvarchar(4000); 23.– Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 24.– and convert object and index IDs to names. 25.SELECT 26.object_id AS objectid, 27.index_id AS indexid, 28.partition_number AS partitionnum, 29.avg_fragmentation_in_percent AS frag 30.INTO #work_to_do 31.FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED') 32.WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; 33.– Declare the cursor for the list of partitions to be processed. 34.DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; 35.– Open the cursor. 36.OPEN partitions; 37.– Loop through the partitions. 38.WHILE (1=1) 39.BEGIN; 40.FETCH NEXT 41.FROM partitions 42.INTO @objectid, @indexid, @partitionnum, @frag; 43.IF @@FETCH_STATUS < 0 BREAK; 44.SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) 45.FROM sys.objects AS o 46.JOIN sys.schemas as s ON s.schema_id = o.schema_id 47.WHERE o.object_id = @objectid; 48.SELECT @indexname = QUOTENAME(name) 49.FROM sys.indexes 50.WHERE object_id = @objectid AND index_id = @indexid; 51.SELECT @partitioncount = count (*) 52.FROM sys.partitions 53.WHERE object_id = @objectid AND index_id = @indexid; 54.– 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. 55.IF @frag < 30.0 56.SET @command = N‘ALTER INDEX ‘ + @indexname + N‘ ON ‘ + @schemaname + N‘.' + @objectname + N‘ REORGANIZE'; 57.IF @frag >= 30.0 58.SET @command = N‘ALTER INDEX ‘ + @indexname + N‘ ON ‘ + @schemaname + N‘.' + @objectname + N‘ REBUILD'; 59.IF @partitioncount > 1 60.SET @command = @command + N‘ PARTITION=' + CAST(@partitionnum AS nvarchar(10)); 61.EXEC (@command); 62.PRINT N‘Executed: ‘ + @command; 63.END; 64.– Close and deallocate the cursor. 65.CLOSE partitions; 66.DEALLOCATE partitions; 67.– Drop the temporary table. 68.DROP TABLE #work_to_do; 69.GO

【SQL SERVER 2008 R2 重建索引的方法】相关文章:

SQLServer2005 评估和管理索引

Access 导入到SQL Server 2005的方法小结

SQL Server 2008R2编写脚本时智能提示功能丢失的处理方法

SQL SERVER 2008 无法附加数据库的解决方法

SQL SERVER2000 的一些技巧

SQL Server 2005支持的两条新语法

SQL Server2008中删除重复记录的方法分享

SQLSERVER2005 中树形数据的递归查询

SQL Server 2005删除日志文件的几种方法小结

SQL Server 2005 返回修改后的数据

精品推荐
分类导航