Reindexing

As my sig indicates I primarily work with Oracle, though I am also
responsiible for about 16 MSSQL databases. One of these databases has a
reindexing job (part of the maintenance plan) that fails. This is easy to
fix.
However I'm wondering what the benefits of the reindexing job actually are.
In the Oracle world it is *almost* never necessary to reindex a table
(unless you want to move the index from one device to another). So before I
allocate extra disk space to the relevant filegroup, would it be better to
remove the reindexing from the maintenance plan?
links rather than a full explanation are equally welcome.
cheers
--
Niall Litchfield
Oracle DBA
Audit Commission UkNiall,
Only you can determine whether the fragmentation hat occurs for your data (based on the
modifications performed) will hurt performance of your particular load. You can use things like DBCC
SHOWCONTIG to check fragmentation level, and/or test performance of a typical query on a
fragmented/non-fragmented table(s). You might even want to record a set of queries using profiler
and replay that load against a fragmented/non-fragmented database.
One link that might be of use is:
http://tinyurl.com/8dok
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fa0d7f8$0$9469$ed9e5944@.reading.news.pipex.net...
> As my sig indicates I primarily work with Oracle, though I am also
> responsiible for about 16 MSSQL databases. One of these databases has a
> reindexing job (part of the maintenance plan) that fails. This is easy to
> fix.
> However I'm wondering what the benefits of the reindexing job actually are.
> In the Oracle world it is *almost* never necessary to reindex a table
> (unless you want to move the index from one device to another). So before I
> allocate extra disk space to the relevant filegroup, would it be better to
> remove the reindexing from the maintenance plan?
> links rather than a full explanation are equally welcome.
> cheers
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission Uk
>|||Cheers.
I had found the article you refer to, and it is helpful. I like the idea of
recording baseline workload as well, though determining whether performance
changes are due to index fragmentation (which I still haven't quite got my
head around) or changed data volumes might be a challenge.
--
Niall Litchfield
Oracle DBA
Audit Commission Uk
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eqj%23WatnDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Niall,
> Only you can determine whether the fragmentation hat occurs for your data
(based on the
> modifications performed) will hurt performance of your particular load.
You can use things like DBCC
> SHOWCONTIG to check fragmentation level, and/or test performance of a
typical query on a
> fragmented/non-fragmented table(s). You might even want to record a set of
queries using profiler
> and replay that load against a fragmented/non-fragmented database.
> One link that might be of use is:
> http://tinyurl.com/8dok
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
> news:3fa0d7f8$0$9469$ed9e5944@.reading.news.pipex.net...
> > As my sig indicates I primarily work with Oracle, though I am also
> > responsiible for about 16 MSSQL databases. One of these databases has a
> > reindexing job (part of the maintenance plan) that fails. This is easy
to
> > fix.
> >
> > However I'm wondering what the benefits of the reindexing job actually
are.
> > In the Oracle world it is *almost* never necessary to reindex a table
> > (unless you want to move the index from one device to another). So
before I
> > allocate extra disk space to the relevant filegroup, would it be better
to
> > remove the reindexing from the maintenance plan?
> >
> > links rather than a full explanation are equally welcome.
> >
> > cheers
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission Uk
> >
> >
>|||> ...changes are due to index fragmentation (which I still haven't quite got my
> head around)
To understand fragmentation in SQL Server, you need to understand extents to begin with. Read the
physical database architecture stuff in Books Online, and even a good book on the topic. "Inside MS
SQL Server" is a good architectural book on SQL Server (MS Press).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fa10c09$0$9479$ed9e5944@.reading.news.pipex.net...
> Cheers.
> I had found the article you refer to, and it is helpful. I like the idea of
> recording baseline workload as well, though determining whether performance
> changes are due to index fragmentation (which I still haven't quite got my
> head around) or changed data volumes might be a challenge.
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission Uk
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eqj%23WatnDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > Niall,
> >
> > Only you can determine whether the fragmentation hat occurs for your data
> (based on the
> > modifications performed) will hurt performance of your particular load.
> You can use things like DBCC
> > SHOWCONTIG to check fragmentation level, and/or test performance of a
> typical query on a
> > fragmented/non-fragmented table(s). You might even want to record a set of
> queries using profiler
> > and replay that load against a fragmented/non-fragmented database.
> > One link that might be of use is:
> > http://tinyurl.com/8dok
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
> > news:3fa0d7f8$0$9469$ed9e5944@.reading.news.pipex.net...
> > > As my sig indicates I primarily work with Oracle, though I am also
> > > responsiible for about 16 MSSQL databases. One of these databases has a
> > > reindexing job (part of the maintenance plan) that fails. This is easy
> to
> > > fix.
> > >
> > > However I'm wondering what the benefits of the reindexing job actually
> are.
> > > In the Oracle world it is *almost* never necessary to reindex a table
> > > (unless you want to move the index from one device to another). So
> before I
> > > allocate extra disk space to the relevant filegroup, would it be better
> to
> > > remove the reindexing from the maintenance plan?
> > >
> > > links rather than a full explanation are equally welcome.
> > >
> > > cheers
> > >
> > > --
> > > Niall Litchfield
> > > Oracle DBA
> > > Audit Commission Uk
> > >
> > >
> >
> >
>|||"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OvDkvLvnDHA.2080@.TK2MSFTNGP10.phx.gbl...
> > ...changes are due to index fragmentation (which I still haven't quite
got my
> > head around)
> To understand fragmentation in SQL Server, you need to understand extents
to begin with. Read the
> physical database architecture stuff in Books Online, and even a good book
on the topic. "Inside MS
> SQL Server" is a good architectural book on SQL Server (MS Press).
Thanks for the book recommendation. I think that I have a pretty good handle
on pages/extents as they seem to map exactly to blocks/extents in Oracle
(except that they are fixed in size which for the purposes of this
discussion is irrelevant). My problem came (I think) with the definition of
fragmentation. they key definition is indeed in your article, which states
"Fragmentation exists when indexes have pages in which the logical ordering,
based on the key value, does not match the physical ordering inside the data
file. " and " Ideally, the physical order of the pages in the data file
should match the logical ordering. Overall disk throughput is increased
significantly when the physical ordering matches the logical ordering of the
data. This leads to much better performance for certain types of queries.
When the physical ordering does not match the logical ordering, disk
throughput can become less efficient, because the disk head must move back
and forth to gather the index pages instead of scanning forward in one
direction". Obviously the acid test is does the application suffer in day to
day use if indexes are fragmented, but the argument advanced above seems to
me to be somewhat lacking in force because
1. MSSQL is a multi-user system so multiple sessions will be competing for
IO on the same device.
2. Some data pages will be in memory and so you would expect to scan the
index in different chunks.
3. The datafile will be cached by the filesystem anyway so a physical IO
call may not require a disk read.
4. You probably have a caching disk controller to get in the way as well.
My mental definition of fragmentation had unusable chunks of free space in
the datafile (it is what an Oracle DBA would understand by fragmentation),
and so long as there is a free page I can't see how this can happen in MSSQL
hence my confusion. Thanks anyway.
Niall Litchfield
Oracle DBA
Audit Commission Uk|||You should also read this white paper
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
for an excellent discussion on diagnosing and correcting fragmentation.
Gail Erickson
SQL Server Documentaion Team
This posting is provided "AS IS" with no warranties, and confers no rights.
"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fa12efd$0$9471$ed9e5944@.reading.news.pipex.net...
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OvDkvLvnDHA.2080@.TK2MSFTNGP10.phx.gbl...
> > > ...changes are due to index fragmentation (which I still haven't quite
> got my
> > > head around)
> >
> > To understand fragmentation in SQL Server, you need to understand
extents
> to begin with. Read the
> > physical database architecture stuff in Books Online, and even a good
book
> on the topic. "Inside MS
> > SQL Server" is a good architectural book on SQL Server (MS Press).
> Thanks for the book recommendation. I think that I have a pretty good
handle
> on pages/extents as they seem to map exactly to blocks/extents in Oracle
> (except that they are fixed in size which for the purposes of this
> discussion is irrelevant). My problem came (I think) with the definition
of
> fragmentation. they key definition is indeed in your article, which states
> "Fragmentation exists when indexes have pages in which the logical
ordering,
> based on the key value, does not match the physical ordering inside the
data
> file. " and " Ideally, the physical order of the pages in the data file
> should match the logical ordering. Overall disk throughput is increased
> significantly when the physical ordering matches the logical ordering of
the
> data. This leads to much better performance for certain types of queries.
> When the physical ordering does not match the logical ordering, disk
> throughput can become less efficient, because the disk head must move back
> and forth to gather the index pages instead of scanning forward in one
> direction". Obviously the acid test is does the application suffer in day
to
> day use if indexes are fragmented, but the argument advanced above seems
to
> me to be somewhat lacking in force because
> 1. MSSQL is a multi-user system so multiple sessions will be competing for
> IO on the same device.
> 2. Some data pages will be in memory and so you would expect to scan the
> index in different chunks.
> 3. The datafile will be cached by the filesystem anyway so a physical IO
> call may not require a disk read.
> 4. You probably have a caching disk controller to get in the way as well.
> My mental definition of fragmentation had unusable chunks of free space in
> the datafile (it is what an Oracle DBA would understand by fragmentation),
> and so long as there is a free page I can't see how this can happen in
MSSQL
> hence my confusion. Thanks anyway.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission Uk
>|||Niall,
I think Tibor has pointed out a source of your concern and our SQL Server Documentaion
Team has also provided you with a white paper for an excellent discussion on diagnosing
and correcting fragmentation.
Here I add a few values on different fragment handling between products; hope it will be
helpful.
SQL Server uses Reindex and Indexdefrag to shuffle pages and remove no gaps in the actual
database pages from deletion. You can also run dbcc showcontig ('tablename') and look at
extents scan ragmentation to check to see if you have physical fragmentation.
Based on my experience, other products like Oracle, DB2 etc may use Reorg Utility to
remove gaps in the actual pages. I'm not so sure about this but it may have tiny difference of
fragmentation definition across different RMDB products.
Best regards,
Billy Yao
Microsoft Online Partner Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||> Thanks for the book recommendation. I think that I have a pretty good handle
> on pages/extents
Good, makes thing easier.
> My mental definition of fragmentation had unusable chunks of free space in
> the datafile
Ahh, that doesn't happen in SQL Server. One thing to consider is for a heap table ( a table without
a clustered index), you will get "holes" on your pages as you update and delete data. This will be
reused, but there is no command to "compact" these heap data pages.
I see your points about fragmentation ("jumping back and forth") not being so serous at it might
seem at first sight. However, it still can affect performance, partly because "jumping back and
forth" for the disk, partly because it messes up read-ahead (RA reads an extent, uses one page and
then jumps off to some other extent - not good usage of cache).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fa12efd$0$9471$ed9e5944@.reading.news.pipex.net...
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:OvDkvLvnDHA.2080@.TK2MSFTNGP10.phx.gbl...
> > > ...changes are due to index fragmentation (which I still haven't quite
> got my
> > > head around)
> >
> > To understand fragmentation in SQL Server, you need to understand extents
> to begin with. Read the
> > physical database architecture stuff in Books Online, and even a good book
> on the topic. "Inside MS
> > SQL Server" is a good architectural book on SQL Server (MS Press).
> Thanks for the book recommendation. I think that I have a pretty good handle
> on pages/extents as they seem to map exactly to blocks/extents in Oracle
> (except that they are fixed in size which for the purposes of this
> discussion is irrelevant). My problem came (I think) with the definition of
> fragmentation. they key definition is indeed in your article, which states
> "Fragmentation exists when indexes have pages in which the logical ordering,
> based on the key value, does not match the physical ordering inside the data
> file. " and " Ideally, the physical order of the pages in the data file
> should match the logical ordering. Overall disk throughput is increased
> significantly when the physical ordering matches the logical ordering of the
> data. This leads to much better performance for certain types of queries.
> When the physical ordering does not match the logical ordering, disk
> throughput can become less efficient, because the disk head must move back
> and forth to gather the index pages instead of scanning forward in one
> direction". Obviously the acid test is does the application suffer in day to
> day use if indexes are fragmented, but the argument advanced above seems to
> me to be somewhat lacking in force because
> 1. MSSQL is a multi-user system so multiple sessions will be competing for
> IO on the same device.
> 2. Some data pages will be in memory and so you would expect to scan the
> index in different chunks.
> 3. The datafile will be cached by the filesystem anyway so a physical IO
> call may not require a disk read.
> 4. You probably have a caching disk controller to get in the way as well.
> My mental definition of fragmentation had unusable chunks of free space in
> the datafile (it is what an Oracle DBA would understand by fragmentation),
> and so long as there is a free page I can't see how this can happen in MSSQL
> hence my confusion. Thanks anyway.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission Uk
>