none
下面sql怎么没作用呢,关于索引重建的 RRS feed

  • 问题

  • set nocount on
    declare r_t cursor
    for select name from sys.tables
    declare @T varchar(50)
    open r_t
    fetch next from r_t into @t
    while @@fetch_status=0
    begin
    	declare R_Index cursor
    		for select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t
    		join sys.indexes i on i.object_id=t.object_id
    		join sys.dm_db_index_physical_stats(db_id(),object_id(@T),null,null,'limited') s
    		on s.object_id=i.object_id and s.index_id=i.index_id
    	declare @TName varchar(100),@IName varchar(100),@avg int,@str varchar(500),@str2 varchar(500)
    	open r_index
    	fetch next from r_index into @TName,@Iname,@avg
    	while @@fetch_status=0
    	begin
    		if @avg>=30 --碎片率大于30,则重建该表的索引
    			begin
    			   set @str='alter index all on dbo.'+rtrim(@tname)+' rebuild'
    			end
    		if @str2<>@str --没有执行过
    			begin
    			   exec @str --执行
    			   print @str
    			   set @str2=@str --记录执行过
    			end
    		fetch next from r_index into @TName,@Iname,@avg
    	end
    	close r_index
    	deallocate r_index
    	fetch next from r_t into @t
    end
    close r_t
    deallocate r_t
    set nocount off
    我执行的时候怎么什么都不打印呢?麻烦各位帮忙看下问题出在哪里。碎片大于30%的索引,确实是有的哦

    开心了就笑,不开心了就过会儿再笑

    2012年4月16日 8:09

答案

  • 之前看得不是很仔细, 既然是整个表做 rebuild, 那么里面只需要判断一下就行了, 不需要循环每个 index 来判断

    参考下面的, 做了一些调整, 测试过的

    set nocount on
    declare r_t cursor
    for select 
    --name 
    QUOTENAME(SCHEMA_NAME(schema_id)) +N'.' + QUOTENAME(name)	-- 考虑 SCHEMA 不一定是 dbo
    from sys.tables
    declare @T sysname, @str nvarchar(4000)
    open r_t
    fetch next from r_t into @t
    while @@fetch_status=0
    begin
    		if EXISTS(
    			SELECT *
    			FROM sys.dm_db_index_physical_stats (
    				db_id(), object_id(@T), NULL, NULL, 'limited'
    			) AS s
    			WHERE avg_fragmentation_in_percent >= 30
    		) --碎片率大于30,则重建该表的索引
    			begin
    			   --set @str='alter index all on dbo.'+rtrim(@T)+' rebuild'
    			   set @str='alter index all on '+rtrim(@T)+' rebuild'
    			   exec(@str) --执行, 要加 括号
    			   print @str
    			end
    
    	fetch next from r_t into @t
    end
    close r_t
    deallocate r_t
    set nocount off
    
    

    2012年4月20日 3:08

全部回复

  • 看起來你是漏掉了在@avg >= 30的時候要去print @str了,改成下列這樣就會打印了。

    set nocount on
    declare r_t cursor
    for select name from sys.tables
    declare @T varchar(50)
    open r_t
    fetch next from r_t into @t
    while @@fetch_status=0
    begin
    	declare R_Index cursor
    		for select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t
    		join sys.indexes i on i.object_id=t.object_id
    		join sys.dm_db_index_physical_stats(db_id(),object_id(@T),null,null,'limited') s
    		on s.object_id=i.object_id and s.index_id=i.index_id
    	declare @TName varchar(100),@IName varchar(100),@avg int,@str varchar(500),@str2 varchar(500)
    	open r_index
    	fetch next from r_index into @TName,@Iname,@avg
    	while @@fetch_status=0
    	begin
    	
    		if @avg>=30 --碎片率大于30,则重建该表的索引
    			begin
    			   set @str='alter index all on dbo.'+rtrim(@tname)+' rebuild'
    			   print @str 
    			end
    		if @str2<>@str --没有执行过
    			begin
    			   exec @str --执行
    			   print @str
    			   set @str2=@str --记录执行过
    			end
    		fetch next from r_index into @TName,@Iname,@avg
    	end
    	close r_index
    	deallocate r_index
    	fetch next from r_t into @t
    end
    close r_t
    deallocate r_t
    set nocount off


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/

    2012年4月16日 8:48
  • 		if @str2<>@str --没有执行过
    			begin
    			   exec @str --执行
    			   print @str
    			   set @str2=@str --记录执行过
    			end
    		fetch next from r_index into @TName,@Iname,@avg

    可是if @str2<>@str这句里面有的啊,应该会执行的。在上个if里面给仅仅给@str赋值了,没有给@str2赋值,所以,下面的if肯定会执行的呀,麻烦你从逻辑上帮我分析下,看看问题在哪里。

    我主要是想,通过判断碎片率,如果碎片率大于30,那么对这个表整表进行所有索引重建一次,只是一次


    开心了就笑,不开心了就过会儿再笑

    2012年4月17日 1:59
  • 不好意思,我以為你只是要印出@str,我調整了一下你的T-SQL,發現問題是出在你沒給@str2的初始值,造成初始值變成null,我只修改粗體字的部分,就可以順利執行你的T-SQL了。

    set nocount on
    declare r_t cursor
    for select name from sys.tables
    declare @T varchar(50)
    open r_t
    fetch next from r_t into @t
    while @@fetch_status=0
    begin
    	declare R_Index cursor
    		for select t.name,i.name,s.avg_fragmentation_in_percent from sys.tables t
    		join sys.indexes i on i.object_id=t.object_id
    		join sys.dm_db_index_physical_stats(db_id(),object_id(@T),null,null,'limited') s
    		on s.object_id=i.object_id and s.index_id=i.index_id
    	declare @TName varchar(100),@IName varchar(100),@avg int,@str varchar(500)
    	,@str2 varchar(500) = ''
    	open r_index
    	fetch next from r_index into @TName,@Iname,@avg
    	while @@fetch_status=0
    	begin
    	
    		if @avg>=30 --碎片率大于30,则重建该表的索引
    			begin
    			   set @str='alter index all on dbo.'+rtrim(@tname)+' rebuild'
    			end
    		if @str2<>@str --没有执行过
    			begin
    			   exec (@str) --执行
    			   print @str
    			   set @str2=@str --记录执行过
    			end
    		fetch next from r_index into @TName,@Iname,@avg
    	end
    	close r_index
    	deallocate r_index
    	fetch next from r_t into @t
    end
    close r_t
    deallocate r_t
    set nocount off


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/

    2012年4月17日 2:14
  • 原来的脚本是因为 @str2 没有初始化导致的

    其实, 把执行和 PRINT 的操作都放在 if @avg>=30 中就行了吧, 没必要把脚本复杂化

    或者是每次循环开始或结束时把 @str 初始化为人, 执行的时候判断 @str 不为空则执行,这样也简化了逻辑

    2012年4月17日 4:43
  • 仁兄的这种做法, 我之前试过,刚才又把你的代码粘贴过来试试,还是有错,不能向局部变量赋予默认值

    开心了就笑,不开心了就过会儿再笑

    2012年4月20日 1:57
  • 貌似你没有太明白我的意思。

    我是想,if @avg>=30,那么就对这个表上的所有索引进行重建。那么在循环下个@avg时,肯定会再次出现这个表的索引要重建的情况,而我就是想让一个表,它的索引只重建一次


    开心了就笑,不开心了就过会儿再笑

    2012年4月20日 2:18
  • 变量在定义中初始化, 这个是 2008 才开始扶持的, 你可以在循环前, 使用 SET 语句初始化一下就行, 不在定义中初始化

    2012年4月20日 2:51
  • 之前看得不是很仔细, 既然是整个表做 rebuild, 那么里面只需要判断一下就行了, 不需要循环每个 index 来判断

    参考下面的, 做了一些调整, 测试过的

    set nocount on
    declare r_t cursor
    for select 
    --name 
    QUOTENAME(SCHEMA_NAME(schema_id)) +N'.' + QUOTENAME(name)	-- 考虑 SCHEMA 不一定是 dbo
    from sys.tables
    declare @T sysname, @str nvarchar(4000)
    open r_t
    fetch next from r_t into @t
    while @@fetch_status=0
    begin
    		if EXISTS(
    			SELECT *
    			FROM sys.dm_db_index_physical_stats (
    				db_id(), object_id(@T), NULL, NULL, 'limited'
    			) AS s
    			WHERE avg_fragmentation_in_percent >= 30
    		) --碎片率大于30,则重建该表的索引
    			begin
    			   --set @str='alter index all on dbo.'+rtrim(@T)+' rebuild'
    			   set @str='alter index all on '+rtrim(@T)+' rebuild'
    			   exec(@str) --执行, 要加 括号
    			   print @str
    			end
    
    	fetch next from r_t into @t
    end
    close r_t
    deallocate r_t
    set nocount off
    
    

    2012年4月20日 3:08
  • 谢谢,我先测试下

    开心了就笑,不开心了就过会儿再笑

    2012年4月20日 4:20