How database grows?

Question

How database grows?

Thinky Night

SSCommitted

Points: 1663
More actions
April 12, 2012 at 2:17 am

#254663

Hi All,
I wonder this question?
First I create an empty db. And create a table. Then i insert some rows to the table. What Happens?
Is First an extent created with 8 pages? Then storage engine uses first page on the extent?
OR
Does storage engine creates first page on the disk? Then pages will create an extent?
Any Ideas?
Regards,

Viewing 9 posts - 1 through 8 (of 8 total)

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

It's going to allocate an extent first then put pages there, but why does it matter?

"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

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

When a database is created, it's a specific size (the size of Model or the size you specified)

First page allocated from a new table, SQL will look for a mixed extent with at least one free page in it. It will mark that page as allocated. If there are no mixed extents with at least one page, SQL will allocate an extent, mark it as mixed and allocate a page from it to the new table.

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

Thinky Night SSCommitted Points: 1663 More actions · Answer 3

Thanks All,

New questions:

If there is a mixed extent, Must it contain a Shared Global Allocation Map (SGAM) Page and Page Free Space (PFS) Page?

And

When a new extent will be required - during Uniform Extent creation- will Uniform extent be created with Global Allocation Map(GAM) page, Shared Global Allocation Map (SGAM) Page and Page Free Space (PFS) Page?

Regards,

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

Thinky Night (4/12/2012)
Thanks All,
New questions:
If there is a mixed extent, Must it contain a Shared Global Allocation Map (SGAM) Page and Page Free Space (PFS) Page?
And
When a new extent will be required - during Uniform Extent creation- will Uniform extent be created with Global Allocation Map(GAM) page, Shared Global Allocation Map (SGAM) Page and Page Free Space (PFS) Page?
Regards,

I had to go look this up in Kalen Delaney's book. The GAM page manages 64,000 extents or about 4gb of data. There is only one GAM for each 4gb. Same thing with the SGAM.

Again, from Kalen's book, a PFS page is managing 8,088 page range within a file.

So no, these things are not allocated with each extent, but rather as needed within a file.

"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

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

Thinky Night (4/12/2012)
If there is a mixed extent, Must it contain a Shared Global Allocation Map (SGAM) Page and Page Free Space (PFS) Page?

No, not if one already exists for the GAM/PFS interval

When a new extent will be required - during Uniform Extent creation- will Uniform extent be created with Global Allocation Map(GAM) page, Shared Global Allocation Map (SGAM) Page and Page Free Space (PFS) Page?

No, not if one already exists for the GAM/PFS interval

If it's the first table created in the DB, those allocation pages will already exist.

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

Thinky Night SSCommitted Points: 1663 More actions · Answer 6

Thanks again, I am still reading Kalen's book, and tring to understand concepts.

New question:

Can i see all pages in a database with a command like DBCC IND?

OR

How can see SGAM Page with DBCC IND?

And in the same book, she mentioned 13-types of pages are in the SQL Server. But SQL Server announces 9 of them. Will there be a restriction to see the other types of pages?

Regards,

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

DBCC IND doesn't show pages. It lists which pages belong to which objects. To view pages, you use DBCC Page. That can view any allocated or unallocated page anywhere in the DB file.

Page types:

Database header

File Header

Data page

index page

Text Mix

Text Data

Row Overflow

IAM

PFS

GAM

SGAM

ML

DIFF

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

Thinky Night (4/12/2012)
Thanks again, I am still reading Kalen's book, and tring to understand concepts.
New question:
Can i see all pages in a database with a command like DBCC IND?
OR
How can see SGAM Page with DBCC IND?
And in the same book, she mentioned 13-types of pages are in the SQL Server. But SQL Server announces 9 of them. Will there be a restriction to see the other types of pages?
Regards,

DBCC IND shows which pages are associated to which object and it takes the syntax

DBCC IND (dbname or ID, 'table name' or ID, index id)

Pull a page from the list and view using

DBCC TRACEON (3604)

GO

DBCC PAGE (dbname or ID, fileid, pageid, viewoption)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉