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 Building

Does SQL Server 2000 optimize indexes differently than SQL 7
It seems that SQL 2K doesn't (re)build the index until you execute a
query that will utilize the index. This is causing the first time you
execute the query to be slow. The second time it is executed it runs
fast.
We have maintenance plans to rebuild the indexes each night. This
doesn't seem to help. Instead if it seems like the index is rebuilt
when our users are executing the query which is undesirable. Does
anyone know if this is what SQL Server does? If so, how can I can this
behavior?
GregGreg,
SQL Server does not build indexes unless you actually give it the command to
do so. It does not build any on the fly. It may create statistics on the
fly but they are not indexes. Your most likely seeing the effects of two
things. One is that since you rebuild indexes each night (which probably
isn't necessary) you will invalidate the cached plan. So the next time you
run a query it must recompile the plan. The other and more likely is that
the data has most likely been flushed from cache and will need to be brought
back into cache from disk. This is a relatively slow process. But once it
is in cache the next queries will be much faster.
Andrew J. Kelly
SQL Server MVP
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||Indexes are maintained with each insert/update/delete.
with time, indexes can be fragmented, this is why we defragment them. They
are defragmented when you execute the DBCC DBREINDEX command (of whichever
method you are using).
There is no difference between SQL7 and 2000 in this regard. Possible causes
for what you see can be 1) data is not in the cache when the "first" query
hits is or 2) the optimizer has to produce a query plan, possible it think
so as the indexes has been defragmented. These are two reasons I cam up
with, there can be others as well, of course.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||The only thing that SQL might do is update statistics when looking at a
query. This shouldn't matter because statistics are updated nightly as part
of the dbreindex...
Could the issue be related to physcial IO?... First user brings data into
memory, and others benefit?
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg

Index Building

Does SQL Server 2000 optimize indexes differently than SQL 7
It seems that SQL 2K doesn't (re)build the index until you execute a
query that will utilize the index. This is causing the first time you
execute the query to be slow. The second time it is executed it runs
fast.
We have maintenance plans to rebuild the indexes each night. This
doesn't seem to help. Instead if it seems like the index is rebuilt
when our users are executing the query which is undesirable. Does
anyone know if this is what SQL Server does? If so, how can I can this
behavior?
GregGreg,
SQL Server does not build indexes unless you actually give it the command to
do so. It does not build any on the fly. It may create statistics on the
fly but they are not indexes. Your most likely seeing the effects of two
things. One is that since you rebuild indexes each night (which probably
isn't necessary) you will invalidate the cached plan. So the next time you
run a query it must recompile the plan. The other and more likely is that
the data has most likely been flushed from cache and will need to be brought
back into cache from disk. This is a relatively slow process. But once it
is in cache the next queries will be much faster.
--
Andrew J. Kelly
SQL Server MVP
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||Indexes are maintained with each insert/update/delete.
with time, indexes can be fragmented, this is why we defragment them. They
are defragmented when you execute the DBCC DBREINDEX command (of whichever
method you are using).
There is no difference between SQL7 and 2000 in this regard. Possible causes
for what you see can be 1) data is not in the cache when the "first" query
hits is or 2) the optimizer has to produce a query plan, possible it think
so as the indexes has been defragmented. These are two reasons I cam up
with, there can be others as well, of course.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||The only thing that SQL might do is update statistics when looking at a
query. This shouldn't matter because statistics are updated nightly as part
of the dbreindex...
Could the issue be related to physcial IO?... First user brings data into
memory, and others benefit?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
<greg@.nospam.xyz> wrote in message news:40323538.2844@.nospam.xyz...
> Does SQL Server 2000 optimize indexes differently than SQL 7
> It seems that SQL 2K doesn't (re)build the index until you execute a
> query that will utilize the index. This is causing the first time you
> execute the query to be slow. The second time it is executed it runs
> fast.
> We have maintenance plans to rebuild the indexes each night. This
> doesn't seem to help. Instead if it seems like the index is rebuilt
> when our users are executing the query which is undesirable. Does
> anyone know if this is what SQL Server does? If so, how can I can this
> behavior?
> Greg|||First use, sometimes doing an update statistics will help.
Other things I've done at some sites, with 7.0 is execute
the proc after its built before my users get in. Not the
cleanest way, but it worked. I haven't had similar
problems in 2000, but you never know.
Gary Abbott
MS-SQL Database Architect
>--Original Message--
>Does SQL Server 2000 optimize indexes differently than
SQL 7
>It seems that SQL 2K doesn't (re)build the index until
you execute a
>query that will utilize the index. This is causing the
first time you
>execute the query to be slow. The second time it is
executed it runs
>fast.
>We have maintenance plans to rebuild the indexes each
night. This
>doesn't seem to help. Instead if it seems like the index
is rebuilt
>when our users are executing the query which is
undesirable. Does
>anyone know if this is what SQL Server does? If so, how
can I can this
>behavior?
>Greg
>.
>|||UPDATE STATISTICS shouldn't be needed after DBCC DBREINDEX because the
distribution data is updated with the index rebuild...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
<anonymous@.discussions.microsoft.com> wrote in message
news:1213e01c3f5b8$534208d0$a401280a@.phx.gbl...
> First use, sometimes doing an update statistics will help.
> Other things I've done at some sites, with 7.0 is execute
> the proc after its built before my users get in. Not the
> cleanest way, but it worked. I haven't had similar
> problems in 2000, but you never know.
> Gary Abbott
> MS-SQL Database Architect
>
> >--Original Message--
> >Does SQL Server 2000 optimize indexes differently than
> SQL 7
> >
> >It seems that SQL 2K doesn't (re)build the index until
> you execute a
> >query that will utilize the index. This is causing the
> first time you
> >execute the query to be slow. The second time it is
> executed it runs
> >fast.
> >
> >We have maintenance plans to rebuild the indexes each
> night. This
> >doesn't seem to help. Instead if it seems like the index
> is rebuilt
> >when our users are executing the query which is
> undesirable. Does
> >anyone know if this is what SQL Server does? If so, how
> can I can this
> >behavior?
> >
> >Greg
> >.
> >

index ..optimize query pls

hi,
the below table used in the query doest have any indexes...
i've created non clus index on status and barcode...
will this optimize the query,or should i go for covering index..
**should i create index on temp table also...
UPDATE MMMailCust
SET status = 'Approved'
FROM MMMailCust a
INNER JOIN #tmpOneMMReason b ON (a.barcode = b.barcode)
regardsYou are only involving status and barcode on the one table, how wide is your
temp table or do you have to use it?
Ray Higdon MCSE, MCDBA, CCNA
--
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:F02BC517-5A6E-438C-B2EE-F8103DA3AEF8@.microsoft.com...
> hi,
> the below table used in the query doest have any indexes...
> i've created non clus index on status and barcode...
> will this optimize the query,or should i go for covering index..
> **should i create index on temp table also...
> UPDATE MMMailCust
> SET status = 'Approved'
> FROM MMMailCust a
> INNER JOIN #tmpOneMMReason b ON (a.barcode = b.barcode)
> regards|||hi
temp table at a time will contain on 5000 records ..
thnks|||sanjay
Why did you create non clustered index on the status column? It seems to be
useless because of low selectivity of the column . You could have 'approved'
,''not approved " ...what else?
but non clustered index would be more useful if your data will be at least
95% selective.
I'd suggest you to remove the index from status column and run the query.
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:740BBFD5-7611-45CF-B85E-7ECC5F4D22FC@.microsoft.com...
> hi
> temp table at a time will contain on 5000 records ..
> thnks|||Unless the status column is part of a covering index...
Ray Higdon MCSE, MCDBA, CCNA
--
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23flIAj4$DHA.684@.tk2msftngp13.phx.gbl...
> sanjay
> Why did you create non clustered index on the status column? It seems to
be
> useless because of low selectivity of the column . You could have
'approved'
> ,''not approved " ...what else?
> but non clustered index would be more useful if your data will be at least
> 95% selective.
> I'd suggest you to remove the index from status column and run the query.
>
> "sanjay" <anonymous@.discussions.microsoft.com> wrote in message
> news:740BBFD5-7611-45CF-B85E-7ECC5F4D22FC@.microsoft.com...
>

index ..optimize query pls

hi
the below table used in the query doest have any indexes..
i've created non clus index on status and barcode..
will this optimize the query,or should i go for covering index.
**should i create index on temp table also..
UPDATE MMMailCust
SET status = 'Approved'
FROM MMMailCust a
INNER JOIN #tmpOneMMReason b ON (a.barcode = b.barcode)
regardsYou are only involving status and barcode on the one table, how wide is your
temp table or do you have to use it?
--
Ray Higdon MCSE, MCDBA, CCNA
--
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:F02BC517-5A6E-438C-B2EE-F8103DA3AEF8@.microsoft.com...
> hi,
> the below table used in the query doest have any indexes...
> i've created non clus index on status and barcode...
> will this optimize the query,or should i go for covering index..
> **should i create index on temp table also...
> UPDATE MMMailCust
> SET status = 'Approved'
> FROM MMMailCust a
> INNER JOIN #tmpOneMMReason b ON (a.barcode = b.barcode)
> regards|||h
temp table at a time will contain on 5000 records .
thnks|||sanjay
Why did you create non clustered index on the status column? It seems to be
useless because of low selectivity of the column . You could have 'approved'
,''not approved " ...what else?
but non clustered index would be more useful if your data will be at least
95% selective.
I'd suggest you to remove the index from status column and run the query.
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:740BBFD5-7611-45CF-B85E-7ECC5F4D22FC@.microsoft.com...
> hi
> temp table at a time will contain on 5000 records ..
> thnks|||Unless the status column is part of a covering index...
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23flIAj4$DHA.684@.tk2msftngp13.phx.gbl...
> sanjay
> Why did you create non clustered index on the status column? It seems to
be
> useless because of low selectivity of the column . You could have
'approved'
> ,''not approved " ...what else?
> but non clustered index would be more useful if your data will be at least
> 95% selective.
> I'd suggest you to remove the index from status column and run the query.
>
> "sanjay" <anonymous@.discussions.microsoft.com> wrote in message
> news:740BBFD5-7611-45CF-B85E-7ECC5F4D22FC@.microsoft.com...
> > hi
> > temp table at a time will contain on 5000 records ..
> >
> > thnks
>