SQL 2k - Physical Data Structure : PFS+GAM+SGAM+IAM

Question

SQL 2k - Physical Data Structure : PFS+GAM+SGAM+IAM

Ramanuj

SSC Veteran

Points: 278
More actions
October 3, 2004 at 7:09 am

#109594

Hi ; I am Ramanuj from India.
I am interested in knowing more about how SQL server internally stores the data (PFS, GAM, SGAM etc etc ). I have gone through "Inside SQL Server 2000" by Kalen Delaney; but still have many queries like how IAM, firstIAM, first, root are all mapped. Can anyone suggest any URL which explains more of it in details.
Also is there any tool which will display the physical data in the file (similar to disk defragmentation), including free-space, Exten/page, IAM etc w.r.t. the objects it is allocated to.
thanks,
ramanuj

🙂

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

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

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 1

If have to confess my ignorance about not knowing the PFS, GAM, and SGAM acronyms. As for viewing the data, I think what you are after are the DBCC TAB, DBCC PRTIPAGE, and DBCC PAGE undocummented commands. Following is one of many different links that explain undocummented DBCC commands, but I have yet to find one that explains them any better than this (but I wish I could). The DBCC PAGE command is difficult to understand and I don't have a good handle on it yet. Good luck. Also, there is the DBCC SHOWCONTIG that gives higher level fragmentation information. The good news is that SHOWCONTIG is a documented command within BOL and is very usefull.

http://www.databasejournal.com/features/mssql/article.php/1442251

Hopefully this addresses the second part of your question.

swjohnson Hall of Fame Points: 3254 More actions · Answer 2

Hello Ramanuj,

The best book that I have read on the subject is Ken England's Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook (ISBN: 1-55558-241-9).

Yes, the title is a bit misleading but he does go into quite a bit of detail about how data is phycially stored, query optimizations, transactions and locking processes, indexes, and the obvious performance tuning.

I don't have the book next to me right now but I do remember some things in the book that talk about commands to see how the data is stored...If I remeber correctly "DBCC Page" and the syntax is DBCC PAGE (dbid | dbname, fileID, pagenumber) or for example DBCC PAGE ('Northwind',1,30)

Please note that DBCC Page is undocumented, please use at your own risk.

Good luck!

Shawn

Accepting all invites

Ramanuj SSC Veteran Points: 278 More actions · Answer 3

Thanks for all the information.

I have gone through Ken England's perf. Opt book as well.

I am also aware of DBCC PAGE, DBCC EXTENTINFO, etc DBCC commands, but I am still looking for an article which explains a sample small DB with reading all the pages of a table logically to retreive data using DBCC PAGE; therby also explaining in details the functionality/logical of IAM, root, firstIAM etc etc with an example.

Any help on this is much appreciated !

thanks in advance,

ramanuj

🙂

Koby BILLER Old Hand Points: 367 More actions · Answer 4

I hope it is not too late for you.

Even if you have the physical data structure on the disk, it is far from taking conclusions whether the situation is good or bad.

I am not an SQL expert. My main area is External Fragmentation. I am exploring into Internal Fragmentation, and lookong for a counterpart in the SQL side.

If someone is challanged, please have a look at

my site, where I describe the concept, and answer me through the site.

Thanks,

Koby BILLER

http://www.disklace.com

DISKLACE

Ramanuj SSC Veteran Points: 278 More actions · Answer 5

Thanks Koby for your efforts to help me out of the problem. Your website provides information of OS Disk fragmentation in general.

I am looking for Fragmentation, internal allocation of data from SQL Server point of view.

anyways, thanks a lot.

ramanuj

🙂

Koby BILLER Old Hand Points: 367 More actions · Answer 6

ramanuj,

I was not only trying to help you, but to all SQL community. The problem of fragmentation hits many areas, and I am trying to offer my help to you, SQL guys.

It is still possible, if any SQL expert will see this message, and contact me, and together we'll be able to create something.

What you saw in my site, is only a demonstraion of the answers that the SQL world will be able to get, in response to the question "How much my data base is fragmented."