May 10, 2006 at 2:26 pm
I have a rather specific I/O question that came up in a discussion about storing metadata in tables. When I say metadata, I mean columns like CreatedBy, CreatedDate, ModifiedDate, ModifiedBy, etc.
In this specific case, this type of column will never be returned to the interface. So, the debate surrounded Clustered Scan index seeks and I/O as it relates to the non-returned columns.
Which of the below scenerios is an accurate depiction of SQL Server's I/O?
TABLE tblCompositeKey (ID1, ID2, Description, CreatedBy, CreatedDate, ModifiedDate, ModifiedBy)
CLUSTERED KEY (ID1, ID2)
SELECT Description FROM tblCompositeKey WHERE ID1=? AND ID2=?
SCENERIO 1:
The clustered index "is" the table so all table rows related ti ID1 are paged into memory, pages are discarded based on ID2<>?, when selection is ready the correct "Description" value is immediately returned becuase it has ALREADY been paged into memory.
SCENERIO 2:
The B-Tree level of the index only holds ID1 and ID2 (right???) so the B-Tree pages with ID1=? are paged into memory, then B-Tree pages are discarded based on ID2<>?, finally the records to be selected result in an additional logical I/O ONLY to the leaf level (table level) pages that satisfy both criteria.
Obviously, the "metadata" columns have less I/O effect in Scenerio 2.
Anyone know which it is?
May 10, 2006 at 2:48 pm
IN SQL Server the table data is stored at the leaf level of the clustered index. So scenario 1 would be correct.
hth
David
ps from BOL a better description
In SQL Server, indexes are organized as B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and leaf nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list.
May 10, 2006 at 2:57 pm
Sorry think i miss read your question.
The index would be traversed using the values of using the values of ID1 & ID2 and when it reached the leaf level it would have reached the actual data row for the table so no other IO would result.
So all the data rows would not be paged into memory just the node & intermediate levels of the index as it was traversed and then finally the data page at the leaf level which actually contains the row you are looking for.
hth
David
May 10, 2006 at 3:18 pm
David, based on your response, it looks like Scenerio 2 is correct.
Essentially, the intermediate pages will be traversed (paged into memory and processed) based on the values of ID1 and ID2. Since the intermediate level only contains ID1 & ID2 (and some sort of data row locator I presume) then I/O amounts in bytes will not be increased based on the size of the total table at leaf level. However, once the correct table rows have been determined, leaf nodes (in pages) will be paged into memory in order to satisfy the selected column: Descrption.
Does this sound right?
May 10, 2006 at 3:24 pm
Part of my confusion... This is from BOL:
"For a clustered index, sysindexes.root points to the top of the clustered index. SQL Server navigates down the index to find the row corresponding to a clustered index key. To find a range of keys, SQL Server navigates through the index to find the starting key value in the range, and then scans through the data pages using the previous or next pointers. To find the first page in the chain of data pages, SQL Server follows the leftmost pointers from the root node of the index."
I think the explanation leave a lot to be desired. Is it essentially saying that seeks use intermediate nodes while scans go directly to the leaf level?
May 10, 2006 at 3:47 pm
Yes on a clustered index an index scan is a table scan as it is scan of the leaf level which is the table data. i.e it does not traverse the index at all it goes straight to the leaf level and scans all the leaf level data pages.
Whereas a seek traverses the index from the root through the intermediate levels arriving at the leaf level page which contains the row of data you are looking.
hth
David
May 15, 2006 at 5:09 am
rhunt,
Scenario 1 and scenario 2 have identical I-O until you want to access the Description column. Scenario 1 means the page with the description may be in the same extent as the parent index page, resulting in no I-O. Scenario 2 makes the co-location of the page in the same extent far less likely so more I-O would be needed (maybe also a clustered index lookup).
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 15, 2006 at 6:58 am
Ed, I'll admit that I am a bit confused by your response. The two scenerios I described above were two different scenerios(I intended them to be opposing) that I had rattling around in my head about how clustered indexes affected I/O related to columns not found in the clustered index. I don't see how they could both be right.
The problem with my scenerio 1 was that it assumed that the entire row (all columns in the table) was read into memory when the clustered index was read. This was because I was misunderstanding the columns to all be on the same 8k pages. After further review, it seems that scenerio 2 was more correct because of the way intermediate pages are used to drive down to the leaf level.
However, you have sparked my interest with your description of extents. Are you sure the intermediate and leaf level pages are on the same extents? If that were the case, scenerio 1 starts to look more correct because this would have similar implications to storing everything on the same pages. You've made it sound like:
1) SQL Server pages in extents holding intermediate AND leaf level pages related to ID1=?.
2) SQL Server discards records where ID2<>?
3) SQL Server already has the leaf level page with relevant "Description" values because the were contained on the extents already in memory (no additional I/O is required)
Is this what you were saying?
May 15, 2006 at 7:19 am
Apart from the bottom-level leaf pages, clustered and non-clustered indexes are the same. Therefore any discussion of I-O load should focus on what happens when pocessing the bottom-level only.
In a Non-clustered index, the index pages and data pages will always be in different extents, so there will always be at least 1 I-O needed to get to the data when you get to the end of the NC index. (The only possible exception is if the table is so small that all relevant data is in the shared extent.)
In a Clustered index, we know the bottom level leaf pages are also the data pages. We also know that all SQL I-O is done in units of extents, not pages. This gives the possibility that the parent index page is in the same extent as its data pages, and if not it is likely to be close by (maybe close enough so that pre-fetch would have already read the extent into memory). Therefore we may not need any additional I-O to access the data.
There are some situations where having a clustered index on a table is not good for performance. Typically, this would be when you have a high insert or update level would cause many index page splits, and very few or no range-based queries are issued against the clustered index. In just about all other situations a clustered index gives a performance benefit.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 15, 2006 at 7:57 am
Interesting. Thanks for all the info. I have been curious about this because I am concerned about the new over-head being introduced into our DB designs since the advent of SOX. Just about every table now has to end with [CreatedBy, CreatedDate, UpdatedBy, UpdatedDate] These columns are a paper trail only and are never used by applications. In fact, we've never really had a reason to use them for admin purposes either. So, I have been toying with the idea of breaking these "audit" columns into separate tables with a foreign key.
That way, this:
tblWhatever: PKID, Col1, COl2, Col3, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate
...can become:
tblWhatever: PKID, Col1, Col2, Col3
tblWhateverAudit: PKID, FKID, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate.
Based on your statement "This gives the possibility that the parent index page is in the same extent as its data pages", even an index seek on tens of thousands of key values (once this table has record counts in the millions) will result in far more extents/pages in memory than is required if the audit fields are not broken out.
Thanks again for the info.
RH
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply