SQLServer2008R2的遍历所有表更新统计信息和索引重建

桃扇骨 2022-12-27 08:00 107阅读 0赞

【1.以下是索引重建】

项目升级数据库由SQL2000升级到2008R2，今天对数据库表进行碎片扫描，发现有些表碎片较大，于是决定重建索引，联机帮助是最好的老师，将相关脚本摘录备后查。

参考sys.dm\_db\_index\_physical\_stats

SELECT

OBJECT\_NAME(object\_id) as objectname,

object\_id AS objectid,

index\_id AS indexid,

partition\_number AS partitionnum,

avg\_fragmentation\_in\_percent AS fra

FROM sys.dm\_db\_index\_physical\_stats (DB\_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg\_fragmentation\_in\_percent > **10.0** AND index\_id > **0**;

使用脚本中的 sys.dm\_db\_index\_physical\_stats 重新生成或重新组织索引 （**来源于联机帮助**）

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(**130**);

DECLARE @objectname nvarchar(**130**);

DECLARE @indexname nvarchar(**130**);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(**4000**);

\-- Conditionally select tables and indexes from the sys.dm\_db\_index\_physical\_stats function

\-- and convert object and index IDs to names.

SELECT

object\_id AS objectid,

index\_id AS indexid,

partition\_number AS partitionnum,

avg\_fragmentation\_in\_percent AS frag

INTO \#work\_to\_do

FROM sys.dm\_db\_index\_physical\_stats (DB\_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg\_fragmentation\_in\_percent > **10.0** AND index\_id > **0**;

\-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT \* FROM \#work\_to\_do;

\-- Open the cursor.

OPEN partitions;

\-- Loop through the partitions.

WHILE (**1**=**1**)

BEGIN;

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF **@@FETCH\_STATUS** < **0** BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema\_id = o.schema\_id

WHERE o.object\_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE  object\_id = @objectid AND index\_id = @indexid;

SELECT @partitioncount = count (\*)

FROM sys.partitions

WHERE object\_id = @objectid AND index\_id = @indexid;

\-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag < **30.0**

SET @command = N'ALTER INDEX ' \+ @indexname \+ N' ON ' \+ @schemaname \+ N'.' \+ @objectname \+ N' REORGANIZE';

IF @frag >= **30.0**

SET @command = N'ALTER INDEX ' \+ @indexname \+ N' ON ' \+ @schemaname \+ N'.' \+ @objectname \+ N' REBUILD';

IF @partitioncount > **1**

SET @command = @command \+ N' PARTITION=' \+ CAST(@partitionnum AS nvarchar(**10**));

EXEC (@command);

PRINT N'Executed: ' \+ @command;

END;

\-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

\-- Drop the temporary table.

DROP TABLE \#work\_to\_do;

【2.以下是更新统计信息】

DECLARE UpdateStatisticsTables CURSOR READ\_ONLY FOR

SELECT sst.name,

Schema\_name(sst.schema\_id)

FROM   sys.tables sst

WHERE  sst.TYPE = 'U'

DECLARE @name   VARCHAR(**80**),

@schema VARCHAR(**40**)

OPEN UpdateStatisticsTables

FETCH NEXT FROM UpdateStatisticsTables INTO @name, @schema

WHILE ( **@@FETCH\_STATUS** <> \-**1** )

BEGIN

IF ( **@@FETCH\_STATUS** <> \-**2** )

BEGIN

DECLARE @sql NVARCHAR(**1024**)

SET @sql='UPDATE STATISTICS ' \+ Quotename(@schema)

'.' \+ Quotename(@name)

EXEC Sp\_executesql @sql

END

FETCH NEXT FROM UpdateStatisticsTables INTO @name, @schema

END

CLOSE UpdateStatisticsTables

DEALLOCATE UpdateStatisticsTables

UPDATE STATISTICS tblCompany  -- 表名

USE tblCompany; -- 数据库名

EXEC sp\_updatestats -- 更新所有表统计信息

SQLServer2008R2的遍历所有表更新统计信息和索引重建

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle 对表重建索引,重建特定Oracle表的所有常规索引

相关 SQLServer2008R2的遍历所有表更新统计信息和索引重建

相关 SQLServer2008 R2大数据表的分区实现

相关 SQLserver2008R2之登录篇

相关重建所有表的索引的存储过程

相关 Windows Server 2008 Enterprise R2 & SQLServer 2008 Enterprise R2群集安装步骤

相关 SQLServer 2008 r2下载及安装

相关 sqlServer2005升级到sqlServer2008R2

相关 sqlserver 2008r2 执行代码快捷键

相关 sqlserver2008r2 还原bak文件