index Option - Fill Factor

Hi,
Lets suppose a table with 300.000+ rows. This table is heavily Updated,
SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
most of cases, a row is between 7 and 10 times updated, in different columns
.
I have created 5 indexes in this table, corresponding to columns that I
frequently use in SELECTs or UPDATEs actions.
BOL tells me that a 100(0)% Fill Factor should not be used on heavily
updated table.
In this particular case, what Fill Factor percentage can be used in each
index?
Thanks in advance...
Marco.Marco
if you have an index page with 1,4,5, keys and try to insert a row=2 so
page splits may occured because there is no room for a new one and 4,5 key
need to be moved on another page. These keys are out of the logical order
and it causes to External fragemtation your database. But if your keys are
added as 6,7,8 there will no pages splits and a fill factor 90% is gonna be
ok. The same way take a look at Updating and Deletion.
http://www.sql-server-performance.c...agmentation.asp
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||If your index is doing page splits you might benefit from using a lower
fillfactor... However this is true on the main table ONLY when new rows are
NOT added at the end of the table ( as would be the case where the clustered
index is the PK, or a strictly increaseing Date field.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
> columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Marco
Also read this one
http://www.sql-server-performance.c...ing_indexes.asp
"Marco Pais" <Marco Pais@.discussions.microsoft.com> wrote in message
news:AFDB46ED-4761-4B84-899D-DAD28A69AF18@.microsoft.com...
> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different
columns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Firstly have a look at
http://msdn.microsoft.com/library/d...>
_05_9ak5.asp
This gives a bit of background on what a fill factor does.
So a fill factor of 100% means that the total size of the data in the
datafile will be as small as possible, but your running the risk of Page
Splitting, which can affect performance.
A lower value say 0% means that there is less change of page splitting, but
your datafiles will be a bit bigger.
So with your example you need to ask yourself 'What is more important to me'
size or performance. BTW its not to say that a 0% will not cause page
splitting, it does, but that there is probably going to be less chance of it
happening.
Anyway the answer to your question is going to be a bit glib. Basically
there is no real way of finding for yourself what is going to be the best
alternative except to experement with the fill factors until you find out
yourself what is the best combination of fill factors.
NB you can run a nightly job to re-organise your database index using the
Database Maintenance plans, that will get rid of your split pages, but it ma
y
take a bit of time to do, see
http://msdn.microsoft.com/library/d...r />
_4iur.asp
"Marco Pais" wrote:

> Hi,
> Lets suppose a table with 300.000+ rows. This table is heavily Updated,
> SELECTED or DELETED. Let's say... there are 400/500 new rows per day and,
> most of cases, a row is between 7 and 10 times updated, in different colum
ns.
> I have created 5 indexes in this table, corresponding to columns that I
> frequently use in SELECTs or UPDATEs actions.
> BOL tells me that a 100(0)% Fill Factor should not be used on heavily
> updated table.
> In this particular case, what Fill Factor percentage can be used in each
> index?
> Thanks in advance...
> Marco.|||Thanks guys...
I will check these articles out...