August 11, 2009 at 2:20 pm
I have over 450 indexes I want to rebuild. What is the best approach for this task? Any help or guidance will be greatly appreciated.
August 12, 2009 at 8:15 am
I pretty much always point people to Michelle Ufford's Index Defrag Script[/url] for index maintenance.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 13, 2009 at 3:33 pm
Thanks for the link. I have the "Rebuild Index" job setup on a maintenance plan, but I still have a lot of fragmented indexs.
March 26, 2010 at 11:10 am
Jack,
I feel like I've asked this before (of you) but I can;t infd a story or forum to that effectw ehn doing a search so if I have and have forgot then apolagies.
I've looked at Uffords script before about 6 months to a year ago and at the time I had problems because my database, while running on SQL Server 2005 is set to a compatability mode of SQL 2000(8.0) so I have to tweak some of the script code namely the use of Object_ID() when passed to the dm_db_index_physical_stats . I juct checked and tried the most recent version of her script and even after exachnging Object_ID() with the actual Index ID I still can;t get her script to work. It doesn't error out but instead just does not do anything except for create the table listed in the script.
I don;t even get any messages when setting it to debug mode.
Have you ever heard of anyone having similiar isues with it?
Thanks
Kindest Regards,
Just say No to Facebook!March 26, 2010 at 1:04 pm
Never used that on SQL Server 2000 or 2000 mode DB's. I can try something this afternoon.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2010 at 11:26 am
Jack Corbett (3/26/2010)
Never used that on SQL Server 2000 or 2000 mode DB's. I can try something this afternoon.
Thanks. I knwo your as busy as the rest of us and this isn't anything time sensative so just if you get a chance whenever and don;t mind sharing that would be great.
What realy sucks about this is I just checked (the otherday) on the software vendors website to see what new version we will have to upgrade to in order to get official support for using SQL Server 2008 or for at least setting the DB Compatability to 2005 if not 2008 and even with the next major build of their product which is probably end of this year to start of next year from being ready for general use, they aren't supporting anything higher then SQL Server 2005 with DB compatability at 8.0. This is what you get when a software company whos product relies hevaily on a RDBMS, is convinced they don't need any formally trained ro certified DB anything to help with tehir product development/growth.
Amazing i tell you, abso-freakingly amazing.
Kindest Regards,
Just say No to Facebook!May 21, 2010 at 1:35 am
--descript:cycle all the index in all talbe when then fragment is too big it will built a sql to rebuild or reorganize it
declare @tablename nvarchar(50)
declare @idx_id int,@idx_name nvarchar(50),@idx_fragment float
declare @cmd nvarchar(1000)
declare CurTable cursor for
select name
from sys.tables;
open CurTable
fetch next from CurTable into @tablename
IF @@FETCH_STATUS <> 0
PRINT 'no table'
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE CurFragment CURSOR FOR
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@tablename),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
OPEN CurFragment
FETCH NEXT FROM CurFragment INTO @idx_id,@idx_name,@idx_fragment
IF @@FETCH_STATUS <> 0
PRINT @tablename +' no index'
WHILE @@FETCH_STATUS = 0
BEGIN
if @idx_fragment>30
set @cmd='alter index '+@idx_name+' on '+@tablename+' REBUILD WITH (ONLINE = ON)';
else
if @idx_fragment>20
set @cmd='alter index '+@idx_name+' on '+@tablename+' reorganize ';
else
begin
set @cmd='';
end
print @tablename + ' '+@idx_name +' '+cast(@idx_fragment as nvarchar)
PRINT @cmd
exec(@cmd);
FETCH NEXT FROM CurFragment INTO @idx_id,@idx_name,@idx_fragment
END --end for index while
CLOSE CurFragment
DEALLOCATE CurFragment
fetch next from CurTable into @tablename
END --end for table while
CLOSE CurTable
DEALLOCATE CurTable
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply