Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Time for a ROWID? Expand / Collapse
Author
Message
Posted Wednesday, July 27, 2011 7:11 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
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? -- Stephen Stills
Post #1149127
Posted Wednesday, July 27, 2011 7:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 1,642, Visits: 4,679
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?
Post #1149179
Posted Wednesday, July 27, 2011 7:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 42,756, Visits: 35,847
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 :)



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 2008, MVP
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

Post #1149180
Posted Wednesday, July 27, 2011 7:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 19, 2014 3:43 PM
Points: 8, Visits: 36
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.
Post #1149183
Posted Wednesday, July 27, 2011 7:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:23 AM
Points: 1,642, Visits: 4,679
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.
Post #1149199
Posted Wednesday, July 27, 2011 7:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 19, 2014 3:43 PM
Points: 8, Visits: 36
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.
Post #1149201
Posted Wednesday, July 27, 2011 8:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:04 PM
Points: 113, Visits: 421
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.
Post #1149234
Posted Wednesday, July 27, 2011 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 5,586, Visits: 24,925
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
Post #1149236
Posted Wednesday, July 27, 2011 8:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:38 AM
Points: 22, Visits: 148
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...
Post #1149243
Posted Wednesday, July 27, 2011 8:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, May 4, 2014 7:48 PM
Points: 369, Visits: 217
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.
Post #1149249
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse