September 15, 2014 at 6:58 am
hi,
I in a table or partition, the first page is an IAM-Page if I'm not wrong, this page keeps track of the extents.
In the first extent to where the "first_iam_page" points to, the extent is a mixed extent, therefore the pages can be from different tables or partitions, correct? How does my IAM-Page map the right pages to the corresponding table? the following extents are all uniform of one table type, so I guess it doesnt matter then. But n, how does it keep track of which pages belong to which tables in the first extent?
My second question is, the first IAM-Page is obviously an IAM-Page, but there are also GAM , SGAM and PFS Page files... where are they stored? Because when I create a table and insert a big value(8000) into it, it reserves 16KB for that table, one for the IAM-page, and one for the first data-page. But where can I find the GAM,SGAM and PFS page files? or are they not page files, just some other structures?
September 15, 2014 at 7:09 am
Not Page Files. Pages. They're pages in the database, like any other page.
Try this: http://www.amazon.com/Microsoft-Server-Internals-Developer-Reference/dp/0735658560
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
September 15, 2014 at 7:27 am
yeah I mean they're not all file pages, but pages, there are 13 different pages.. so where are the GAM,SGAM and PFS pages stored ?
edit: ok I think I found out, I think they're stored separately in the tempdb...
September 15, 2014 at 7:39 am
They're database pages, they're in the database file, like all other database pages are.
No, they're not in TempDB. If they were, then you'd have to restore databases from backup after a SQL restart, since TempDB is completely recreated at startup.
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
September 15, 2014 at 11:40 am
ok, so can anybody tell me what a database file is ?
Every database file has its own chain of GAM and SGAM pages. The first GAM page is always the third page in the data file (page number 2). The first SGAM page is always the fourth page in the data file (page number 3). The next GAM and SGAM pages appear every 511,230 pages in the data files which allows SQL Server to navigate through them quickly when needed.
and what is a data file? if the GAM and SGAM pages are stored in the data file, where is that? is it a different place than the regular data pages that contain the rows?
September 15, 2014 at 11:44 am
freddyism00 (9/15/2014)
ok, so can anybody tell me what a database file is ?
Um, the data files of a SQL database. The ones that by convention have a .mdf or .ndf extension.
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
September 15, 2014 at 11:51 am
ah ok I understand, so in each database file, there are those type of pages somewhere that keep track of these data pages etc. and everything what is inside each table, am I right? and nobody knows where those GAM SGAM pages etc. are
September 15, 2014 at 11:55 am
Plenty of people know what those pages are, exactly where they are, what they do, etc. There's even a book that has a full chapter on the structure of the database file and pages. I linked it earlier.
You even quoted a paragraph earlier that stated exactly where the GAM and SGAM pages are.
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
September 15, 2014 at 12:10 pm
yeah, the third and the fourth page in the data file, what exactly is the data file? its not the .mdf file I think...?
it would be too easy to just say the answer, wouldn't it? 😉
September 15, 2014 at 12:52 pm
freddyism00 (9/15/2014)
yeah, the third and the fourth page in the data file, what exactly is the data file?
I already answered that
GilaMonster (9/15/2014)
freddyism00 (9/15/2014)
ok, so can anybody tell me what a database file is ?Um, the data files of a SQL database. The ones that by convention have a .mdf or .ndf extension.
it would be too easy to just say the answer, wouldn't it? 😉
I gave you a link to a book which has an entire chapter devoted to the structure of the database, the data files, the pages, etc. What do you expect me to do, write several pages on this for you?
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
September 15, 2014 at 1:35 pm
freddyism00 (9/15/2014)
yeah, the third and the fourth page in the data file, what exactly is the data file? its not the .mdf file I think...?it would be too easy to just say the answer, wouldn't it? 😉
Yeah, the data file is the .mdf (*.ndf, or even .doc if you choose to do that, don't). All data pages are persisted within that file. The exceptions are stuff like filestream that gets put into other storage locations (filestream is used with in-memory storage by the way), but even these are defined with the data files as part of what makes up the database. The stuff is stored in the files you define. It can't be stored anywhere else. It wouldn't make any sense.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply