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...

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.co...gmentation.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.co...ng_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/de...es_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 may
take a bit of time to do, see
http://msdn.microsoft.com/library/de...ntwiz_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 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.
|||Thanks guys...
I will check these articles out...

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.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
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.com/rd_index_fragmentation.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.|||Marco
Also read this one
http://www.sql-server-performance.com/rebuilding_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/default.asp?url=/library/en-us/createdb/cm_8_des_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 may
take a bit of time to do, see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dbmntwiz/dbmntwiz_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 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.|||Thanks guys...
I will check these articles out...

Index Fragmentation

I have some very large transactional tables (20GB+). I am looking to optimize
the indexes. My question relates to fill factors.
In these tables (assuming they have a clustered index that is a sequential
value (or identity field)), if I have a covering index where the first key is
the clustered index, will fragmentation occur in this index as new data is
added since all the values will be added to the end? If fragmentation does
not occur in this situation, I would assume that specifying a non-default
fill factor on these types of indexes be a waste of disk space and resources.
Similarly, would another index, such as a transaction date (which generally
will only increase) peform similarly to the above example.Jason
Start with
http://www.sql-server-performance.com/rd_index_fragmentation.asp
"Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>I have some very large transactional tables (20GB+). I am looking to
>optimize
> the indexes. My question relates to fill factors.
> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
> added since all the values will be added to the end? If fragmentation
> does
> not occur in this situation, I would assume that specifying a non-default
> fill factor on these types of indexes be a waste of disk space and
> resources.
>
> Similarly, would another index, such as a transaction date (which
> generally
> will only increase) peform similarly to the above example.
>|||I had already read that, however that article really focuses on when to
defragment the indexes, not necessarily what a proper fill factor is or with
what types of indexes a fill factor might be proper for.
"Uri Dimant" wrote:
> Jason
> Start with
> http://www.sql-server-performance.com/rd_index_fragmentation.asp
>
>
> "Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
> news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
> >I have some very large transactional tables (20GB+). I am looking to
> >optimize
> > the indexes. My question relates to fill factors.
> >
> > In these tables (assuming they have a clustered index that is a sequential
> > value (or identity field)), if I have a covering index where the first key
> > is
> > the clustered index, will fragmentation occur in this index as new data is
> > added since all the values will be added to the end? If fragmentation
> > does
> > not occur in this situation, I would assume that specifying a non-default
> > fill factor on these types of indexes be a waste of disk space and
> > resources.
> >
> >
> > Similarly, would another index, such as a transaction date (which
> > generally
> > will only increase) peform similarly to the above example.
> >
>
>|||Jason,
> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
I am not quite sure what you mean by that. A clustered index (CI) is
essentially a covering index on all columns. If you have a CI on a
monotonically incrementing value such as Identity then newly inserted rows
will not cause fragmentation. But if you later update any rows on columns
that are not fixed in size with a larger value than the original you can get
page splits. A little bit of fragmentation is usually not a problem. If it
is an OLTP system fragmentation is not that much of an issue. See the
article below:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
To answer your question on non-clustered indexes they are just like
clustered indexes in structure except that the leaf level is only the
column(s) in the index expression. In a CI the leaf level is the whole row.
As with a CI the NCI will not fragment if the rows are inserted in column
order such as datetime etc.
Andrew J. Kelly SQL MVP
"Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>I have some very large transactional tables (20GB+). I am looking to
>optimize
> the indexes. My question relates to fill factors.
> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
> added since all the values will be added to the end? If fragmentation
> does
> not occur in this situation, I would assume that specifying a non-default
> fill factor on these types of indexes be a waste of disk space and
> resources.
>
> Similarly, would another index, such as a transaction date (which
> generally
> will only increase) peform similarly to the above example.
>

Index Fragmentation

I have some very large transactional tables (20GB+). I am looking to optimize
the indexes. My question relates to fill factors.
In these tables (assuming they have a clustered index that is a sequential
value (or identity field)), if I have a covering index where the first key is
the clustered index, will fragmentation occur in this index as new data is
added since all the values will be added to the end? If fragmentation does
not occur in this situation, I would assume that specifying a non-default
fill factor on these types of indexes be a waste of disk space and resources.
Similarly, would another index, such as a transaction date (which generally
will only increase) peform similarly to the above example.
Jason
Start with
http://www.sql-server-performance.co...gmentation.asp
"Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>I have some very large transactional tables (20GB+). I am looking to
>optimize
> the indexes. My question relates to fill factors.
> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
> added since all the values will be added to the end? If fragmentation
> does
> not occur in this situation, I would assume that specifying a non-default
> fill factor on these types of indexes be a waste of disk space and
> resources.
>
> Similarly, would another index, such as a transaction date (which
> generally
> will only increase) peform similarly to the above example.
>
|||I had already read that, however that article really focuses on when to
defragment the indexes, not necessarily what a proper fill factor is or with
what types of indexes a fill factor might be proper for.
"Uri Dimant" wrote:

> Jason
> Start with
> http://www.sql-server-performance.co...gmentation.asp
>
>
> "Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
> news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>
>
|||Jason,

> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
I am not quite sure what you mean by that. A clustered index (CI) is
essentially a covering index on all columns. If you have a CI on a
monotonically incrementing value such as Identity then newly inserted rows
will not cause fragmentation. But if you later update any rows on columns
that are not fixed in size with a larger value than the original you can get
page splits. A little bit of fragmentation is usually not a problem. If it
is an OLTP system fragmentation is not that much of an issue. See the
article below:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
To answer your question on non-clustered indexes they are just like
clustered indexes in structure except that the leaf level is only the
column(s) in the index expression. In a CI the leaf level is the whole row.
As with a CI the NCI will not fragment if the rows are inserted in column
order such as datetime etc.
Andrew J. Kelly SQL MVP
"Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>I have some very large transactional tables (20GB+). I am looking to
>optimize
> the indexes. My question relates to fill factors.
> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
> added since all the values will be added to the end? If fragmentation
> does
> not occur in this situation, I would assume that specifying a non-default
> fill factor on these types of indexes be a waste of disk space and
> resources.
>
> Similarly, would another index, such as a transaction date (which
> generally
> will only increase) peform similarly to the above example.
>

Index Fragmentation

I have some very large transactional tables (20GB+). I am looking to optimiz
e
the indexes. My question relates to fill factors.
In these tables (assuming they have a clustered index that is a sequential
value (or identity field)), if I have a covering index where the first key i
s
the clustered index, will fragmentation occur in this index as new data is
added since all the values will be added to the end? If fragmentation does
not occur in this situation, I would assume that specifying a non-default
fill factor on these types of indexes be a waste of disk space and resources
.
Similarly, would another index, such as a transaction date (which generally
will only increase) peform similarly to the above example.Jason
Start with
http://www.sql-server-performance.c...agmentation.asp
"Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>I have some very large transactional tables (20GB+). I am looking to
>optimize
> the indexes. My question relates to fill factors.
> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
> added since all the values will be added to the end? If fragmentation
> does
> not occur in this situation, I would assume that specifying a non-default
> fill factor on these types of indexes be a waste of disk space and
> resources.
>
> Similarly, would another index, such as a transaction date (which
> generally
> will only increase) peform similarly to the above example.
>|||I had already read that, however that article really focuses on when to
defragment the indexes, not necessarily what a proper fill factor is or with
what types of indexes a fill factor might be proper for.
"Uri Dimant" wrote:

> Jason
> Start with
> http://www.sql-server-performance.c...agmentation.asp
>
>
> "Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
> news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>
>|||Jason,

> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
I am not quite sure what you mean by that. A clustered index (CI) is
essentially a covering index on all columns. If you have a CI on a
monotonically incrementing value such as Identity then newly inserted rows
will not cause fragmentation. But if you later update any rows on columns
that are not fixed in size with a larger value than the original you can get
page splits. A little bit of fragmentation is usually not a problem. If it
is an OLTP system fragmentation is not that much of an issue. See the
article below:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
To answer your question on non-clustered indexes they are just like
clustered indexes in structure except that the leaf level is only the
column(s) in the index expression. In a CI the leaf level is the whole row.
As with a CI the NCI will not fragment if the rows are inserted in column
order such as datetime etc.
Andrew J. Kelly SQL MVP
"Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>I have some very large transactional tables (20GB+). I am looking to
>optimize
> the indexes. My question relates to fill factors.
> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
> added since all the values will be added to the end? If fragmentation
> does
> not occur in this situation, I would assume that specifying a non-default
> fill factor on these types of indexes be a waste of disk space and
> resources.
>
> Similarly, would another index, such as a transaction date (which
> generally
> will only increase) peform similarly to the above example.
>

Index Fill Ratio

Iâ'm trying to copy a very large amount of data about 10,000,000 records from
one table to another using DTS. And while Iâ'm doing the task although I can
select data from the destination table using the sql query analyzer, I canâ't
execute the same select statement using ADO connection through a web site.
Please advise A.S.A.P
Thanks and Best Regards,Hi
I'd doing such tasks at the end of the day where workload is off.
See TRANSACTION ISOLATION LEVEL in the BOL
"Ehab ELGEDDAWY" <Ehab ELGEDDAWY@.discussions.microsoft.com> wrote in message
news:4287D94F-12C2-40F3-8CFC-DD443400FB22@.microsoft.com...
> I?m trying to copy a very large amount of data about 10,000,000 records
> from
> one table to another using DTS. And while I?m doing the task although I
> can
> select data from the destination table using the sql query analyzer, I can?t
> execute the same select statement using ADO connection through a web site.
> Please advise A.S.A.P
> Thanks and Best Regards,
>

Index Fill Ratio

I’m trying to copy a very large amount of data about 10,000,000 records from
one table to another using DTS. And while I’m doing the task although I can
select data from the destination table using the sql query analyzer, I can’t
execute the same select statement using ADO connection through a web site.
Please advise A.S.A.P
Thanks and Best Regards,
Hi
I'd doing such tasks at the end of the day where workload is off.
See TRANSACTION ISOLATION LEVEL in the BOL
"Ehab ELGEDDAWY" <Ehab ELGEDDAWY@.discussions.microsoft.com> wrote in message
news:4287D94F-12C2-40F3-8CFC-DD443400FB22@.microsoft.com...
> Im trying to copy a very large amount of data about 10,000,000 records
> from
> one table to another using DTS. And while Im doing the task although I
> can
> select data from the destination table using the sql query analyzer, I cant
> execute the same select statement using ADO connection through a web site.
> Please advise A.S.A.P
> Thanks and Best Regards,
>

Index Fill Ratio

I’m trying to copy a very large amount of data about 10,000,000 records fr
om
one table to another using DTS. And while I’m doing the task although I c
an
select data from the destination table using the sql query analyzer, I can
t
execute the same select statement using ADO connection through a web site.
Please advise A.S.A.P
Thanks and Best Regards,Hi
I'd doing such tasks at the end of the day where workload is off.
See TRANSACTION ISOLATION LEVEL in the BOL
"Ehab ELGEDDAWY" <Ehab ELGEDDAWY@.discussions.microsoft.com> wrote in message
news:4287D94F-12C2-40F3-8CFC-DD443400FB22@.microsoft.com...
> Im trying to copy a very large amount of data about 10,000,000 records
> from
> one table to another using DTS. And while Im doing the task although I
> can
> select data from the destination table using the sql query analyzer, I can
t
> execute the same select statement using ADO connection through a web site.
> Please advise A.S.A.P
> Thanks and Best Regards,
>

Index and Data Pages

I am trying to understand how the data in sql server is stored and
also regarding fill factor and page splitting.

1) My first question what is the difference between Index pages and
Data pages.
and how are they different for clustered and non clustered indexes and
heap tables.

2) What is the relation between index and data pages.

2) BOL says that fill factor is used to define the amount of free
space on each page of index. I am confused here what does index pages
and data pages contain
for clustered/non clustered/heap tables.

3) Why does page splits occur and do they occur due to the lack of
space in index or data pages?

Thanks

skura"skura" <thotakura1@.comcast.net> wrote in message
news:ccbd7dbf.0311252154.32a46cd5@.posting.google.c om...
> I am trying to understand how the data in sql server is stored and
> also regarding fill factor and page splitting.

I recommend you get a copy of Inside SQL Server 2000. It contains a lot of
detailed information on the low-level implementation of data and index
pages. I've given some short answers below (which will certainly be
oversimplified, but hopefully accurate enough), but there's enough
information in Books Online to get a good understanding.

> 1) My first question what is the difference between Index pages and
> Data pages.
> and how are they different for clustered and non clustered indexes and
> heap tables.

Data pages have data only; clustered index pages have index information and
data (because the leaf level is made up of data pages); non-clustered index
pages have index information only.

> 2) What is the relation between index and data pages.

If the index is clustered, the leaf nodes are data pages. If the index is
non-clustered, but there is already another clustered index on the table,
the leaf nodes point to keys in the clustered index. If the table is a heap,
the index leaf nodes point to rows in data pages.

> 2) BOL says that fill factor is used to define the amount of free
> space on each page of index. I am confused here what does index pages
> and data pages contain
> for clustered/non clustered/heap tables.

As above, with a clustered index, the index includes data pages; with a heap
table, the indexes have only index pages.

> 3) Why does page splits occur and do they occur due to the lack of
> space in index or data pages?

If the table has a clustered index, and the data pages in the leaf node fill
up with data, a split occurs because SQL Server has to 'make room' for the
new data. The same applies to non-clustered indexes - as data is added,
sooner or later the index pages will get full, whatever other indexes may be
on the table.

Think of putting new books in the middle of a shelf which already has lots
of books, sorted in alphabetical order. If all the old books are side to
side with no gaps, you'll have to move some of them every time you add a new
book to the shelf, in order to keep them all in alphabetical order. But if
you leave a number of spaces between the books, then you'll usually be able
to add many more new books before you have to move any of the old ones.

> Thanks
> skura

I hope that helps.

Simon|||Simon, That helps and thanks for the info. I will get the book and read it.

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<3fc51cae$1_3@.news.bluewin.ch>...
> "skura" <thotakura1@.comcast.net> wrote in message
> news:ccbd7dbf.0311252154.32a46cd5@.posting.google.c om...
> > I am trying to understand how the data in sql server is stored and
> > also regarding fill factor and page splitting.
> I recommend you get a copy of Inside SQL Server 2000. It contains a lot of
> detailed information on the low-level implementation of data and index
> pages. I've given some short answers below (which will certainly be
> oversimplified, but hopefully accurate enough), but there's enough
> information in Books Online to get a good understanding.
> > 1) My first question what is the difference between Index pages and
> > Data pages.
> > and how are they different for clustered and non clustered indexes and
> > heap tables.
> Data pages have data only; clustered index pages have index information and
> data (because the leaf level is made up of data pages); non-clustered index
> pages have index information only.
> > 2) What is the relation between index and data pages.
> If the index is clustered, the leaf nodes are data pages. If the index is
> non-clustered, but there is already another clustered index on the table,
> the leaf nodes point to keys in the clustered index. If the table is a heap,
> the index leaf nodes point to rows in data pages.
> > 2) BOL says that fill factor is used to define the amount of free
> > space on each page of index. I am confused here what does index pages
> > and data pages contain
> > for clustered/non clustered/heap tables.
> As above, with a clustered index, the index includes data pages; with a heap
> table, the indexes have only index pages.
> > 3) Why does page splits occur and do they occur due to the lack of
> > space in index or data pages?
> If the table has a clustered index, and the data pages in the leaf node fill
> up with data, a split occurs because SQL Server has to 'make room' for the
> new data. The same applies to non-clustered indexes - as data is added,
> sooner or later the index pages will get full, whatever other indexes may be
> on the table.
> Think of putting new books in the middle of a shelf which already has lots
> of books, sorted in alphabetical order. If all the old books are side to
> side with no gaps, you'll have to move some of them every time you add a new
> book to the shelf, in order to keep them all in alphabetical order. But if
> you leave a number of spaces between the books, then you'll usually be able
> to add many more new books before you have to move any of the old ones.
> > Thanks
> > skura
> I hope that helps.
> Simon