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


Clustered Indexes


Clustered Indexes

Author
Message
rtelgenhoff
rtelgenhoff
Right there with Babe
Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)

Group: General Forum Members
Points: 748 Visits: 366
the cost of changing the physical order within a page is not that much; the page is in cache anyway, it is alwayys read and written as a whole, so the only cost is that of moving at most 8,000 bytes around in a memory block.


I have to agree with Hugo on this. I've done a lot of work in C++ with parsing text files and sorting and caching data. I/O (disk/network) is where you really take the hit. In-memory manipulations using efficient, low-level code can be very fast.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66963 Visits: 18570
Nice question.

Thanks Hugo and Gail for explaining this quite nicely.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Rose Bud
Rose Bud
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1791 Visits: 1062
I regretfully find myself in the 66% majority who answered this question incorrectly. Hopefully, all 529 of us learned something. I know I did. Thanks for the question.
getoffmyfoot
getoffmyfoot
SSC Eights!
SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)SSC Eights! (816 reputation)

Group: General Forum Members
Points: 816 Visits: 412
wware (10/5/2010)
I regretfully find myself in the 66% majority who answered this question incorrectly. Hopefully, all 529 of us learned something. I know I did. Thanks for the question.


+1

Best QotD in a long time.
Robert Davis
Robert Davis
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6664 Visits: 1632
Whether you think SQL Server should be preserving physical order of the data or not doesn't matter. The facts of the case is that it only preserves logical ordering of the data. If a new record is added to an existing page, SQL Server puts it in the first spot with enough space whether that is in the correct physical order or not.

Additionally, if SQL Server needs to split the page to insert a record, it does not split the page at the point where it needs to insert the record. It splits it approximately in half and then inserts the record into the page in which it logically fits. Again, this would be at the first slot big enough for it fit the record in and may even be the last record in the page physically even though logicially is in the middle or beginning or anywhere else.

Also, SQL Server NEVER reads in records from disk. It reads in pages, the location of the record on the page does NOT affect the number disk IO's required to read in the page. Reading in a single page of data is 1 IO whether it is physically sorted by the data or not. So maintaining the physical order of the data in memory would increase the memory workload for the server wihtout getting any benefit.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
Robert Davis
Robert Davis
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6664 Visits: 1632
rtelgenhoff (10/5/2010)

I have to agree with Hugo on this. I've done a lot of work in C++ with parsing text files and sorting and caching data. I/O (disk/network) is where you really take the hit. In-memory manipulations using efficient, low-level code can be very fast.


Since the data is written and read in whole pages only, why would you add extra overhead to your memory operations when you wouldn't be getting any benefit from it?

In-memory manipulations are indeed fast, but SQL is not written to do 1 thing at a time. We're talking about hundreds, thousands, or even millions of memory operations per second. Do you really want to increase the workload for memory operations by a factor of as much as 8000 times?

Additionally, if I'm inserting a row, and SQL has to reorder all of the data in the page in memory, I now have to be able to get a lock on the whole page in order to insert a single row that no other process is trying to use. I've now lost all ability to lock only a single row.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
UMG Developer
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4056 Visits: 2204
Thanks for the question, I think it helps re-enforce some of the less known internal workings of SQL Server.
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20735 Visits: 7660
I got it right, but a question regarding dedicated extents. I know page splits will happen, etc etc, but does an extent stay compact for a range of data, or will it split out of the extent and return to it with page pointers?


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18873 Visits: 12426
Craig Farrell (10/5/2010)
I got it right, but a question regarding dedicated extents. I know page splits will happen, etc etc, but does an extent stay compact for a range of data, or will it split out of the extent and return to it with page pointers?

If you ask what I think you ask, then the answer is that, if a page has to be split, the new page will almost certainly be allocate in a different extent.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
GilaMonster
GilaMonster
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225781 Visits: 46321
Craig Farrell (10/5/2010)
I got it right, but a question regarding dedicated extents. I know page splits will happen, etc etc, but does an extent stay compact for a range of data, or will it split out of the extent and return to it with page pointers?


Test it out and see (hint DBCC IND and DBCC PAGE)

Think about it. What happens if all 8 pages in the dedicated extent are full and a row is inserted that has to go onto page 4 of the extent?

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


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