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 «««1234»»

Clustered Indexes Expand / Collapse
Author
Message
Posted Tuesday, October 5, 2010 9:36 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:11 AM
Points: 703, Visits: 323
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.
Post #998503
Posted Tuesday, October 5, 2010 10:08 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:02 PM
Points: 21,628, Visits: 15,285
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #998536
Posted Tuesday, October 5, 2010 11:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 1:04 PM
Points: 1,364, Visits: 1,055
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.
Post #998626
Posted Tuesday, October 5, 2010 11:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 9:36 AM
Points: 340, Visits: 406
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.
Post #998629
Posted Tuesday, October 5, 2010 1:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:01 PM
Points: 1,613, Visits: 1,539
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #998720
Posted Tuesday, October 5, 2010 1:34 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:01 PM
Points: 1,613, Visits: 1,539
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 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #998727
Posted Tuesday, October 5, 2010 1:59 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Thanks for the question, I think it helps re-enforce some of the less known internal workings of SQL Server.
Post #998743
Posted Tuesday, October 5, 2010 2:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:44 AM
Points: 6,236, Visits: 7,380
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
Post #998758
Posted Tuesday, October 5, 2010 2:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 5,967, Visits: 8,221
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
Post #998762
Posted Tuesday, October 5, 2010 2:34 PM


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 @ 7:54 AM
Points: 42,768, Visits: 35,867
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 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 #998763
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse