DBCC DBREINDEX

Question

DBCC DBREINDEX

raj-247939

SSCertifiable

Points: 5999
More actions
June 10, 2006 at 3:20 am

#115457

Dear all,

DBCC DBREINDEX: To rebuilt all the indexes for a table.
I executed the code like this DBCC DBREINDEX(tablename,'',70)
"70" is the fillfactor means it will fill the index page upto 70 percent.
I need to know why we leave this 30 percent and how it helps us.

from
Killer

Viewing 5 posts - 1 through 4 (of 4 total)

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

Curtis Smith Hall of Fame Points: 3063 More actions · Answer 1

Basically, you are leaving space for the data to grow. Check BOL, but in short, when a page runs out of room it splits the page. more splitting means more overhead, so if you leave your self room to grow.

Curtis Smith
SQL Server DBA
Well in worked in Theory ...

Maria-Raluca SSC Enthusiast Points: 101 More actions · Answer 2

Whe you use this option, must have in mind how often data is modified.

This option, fillfactor is useful to optimize the performance of INSERT or UPDATE statements.

When an index page is full, SQL Server must take time to split the page to make room for new rows (new index page must be alocated) . This option reduce page splitting.

Use the fillfactor option on tables into which many rows are inserted or updated because clustered index key values are frequently modified.

The fillfactor option is applied only when the index is created or rebuilt, Sql server does not maintain the specified percentage.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

CONVERSELY... smaller fill factors also means SELECTs have to wade through more blank pages which will slow them down. If the table is a static lookup table (one that rarely, if ever, has changes), use FILLFACTOR = 100 for the maximum performance. What I'm saying is that not every table should have a FILLFACTOR = 70... only the ones the have daily inserts or updates (as Maria suggested).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

raj-247939 SSCertifiable Points: 5999 More actions · Answer 4

Thanx a lot.

pls also help in my other posts.

from

Killer