February 28, 2011 at 11:32 pm
I was unsure about the first option: The row is moved to a new page
Got it wrong. May be, the first option could have been more clear.
But the question is great. Thank you 🙂
M&M
March 1, 2011 at 12:20 am
This was removed by the editor as SPAM
March 1, 2011 at 1:06 am
My guess is 97% wrong indicates most people assumed a) was implicit in c) - even the UK post office would leave a forwarding pointer without the recipient moving (well, not often):-D
March 1, 2011 at 1:23 am
Definately learned something today. Thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 1, 2011 at 1:54 am
Got the question right, but why is a page full with 4 rows á 1000 bytes?
Best Regards,
Chris Büttner
March 1, 2011 at 2:47 am
I always thought the nonclustered indexes WERE updated when a page split occurred--seems inefficient (from the query side of things, anyway) for them to be pointing at a forwarding pointer to a different page!
March 1, 2011 at 2:55 am
Like christian I am wondering why the page is full. Can someone explain why a page with 3 x 1000 bytes rows, 1 x deleted 1000 byte row (now a forwarding record) and 1 x new 1500 byte row, totaling 5500 bytes doesnt fit in an 8kb page? Since there is no clustered index to control fill factor do heaps have a default fill of 50% and does the deleted row count in this total until after the transaction is completed?
I also thought that the non-clustered indexes were updated to point to the new forwarded record location. Does anyone have a link to BOL as I need to read up on heaps? Great question.
March 1, 2011 at 3:04 am
Ack! Got it wrong, slightly, but enough to lose the points! :ermm:
The explanation I found in this article http://sqlserverpedia.com/wiki/Heaps under heading Forwarded Records said...
Any non-clustered indexes on the heap have to be updated with the new location for this data, resulting in overhead.
...which according to the explanation here is wrong. My new understanding is that the non-clustered index does not get updated, but instead points to the same location now containing a forwarding record pointing to the new location, a double seek effectively I'm imagining.
Did I understand correctly?
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
March 1, 2011 at 3:10 am
Not a simple question, but as I write I'm surprised to see only 11% correct answers.
March 1, 2011 at 3:20 am
DugyC (3/1/2011)
Ack! Got it wrong, slightly, but enough to lose the points! :ermm:The explanation I found in this article http://sqlserverpedia.com/wiki/Heaps under heading Forwarded Records said... Any non-clustered indexes on the heap have to be updated with the new location for this data, resulting in overhead. ...which according to the explanation here is wrong.
I found that too, along with:
Page splits can arise in another way. When an existing record that has a variable length column is updated with a larger value than the existing one, SQL Server has to move half of the records to a new page to accommodate the increased column length
... so that's all wrong? you don't get a page split and indexes are not updated?
March 1, 2011 at 3:50 am
dave.farmer (3/1/2011)
... so that's all wrong? you don't get a page split and indexes are not updated?
"Move half the records to a new page" *is* a page split. Still seems contradictory about the index update, though!
March 1, 2011 at 4:56 am
I've checked the link to SQLServerPedia, but I still disagree that a page in a heap is splitted, just because a row is longer.
I wrote this example:
BEGIN TRY
DROP TABLE tmp.hsp
END TRY
begin CATCH
END CATCH
go
CREATE TABLE tmp.hsp (i INT IDENTITY(1,1), txt VARCHAR(5000))
INSERT INTO tmp.hsp (txt) VALUES ('test1')
INSERT INTO tmp.hsp (txt) VALUES ('test2')
INSERT INTO tmp.hsp (txt) VALUES ('test3')
INSERT INTO tmp.hsp (txt) VALUES ('test4')
SELECT top 10000 * FROM sys.dm_db_index_physical_stats (DB_ID('datawarehouse'), OBJECT_ID('tmp.hsp'), 0, 0, 'detailed')
/* only one page at the moment */
go
UPDATE tmp.hsp SET txt='longer text' WHERE i=1
SELECT top 10000 * FROM sys.dm_db_index_physical_stats (DB_ID('datawarehouse'), OBJECT_ID('tmp.hsp'), 0, 0, 'detailed')
/* still only one page even if we updated a varchar field and made it longer */
go
UPDATE tmp.hsp SET txt='longer text'+REPLICATE('1', 4000) WHERE i=1
SELECT top 10000 * FROM sys.dm_db_index_physical_stats (DB_ID('datawarehouse'), OBJECT_ID('tmp.hsp'), 0, 0, 'detailed')
/* still only one page even if we updated a varchar field and made it longer, but without exceeding the page */
UPDATE tmp.hsp SET txt='longer text'+REPLICATE('1', 4000) WHERE i=2
SELECT top 10000 * FROM sys.dm_db_index_physical_stats (DB_ID('datawarehouse'), OBJECT_ID('tmp.hsp'), 0, 0, 'detailed')
/* Now two pages because we exceeded the page size, and required another page */
go
So I do not think the answer is correct; the page is not split, because it is not filled up as a result of the update.
Am I wrong?
Best regards,
Henrik Staun Poulsen
March 1, 2011 at 5:31 am
First of all thank you very much for the question. Excellent to recap how SQL Server stores data and index information. 🙂
Following the same link to SQLServerPedia (http://sqlserverpedia.com/wiki/Heaps), the follwing information is found a little later in the article:
Forwarded Records
As explained earlier, fillfactor can’t be specified for heaps, and page splits do not happen in heaps; they occur only for clustered and non-clustered indexes.
Also, non-clustered indexes will not get updated automatically--if you want to get rid of the forwarding pointer in your heap, you'd need to manually rebuild your table and non-clustered index(es).
Found this example on the internet--please check out Tibor's blog at http://sqlblog.com/blogs/tibor_karaszi/archive/2009/08/28/heaps-forwarding-pointers-alter-table-rebuild-and-non-clustered-indexes.aspx on how this can be achieved.
Regards,
Michael
March 1, 2011 at 5:35 am
Christian Buettner-167247 (3/1/2011)
Got the question right, but why is a page full with 4 rows á 1000 bytes?
This confused me as well. I concluded the fields are defined as nvarchar, so 4 rows would fill the page.
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply