June 8, 2011 at 11:02 am
SQL Server will use the RID as the Bookmark in a Heap's non-clustered index but how much space does the RID actually occupy on those index entries? i.e. is the RID comprised of 3 separate INTs and are they stored as such?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 1:01 pm
I think I found it but it was not as easy as I expected to find. From this article it looks like a physical RID occupies 8 bytes: [2 byte file id] + [4 byte page id] + [2 byte slot id]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 1:11 pm
That sounds right, but out of curiosity, are you trying to use this for something?
June 8, 2011 at 1:25 pm
Not directly, just in some analysis. I am evaluating an ETL system where data store tables that are directly queries by users and are actively accepting new data nightly are setup as heaps with multiple non-clustered indexes. I am trying to quantify the additional size of a non-clustered index on a heap versus the same non-clustered index on a clustered table. I may post another thread later because I am seeing massive fragmentation in some of the heap's indexes but am not sure where it's coming from. This is my first deep dive into the topic.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 6:42 pm
Non-clustered indexes on a clustered table use the clustering key instead of RID. So if you can keep the clustering key narrow the size of the non-clustered index will be kept to a minimum. Clustered indexes bring a lot of benefits with them. Since a clustered index is implemented as a B-Tree with linked lists the OS can pre-fetch related pages into memory making it much more likely that once you start a query all the data pages will be in readily available instead of the engine having to search for each page as it encounters a new RID.
It is not always necessary to drop the indexes before loading but it is not uncommon to drop and rebuild after loading large batches.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply