fill factor question

Ive always had a hard time getting my head into this topic so please bear
with me. Say Ive got a huge table that gets lots of Inserts. But to do these
Inserts, it needs to do lots of Selects.
#Table3 is the table in question here:
create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
create table #Table2(T2C1 int, T2C2 char(10))
create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4 char(10))
insert into #Table1 (T1C2) values ('T1C2')
insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
select t1.T1C1,T1C2,t2.T2C2, 'test'
from #Table1 t1
inner join #Table2 t2 on t1.t1c1 = t2.t2c1
So, would a table like this call for a lower Fill Factor as it will have
lots of new Inserts. Or would a table like this call for a higher Fill
Factor to increase the Select speed to do those Inserts?
TIA, ChrisR.What column(s) is the clustered index on?
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
> Ive always had a hard time getting my head into this topic so please bear
> with me. Say Ive got a huge table that gets lots of Inserts. But to do
> these Inserts, it needs to do lots of Selects.
> #Table3 is the table in question here:
> create table #Table1(T1C1 int identity (1,1), T1C2 char(10))
> create table #Table2(T2C1 int, T2C2 char(10))
> create table #Table3(T3C1 int, T3C2 char(10), T3C3 char(10), T3C4
> char(10))
> insert into #Table1 (T1C2) values ('T1C2')
> insert into #Table2 (T2C1,T2C2) values (1,'T1C2')
> insert into #Table3 (T3C1,T3C2,T3C3,T3C4)
> select t1.T1C1,T1C2,t2.T2C2, 'test'
> from #Table1 t1
> inner join #Table2 t2 on t1.t1c1 = t2.t2c1
>
> So, would a table like this call for a lower Fill Factor as it will have
> lots of new Inserts. Or would a table like this call for a higher Fill
> Factor to increase the Select speed to do those Inserts?
> TIA, ChrisR.
>|||Lets say C1 for all 3 tables.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
> What column(s) is the clustered index on?
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eRXkg55aFHA.2756@.tk2msftngp13.phx.gbl...
>|||If the inserts come in ordered, a higher fillfactor would be ideal. If
not, a high one will still be good as the index will balance out very
quickly. Maximizing the rows per page will greatly help the selection
of rows of data and be fewer I/Os.|||If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>|||Since this was a question regarding the appropriate fill factor, which you
answered quite nicely, I won't beat up on you too badly.
First of all, IDENTITIES are NOT good candidates for the Clustered Index as
is espoused so often.
Second, Identity Clustered Indexes do create page splits, quite often, at
the node levels of the indexes.
Third, Identities are usually only a surrogate key. All tables, wich
represent enities, must at least have a business key in order to use a
surrogate as a proxy; otherwise, you are just making things up with no clear
definition. That key should have a Unique Constraint defined whenever the
Primary Key is placed on the Identity.
Finally, for you select statement that you are using for your Insert
statement, all attributes used within a Join Condition and any highly
selectable columns that participate in a Where Clause should have and index
defined on them.
Sincerely,
Anthony Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OJ53NT9aFHA.2900@.TK2MSFTNGP15.phx.gbl...
If all you ever did was inserts or deletes (no Updates to variable length
columns) a 100% fill factor would work well on an Identity column. That way
all new rows simply get appended to the end of the current page and do not
cause pagesplits. If you update with larger rows you need to account for
that extra space on each page. Fill factors are always a trade off between
leaving enough room for inserts and updates vs. keeping it full enough to
not waste memory or disk space by having lots of empty space on each page.
There is no one correct answer as to what it should be. You must determine
that based on the activity of each table and the frequency of the
reindexing. If you reindex each night you can usually get by with a
relatively high fill factor. If it's once a week it probably would be
lower. You need to monitor the fragmentation in between reindexing to see
how fragmented it gets and how many page splits happen. If it is heavily
fragmented you may benefit from a lower fill factor. If not make it higher.
What those values should be we can not say. A good place to start is
usually around 80 or 90 on an index that is frequently Inserted, Updated and
Deleted. Then go from there.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:e5WhWP6aFHA.3032@.TK2MSFTNGP10.phx.gbl...
> Lets say C1 for all 3 tables.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OOZsb$5aFHA.2076@.TK2MSFTNGP15.phx.gbl...
>