What system table maps user tables to file/pages ?

Question

What system table maps user tables to file/pages ?

SQL Guy 1

SSCoach

Points: 15979
More actions
February 14, 2011 at 10:07 am

#388850

This is a little bit more theoretical question.
If I issue a query Select * from some_table, how storage engine finds exact file number/name and all pages numbers that belong to some_table ? What system table or allocation map has it ?
Thanks

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

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

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 1

Have a look at "Managing Space Used by Objects" for the internals, how the engine manages it all.

Have a look at the poster Aaron Bertrand points to: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/01/28/sql-server-2008-system-views-poster-is-now-available-for-download.aspx to get a look how we can see the catalog data.

This is the kind of query you may be looking for to get an overview yourself ....

SELECT SUBSTRING(FG.name, 1, 30) AS FilegroupName

, schema_name(O.schema_id) as SchemaName

, O.name AS ObjectName

, X.index_id

, X.name as IxName

, X.type_desc

, DbF.name as DbFileName

, DbF.physical_name as PhysicalFileName

FROM sys.Objects O

INNER JOIN sys.indexes X

ON O.object_id = X.object_id

INNER JOIN sys.filegroups FG

ON X.data_space_id = FG.data_space_id

INNER JOIN sys.database_files DbF

on DbF.data_space_id = X.data_space_id

WHERE O.type = 'U'

ORDER BY FilegroupName

, ObjectName

, index_id

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

SQL Guy 1 SSCoach Points: 15979 More actions · Answer 2

Thanks for quick reply, I found files for my table, but this is only 50% of what I wanted: I want to find page numbers as well.

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

There's no system table for that. You'll need an undocumented function.

Described here: http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Using-DBCC-PAGE-and-DBCC-IND-to-find-out-if-page-splits-ever-roll-back.aspx

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

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 4

I think your only resource then would be

DBCC ind ( <database_name>, <table_name>, non clustered index_id*)

Have a look at Paul Randal s blog for more info of dbcc ind and dbcc page:

http://www.sqlskills.com/BLOGS/PAUL/post/Inside-the-Storage-Engine-Using-DBCC-PAGE-and-DBCC-IND-to-find-out-if-page-splits-ever-roll-back.aspx

edited: I noticed Gail pointed to Pauls blog at about the same time I replied :w00t:

another ref "http://strictlysql.blogspot.com/2010/08/dbcc-ind-dbcc-page-intro.html"

Regarding DBCC.

DBCC is actually a proof for the world to be round.

One way or the other you'll end up at Paul Randals http://www.sqlservercentral.com/Forums/UserInfo285448.aspx pages. Have a look at his signature and you'll find our he wrote parts of the thing.