question about the undocumented function sys.fn_PhysLocFormatter

Question

question about the undocumented function sys.fn_PhysLocFormatter

Alexander Kovacs

Hall of Fame

Points: 3075
More actions
September 2, 2008 at 3:27 am

#192137

When I was looking at sys.fn_PhysLocFormatter I had the following question.
what is the file, page and slot values of a memory table.
I expected an error or random values, but it was not random
My question is: How can I understand file, page and slot values of sys.fn_PhysLocFormatter in a memory table
declare@Memory_Table table
(
[desc] nvarchar(5),
[ID] int
)
insert into
@Memory_Table
([desc],[ID])
select N'A',1 union all
select N'B',2 union all
select N'C',3 union all
select N'D',4 union all
select N'E',5
select
sys.fn_PhysLocFormatter (%%physloc%%) as [Physical RID] ,
[desc],
[ID]
from
@Memory_Table

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Just like temp tables, table variables are physically stored in the tempdb. So the values returned by sys.fn_PhysLocFormatter should point to the file, page of the tempdb.

[font="Verdana"]Markus Bohse[/font]

Alexander Kovacs Hall of Fame Points: 3075 More actions · Answer 2

hi ,

You are saying that table variables always are stored in tempdb.

the reason I was asking the question was the following line from http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

The table variable is NOT necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb. Here is one example shows space taken by table variable in tempdb

I was thinking if it is "NOT necessarily" is should be possible that it could be in memory.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Table variables are always added to the TempDB system tables and are allocated space within TempDB. They won't actually be written to disk unless there's memory pressure, but the framework is created.

Just because something has a pageID doesn't mean it's on disk. Data's stored in pages in memory too, and those pages have the full page header, page footer, slot indexes and pageIDs that the on-disk ones have.

As far as I know, the structure of a page in memory is identical to the structure of a page on disk.

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

Alexander Kovacs Hall of Fame Points: 3075 More actions · Answer 4

Alexander Kovacs

Hall of Fame

Points: 3075

September 3, 2008 at 11:21 am

#866669

thank you for the explanation

amoericke SSC Eights! Points: 978 More actions · Answer 5

The structure of the page in memory is the same as a page on disk, but the information in the header is slightly different. The header is 96 bytes. 64 bytes are the relevant header information you see if you look at a page on disk. 32 bytes are only relevant while the page is in memory. This 32 byte "buffer header" holds information like whether the page is clean or dirty, the reference count, etc......information that is needed while the page is in memory.

This information came from Kalen Delaney's "SQL 2008 Architecture, Internals and Tuning" course. Although I haven't checked, I'm sure you can find it in her book (which I'm sure explains it much better than I just did).