当前位置: > 数据库 > SQL Server 2005 >

Sql Server索引重建

时间:2017-06-05 22:53来源:linux.it.net.cn 作者:IT

公司线上数据有几千万数据,有时候索引碎片会导致索引达不到我们的预期查询效率,这个时候将索引重建将会提升一定效率,不过重建的时候一定得晚上用户少的时候,索引重建需要一定时间。

直接贴自动重建索引脚本吧

 
 1 DECLARE @Database VARCHAR(255);  
 2 DECLARE @Table VARCHAR(255);  
 3 DECLARE @cmd NVARCHAR(500);  
 4 DECLARE @fillfactor INT;
 5 SET @fillfactor = 90;
 6 DECLARE DatabaseCursor CURSOR
 7 FOR
 8     SELECT  name
 9     FROM    master.dbo.sysdatabases
10     WHERE   name IN ( 'ChemicalInfo_ALLFULL_New' )
11     ORDER BY 1;  
12 OPEN DatabaseCursor;  
13 FETCH NEXT FROM DatabaseCursor INTO @Database;  
14 WHILE @@FETCH_STATUS = 0
15     BEGIN  
16         SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
17   table_name + '']'' as tableName FROM [' + @Database
18             + '].INFORMATION_SCHEMA.TABLES
19   WHERE table_type = ''BASE TABLE''';  
20    -- create table cursor  
21         EXEC (@cmd);  
22         OPEN TableCursor;  
23         FETCH NEXT FROM TableCursor INTO @Table;  
24         WHILE @@FETCH_STATUS = 0
25             BEGIN  
26                 IF ( @@MICROSOFTVERSION / POWER(2, 24) >= 9 )
27                     BEGIN
28            -- SQL 2005 or higher command
29                         SET @cmd = 'ALTER INDEX ALL ON ' + @Table
30                             + ' REBUILD WITH (FILLFACTOR = '
31                             + CONVERT(VARCHAR(3), @fillfactor) + ')';
32                         EXEC (@cmd);
33                     END;
34                 ELSE
35                     BEGIN
36           -- SQL 2000 command
37                         DBCC DBREINDEX(@Table,' ',@fillfactor);  
38                     END;
39                 FETCH NEXT FROM TableCursor INTO @Table;  
40             END;  
41         CLOSE TableCursor;  
42         DEALLOCATE TableCursor;  
43         FETCH NEXT FROM DatabaseCursor INTO @Database;  
44     END;  
45 CLOSE DatabaseCursor;  
46 DEALLOCATE DatabaseCursor; 
 


(责任编辑:IT)
------分隔线----------------------------