FillFactors

Question

Post reply

FillFactors

Carl Walden

SSC-Addicted

Points: 411
More actions
March 4, 2004 at 10:40 am

#160637

I have a new DB with over 560 tbls and I need to create a fillfactor is there any script that I can ran to set-up the whole 560 tbls to a fiifactor for 90? I will surely appricate your help.
Thanks

CEWalden

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

chrisleonard SSCertifiable Points: 5030 More actions · Answer 1

Hello Walden,

Here's one way. Have fun!

declare curIndexRebuild cursor
for
select N'dbcc dbreindex (''' + db_name() + N'.'
     + user_name(t.uid) + N'.' + object_name(i.id)
     + N''', ' + i.name + N', 90)'
from   sysindexes i join sysobjects t
on     i.id = t.id
where indid between 1 and 254
and    indexproperty(t.id, i.name, 'IsStatistics') = 0
and    t.type != 'S'
declare @cmd nvarchar(4000)
declare @msg nvarchar(4000)
open curIndexRebuild
fetch next from curIndexRebuild into @cmd
while @@fetch_status != -1
begin
   /* use raiserror with nowait to monitor progress: */
   set @msg = 'About to execute ' + @cmd + '...'
   raiserror (@msg, 10, 1) with nowait
   exec (@cmd)
   fetch next from curIndexRebuild into @cmd
end
deallocate curIndexRebuild
go

Cheers,

Chris

Hendra Eka Putra SSCrazy Points: 2015 More actions · Answer 2

I prefer not to use @@fetch_status <> -1, because @@fetch_status has 3 value, 0 for the successfull fetch, -1 for the failed fetch, and -2 for any other other.

Carl Walden SSC-Addicted Points: 411 More actions · Answer 3

Thanks for all your help, have a nice weekend.

CEWalden

chrisleonard SSCertifiable Points: 5030 More actions · Answer 4

I appreciate your comment, and thought you might be interested to find out that I chose WHILE @@FETCH_STATUS != -1 very deliberately.

To begin with, @@FETCH_STATUS = -2 does not mean "anything else." It specifically means that the row can't be found, such as if the row was there when the active set for the cursor was built in memory, but it isn't there any more.

You have to choose the correct termination for each cursor loop. @@FETCH_STATUS = -1 is usually avoided as a termination check by people because they think they always want to run with only successful fetches. Because of this, a lot of people typically terminate on @@FETCH_STATUS != 0, which is probably what you favor. Good enough. But I use @@FETCH_STATUS != -1 every time I want a "best effort," type of fetch that should continue beyond a fetch status of -2. This is the case fairly often for administrative work.

Take this case. Suppose I got back a fetch status of -2 (which I'm not even certain this type of cursor can return, but that's another topic). Suppose that this happened while trying to fetch the second of several hundred rows. Am I going to be happier knowing that I caught the -2 but didn't rebuild hardly any of the indexes? Or would I rather continue and attempt to fetch the rest, rebuilding as many as I can? I think I'd definitely want to rebuild as many indexes as possible in this case.

So, the bottom line: for this script, I think the loop-termination condition I used is definitely the one I'd want. But as long as you really understand the 3 different values of @@FETCH_STATUS, you should be OK. I'm just a little soap-boxishy about folks who apply a loop termination for a cursor loop without thinking through the alternatives.

Probably the best solution, IMHO, if I wanted to be a little more paranoid about this, would be to check after each fetch (at the top of each loop), using something like:

IF @@FETCH_STATUS=-2 RAISERROR ('Trouble', 10, 1) WITH NO_WAIT

Just my 0.02. Your mileage may vary.

Chris

poddoubsky SSC Veteran Points: 244 More actions · Answer 5

There is undocumented stored procedure available to achive you needs, I personally use it often:

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', '', 80)"

The proc will reindex everything with fullfactor = 80 in this case.

Julia

chrisleonard SSCertifiable Points: 5030 More actions · Answer 6

Yeah, you nailed it, Julia. I hope CEWalden is still following this thread, because unless s/he needs the potential flexibility of the expanded script I posted, what you posted is so much simpler.

In a somewhat explanatory mode, I'll say that we have used a script like the big one I posted so that we could cherry-pick which indexes we wanted to rebuild ... but that took a DBCC SHOWCONTIG run and some analysis just to decide what to rebuild. Once a database gets pretty big, you just can't rebuild it all at once.

Anyway, I can't believe I didn't think of sp_msforeachtable, so I'm glad you posted ... although I feel like I've just had a d'oh! moment. In a few years, I suppose those will become "senior moments."

Carl Walden SSC-Addicted Points: 411 More actions · Answer 7

Julia,

Thanks for the input I did tried to use the undocumented SP MSforeachtable but I guess I did made some mistakes, such mistakes are the main reasons why I will recommend every DBA to join such a group. This group is the best web based group I have come across and keep the good work, happy weekend to everyone, thanks once again for your help.

CEWalden