March 20, 2012 at 8:05 am
derekr 43208 (3/20/2012)
I don't get this because I understand that Fragmentation on an Index has no affect on SQL finding a value in an Index - Right?
Correct, finding a row (which is a seek operation) is unaffected by fragmentation regardless of whether the index is clustered or nonclustered. It is, as I've said before, large range scans that are affected by fragmentation
I get the Clustered Index part but if the Non-Clustered Index just contains pointers to the data how does it get fragmented?
A nonclustered index does not just contain pointers to the data. It contains columns - the index key columns, any include columns and the clustered index key. It's in fact identical in structure to a clustered index (just not with the entire data row at the leaf level). Hence, scans of a nonclustered index are just as likely to be affected by fragmentation of the nonclustered index as scans of a clustered index are to being affected by fragmentation of the clustered index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2012 at 4:45 am
A nonclustered index does not just contain pointers to the data. It contains columns - the index key columns, any include columns and the clustered index key. It's in fact identical in structure to a clustered index (just not with the entire data row at the leaf level). Hence, scans of a nonclustered index are just as likely to be affected by fragmentation of the nonclustered index as scans of a clustered index are to being affected by fragmentation of the clustered index
Ok, let's say that we have Table_1 with the following Columns:
Col_1 int
Col_2 int
Col_3 int
1. We build a Non_Clustered Index on Col_1 only.
That Index then contains only the actual data from Col_1
2. We build a Clustered Index on Col_1 and a Non_clustered index on Col2
Again - That Index then contains only the actual data from Col_2 because the Clustered Index becomes the table
Am I correct in this?
Thanks
March 23, 2012 at 4:56 am
No, the nonclustered indexes always contain the clustered index key.
So your nonclustered index on Col2 will actually be Col2, Col1 or Col2 inlcude Col1, depending whether the nonclustered index is unique or not.
Please take a read through these, I'm getting the impression that you've got an incorrect view of how indexes are structured
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2012 at 4:58 am
Almost. The non-clustered index contains the indexed column(s) and the clustered index column(s), just as the index at the back of a book contains the word you're looking up and the page number. If there is no clustered index, SQL Server uses an internally generated row identifier.
John
March 23, 2012 at 5:15 am
GilaMonster (3/23/2012)
No, the nonclustered indexes always contain the clustered index key.So your nonclustered index on Col2 will actually be Col2, Col1 or Col2 inlcude Col1, depending whether the nonclustered index is unique or not.
Please take a read through these, I'm getting the impression that you've got an incorrect view of how indexes are structured
http://www.sqlservercentral.com/articles/Indexing/68439/
Will Do!
On fragmentation, based on this discussion and some Internet searching
Fragmentation is when the leaf pages of an Index are logically out of order(out of the order in which SQL can order it best) and extent fragmentation is when an extent(8 leaf pages) is not next to the one its supposed to be.
The performance degradation only comes in where SQL needs to perform a large range scan, something like select some_column from some_table where some_column between two or more values very far apart from each other
Correct?
Thanks
I'll give your articles a read
March 23, 2012 at 5:25 am
derekr 43208 (3/23/2012)
The performance degradation only comes in where SQL needs to perform a large range scan, something like select some_column from some_table where some_column between two or more values very far apart from each other
Exactly what I said several times already
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2012 at 5:28 am
GilaMonster (3/23/2012)
derekr 43208 (3/23/2012)
The performance degradation only comes in where SQL needs to perform a large range scan, something like select some_column from some_table where some_column between two or more values very far apart from each otherExactly what I said several times already
Great - Thanks
I think the site owners need to delete this post before I add to it again!
Viewing 7 posts - 46 through 51 (of 51 total)
You must be logged in to reply to this topic. Login to reply