SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Time for a ROWID?


Time for a ROWID?

Author
Message
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5390 Visits: 6900
Steve, maybe it's because I haven't had enough caffeine this morning, but I have a question: How does the ROWID column that you propose differ from an identity column?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12219 Visits: 10648
mar10br0 (7/27/2011)
paul.knibbs (7/27/2011)
I'm not sure increasing the row length in SQL would help much--after all, the largest data types are stored in off-row data pages anyway, and a properly normalised database surely shouldn't need so many of the smaller data types that you'd need to increase the row length?

I don't think Steve was thinking of allowing for larger records, but rather lowering overhead-costs associated with managing fewer pages in the page-pool if the I/O costs for loading a larger page is negligible on modern hardware.
Microsoft would have to do some bench-marking to see if the vast majority of applications running on typical hardware would not be negatively impacted by a larger page-size if top-end hardware may benefit. Maybe even allow the sysadmin to choose the optimum page-size for his hardware when installing the server (if Microsoft did a good job with their source-code, they may be able to compile the server-kernel for different page-sizes).

It seems to me that there are performance advantages to having a smaller more granular page size, at least when querying non-clustered rows. If just one row is read from a page, then the entire page is copied to the buffer. So, if SQL Server's page size was increased from 8K to something like 64K, then the same query would require more storage in the buffer pool and thus more total memory. Of course you could always install more memory, but a larger page size would in many cases use the same memory less efficiently than would a smaller page size.
Does that sound right?


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87075 Visits: 45267
the.komplikator (7/27/2011)
GilaMonster (7/27/2011)

Errr, no...

Non-unique clustered indexes get a uniquifier, which is 4 bytes (an int) added to rows that have duplicate values for the clustering key.
Nonclustered indexes get either the clustering key (complete with uniquifier) if the base table has a clustered index, or the 8-byte RID (file_id, page_id, slot_index) if the base table is a heap.
If the nonclustered index is unique, that's present only in the leaf level, if the nonclustered index is not unique that is present in the leaf and non-leaf levels.


Right. No more early-in-the-morning posts for me Smile


:-D
I try not to post until I've had at least one cup of coffee. Otherwise really strange things get posted

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


stephen.lear
stephen.lear
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 38
I think the reason you can't access the internal ID is that it can change when a reorganization takes place. So you'd break any integrity that relied on the key. This is why we have identity fields that don't change by themselves.
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12219 Visits: 10648
stephen.lear (7/27/2011)
I think the reason you can't access the internal ID is that it can change when a reorganization takes place. So you'd break any integrity that relied on the key. This is why we have identity fields that don't change by themselves.

Oracle exposes the internal address of the row as a pseudocolumn called ROWID. However, I've never found a practical use for it. When it comes to de-duplicating, it's more appropriate to rank() the rows using a combination of columns. Even when dealing with a table that has true duplicates, the same column values straight across, the function row_number() can be used instead of a ROWID, and it's more useful because it's an integer. In Oracle, ROWID is represented as a 16 byte hex string.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
stephen.lear
stephen.lear
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 38
I've always had a problem with heap tables: The basic rule is don't do it. So you have to find something to use as a proper key. If the key is nondeterministic (like an address), then table inserts happen all over the place and pages get torn up. If you create your own primary key, then the table organization never relates to how the data is being used (similar records are not grouped). It's a problem as old as computing. The usual solution is a mix of brute force, blind ignorance, cunning and horsepower. Or programming as it's sometimes known.
krowley
krowley
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 429
Forgive a relative newbie to SQL internals but why can't the page size be variable per database or even per table? This would give the best of all worlds as a small table or one where selects usually only pull one record at a time could have a very small page size, and larger tables or ones where multiple rows are often pulled at the same time could have a larger page size.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7807 Visits: 25280
Steve is this what you are thinking of? (From Paul Randal's blog)

http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-New-(undocumented)-physical-row-locator-function.aspx

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
LSCIV
LSCIV
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 154
Eric M Russell (7/27/2011)
mar10br0 (7/27/2011)
paul.knibbs (7/27/2011)
I'm not sure increasing the row length in SQL would help much--after all, the largest data types are stored in off-row data pages anyway, and a properly normalised database surely shouldn't need so many of the smaller data types that you'd need to increase the row length?

I don't think Steve was thinking of allowing for larger records, but rather lowering overhead-costs associated with managing fewer pages in the page-pool if the I/O costs for loading a larger page is negligible on modern hardware.
Microsoft would have to do some bench-marking to see if the vast majority of applications running on typical hardware would not be negatively impacted by a larger page-size if top-end hardware may benefit. Maybe even allow the sysadmin to choose the optimum page-size for his hardware when installing the server (if Microsoft did a good job with their source-code, they may be able to compile the server-kernel for different page-sizes).

It seems to me that there are performance advantages to having a smaller more granular page size, at least when querying non-clustered rows. If just one row is read from a page, then the entire page is copied to the buffer. So, if SQL Server's page size was increased from 8K to something like 64K, then the same query would require more storage in the buffer pool and thus more total memory. Of course you could always install more memory, but a larger page size would in many cases use the same memory less efficiently than would a smaller page size.
Does that sound right?


Indeed. I'm in the same camp. I would rather have smaller page sizes. It's rare in an OLTP system to actually need 8K pages. Typical transactions are very small by nature so even 2K pages would suffice (we used 2K pages back in the old Sybase days). I would rather have the option of choosing the page size, say from 2K up to 32K for DW environments, instead of the current one-size-fits-all model of SQL Server.

Also, I don't think 'modern' technology has changed much for mechanical drives. Your typical enterprise/SAN hard drive hasn't changed much in the past ~10yrs. The seek times, IOPs, and rotation speeds (10K or 15K) are the same today as they were 10 yrs ago. The only thing that has changed is drive density, but drive performance has remained mostly flat if not slightly worse because of the multiple platters and data density within one drive spindle. Now if we're talking SSD's then that's an entirely different ball game. If you run a SAN filled with SSD's then not sure if any of this matters...
umailedit
umailedit
SSC-Addicted
SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)SSC-Addicted (469 reputation)

Group: General Forum Members
Points: 469 Visits: 246
There is ROWID in oracle but its absolutely useless. At the first reorganization or defragmentation etc. the rowids will change. If it cant change then the database can't defragment, as defragmentation by definition means changing the location of rows to different pages/files.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search