Indext extent fragmentation

Hi,
On the databases I manage I notice high (50%+) extent
fragmentation when I run DBCC SHOWCONTIG against the
tables' indexes. I've run dbcc indexdefrag, dbcc
dbreindex, and create index ...with drop existing on these
indexes and they still show the same fragmentation even
after an UPDATE STATISTICS.
Is there any way to eliminate this beyond completely
dropping the indexes and starting from scratch (not a
realistic option)? The procedure I run has virtually
eliminated the logical fragmentation- are there major
performance issues with leaving them as-is?
-DanHi,
If your select statement returns all the records of a table, such table
fragmentation can cause additional page reads which will reduce the
performance of your query and utilize more resource (Memory/CPU / Disk
reads- I/O. So INDEXDEFRAG will increase the performance on fragmented
table.
One more advantage is for fragmented tables, after INDEXDEFRAG / drop and
recreate index, all the fragmented pages will be cleared and you will have
enough
free space in your database.
Thanks
Hari
US Technology
Drop and re-create a clustered index. "Dan Wunder"
<anonymous@.discussions.microsoft.com> wrote in message
news:03c101c399a6$be7b5340$a401280a@.phx.gbl...
> Hi,
> On the databases I manage I notice high (50%+) extent
> fragmentation when I run DBCC SHOWCONTIG against the
> tables' indexes. I've run dbcc indexdefrag, dbcc
> dbreindex, and create index ...with drop existing on these
> indexes and they still show the same fragmentation even
> after an UPDATE STATISTICS.
> Is there any way to eliminate this beyond completely
> dropping the indexes and starting from scratch (not a
> realistic option)? The procedure I run has virtually
> eliminated the logical fragmentation- are there major
> performance issues with leaving them as-is?
> -Dan|||Please read the whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
This explains everything you need to know.
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OAqkB7dmDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If your select statement returns all the records of a table, such table
> fragmentation can cause additional page reads which will reduce the
> performance of your query and utilize more resource (Memory/CPU / Disk
> reads- I/O. So INDEXDEFRAG will increase the performance on fragmented
> table.
> One more advantage is for fragmented tables, after INDEXDEFRAG / drop and
> recreate index, all the fragmented pages will be cleared and you will
have
> enough
> free space in your database.
> Thanks
> Hari
> US Technology
>
> Drop and re-create a clustered index. "Dan Wunder"
> <anonymous@.discussions.microsoft.com> wrote in message
> news:03c101c399a6$be7b5340$a401280a@.phx.gbl...
> > Hi,
> >
> > On the databases I manage I notice high (50%+) extent
> > fragmentation when I run DBCC SHOWCONTIG against the
> > tables' indexes. I've run dbcc indexdefrag, dbcc
> > dbreindex, and create index ...with drop existing on these
> > indexes and they still show the same fragmentation even
> > after an UPDATE STATISTICS.
> > Is there any way to eliminate this beyond completely
> > dropping the indexes and starting from scratch (not a
> > realistic option)? The procedure I run has virtually
> > eliminated the logical fragmentation- are there major
> > performance issues with leaving them as-is?
> >
> > -Dan
>

Indexing question

Hello All,
Have seen 2 different databases now both on 2005 STD edition different
servers.
That still indicate fragmentation in their indexes (both clustered and non
clustered) immediately post rebuild proccess. I am looking for broad sweeping
statements to stimulate my thought processes as to why this is occuring.
Thanks in advance,
How many pages do they have? If it is less than 8 you will never get rid of
all the fragmentation since they used mixed extents. But if you show the
results it may help.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:62DBFA8F-1CC4-42ED-A7E4-0AFFB4626BEB@.microsoft.com...
> Hello All,
> Have seen 2 different databases now both on 2005 STD edition different
> servers.
> That still indicate fragmentation in their indexes (both clustered and non
> clustered) immediately post rebuild proccess. I am looking for broad
> sweeping
> statements to stimulate my thought processes as to why this is occuring.
> Thanks in advance,
>

Indexing question

Hello All,
Have seen 2 different databases now both on 2005 STD edition different
servers.
That still indicate fragmentation in their indexes (both clustered and non
clustered) immediately post rebuild proccess. I am looking for broad sweeping
statements to stimulate my thought processes as to why this is occuring.
Thanks in advance,How many pages do they have? If it is less than 8 you will never get rid of
all the fragmentation since they used mixed extents. But if you show the
results it may help.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:62DBFA8F-1CC4-42ED-A7E4-0AFFB4626BEB@.microsoft.com...
> Hello All,
> Have seen 2 different databases now both on 2005 STD edition different
> servers.
> That still indicate fragmentation in their indexes (both clustered and non
> clustered) immediately post rebuild proccess. I am looking for broad
> sweeping
> statements to stimulate my thought processes as to why this is occuring.
> Thanks in advance,
>

Indexes and Deadlocks

What are the exact reasons heavily fragmented Indexes can cause Deadlocks?
Is it purely because queries will run faster without fragmentation,
therefore lock escalation will happen quicker?
TIA, ChrisRbasically, yes.
Greg Jackson
PDX, Oregon