May 10, 2011 at 12:38 am
I have a table which name is QUALITYERROR and it has a index which name is IX_5 when ı run
DBCC SHOWCONTIG ('QUALITYERROR',IX_5) WITH FAST
Pages Scanned................................: 44511
- Extent Switches..............................: 18150
- Scan Density [Best Count:Actual Count].......: 30.65% [5564:18151]
- Logical Scan Fragmentation ..................: 97.34%
after ı run
ALTER INDEX ALL ON QUALITYERROR REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE= ON, STATISTICS_NORECOMPUTE = ON,PAD_INDEX =ON)
it is still
Pages Scanned................................: 44511
- Extent Switches..............................: 18150
- Scan Density [Best Count:Actual Count].......: 30.65% [5564:18151]
- Logical Scan Fragmentation ..................: 97.34%
but when ı drop and recreate index it is
LEAF level scan performed.
- Pages Scanned................................: 49050
- Extent Switches..............................: 6135
- Scan Density [Best Count:Actual Count].......: 99.93% [6132:6136]
- Logical Scan Fragmentation ..................: 0.01%
what is the reason of this
May 10, 2011 at 2:08 am
ONLINE= ON,
What I think is..When this option is selected it uses the double memory used by the index.
Thanks
May 10, 2011 at 2:09 am
Maybe but why i can t reduce fragmentaion and why i need drop and create index to reduce fragmentation
May 10, 2011 at 3:11 am
Logical Scan Fragmentation:
Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps and text indexes. An out of order page is one for which the next page indicated in an IAM is a different page than the page pointed to by the next page pointer in the leaf page.
May I know Which Type of index it is.....
Thanks
May 10, 2011 at 5:53 am
Did you define the PAD_INDEX option ON and fillfactor same as before ? what is the Server default fillfactor configurations ?
check the default configurations of the Server or fillfactor by the help of
Select * from sys.configurations
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 10, 2011 at 6:43 am
server fill factor is 70
May 10, 2011 at 7:04 am
online does not use double memory - where on earth did that come from ! sigh! If you pad indexes you'll have a low scan density - actual fragmentation is 100 - scan density so 30% would = 70% pad.
I expect yoour drop and recreate isn't using the padding - well that would be my interpretation of the results you posted.
Padding indexes rarely gives you the advantages you might expect to get - at best you'll make your database bigger and increase the io on your indexes.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 10, 2011 at 7:49 am
In my rebuild index query if i made ONLINE= OFF and everythink is oke what is the diffrence between thats????
May 10, 2011 at 2:33 pm
nothing wrong with online index rebuilds as long as your system is quick, some types of indexes cannot be built online so do be careful. .. I was remarking on the statement that an online index rebuild uses double memory.
however I suppose it's a matter of symantics with essentially the index being built in parallel and then swopping in.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 11, 2011 at 4:13 am
the online = OFF means that the index in question will not be Accessible during the rebuild.
May 11, 2011 at 4:16 am
that is the DML intensive table ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 11, 2011 at 4:30 am
no
May 11, 2011 at 5:23 am
I regret for my post
ONLINE= ON,
What I think is..When this option is selected it uses the double memory used by the index.
Thanks,
ForSqlServer
"Feel Fear And Do It AnyWay"
Thanks
May 11, 2011 at 7:51 am
yeah well sort of; essentially the new index is built in the background leaving the original index available for queries; once it has finished it is swopped in against the original index the the original uindex is dropped. Dropped objects would be removed from cache by natural processes. As was pointed out earlier the advantage of online rebuilds is that the table is not constantly blocked during the index rebuild, however if you have a slow system, a large index and a volatile table it is an outside possibility that the index never actually finishes rebuilding - then that does consume resource. I must try to test this to see if I can load a table with so many updates and inserts that the index cannot be rebuilt - would be an interesting exercise!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply