SQL Server Internals

Question

SQL Server Internals

Sachin Nandanwar

SSCertifiable

Points: 7699
More actions
January 16, 2011 at 10:45 am

#243666

Hi,
I am trying to dig into the internals of SQL Server.
I created a database say "Test".When I run DBCC SHOWFILESTATS it shows the TotalExtents and UsedExtents as 20 each.
Then I created a table "t1" with a single column "id" and inserted a single row in the table.When I run DBCC IND ('Test','t1',1) it shows 2 datapages been allocated also with "sp_spaceused t1" the result returned shows it holds the data of 8KB.
So again when I run DBCC SHOWFILESTATS the totalExtents have now jumped from 20 to 36.Since each extent is a group of 8 datapages so 16 * 8 = 64.So 64 datapages were created just because a small table having a single row was created.
I just want to know what are those 16 extents for ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

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

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

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

Probably because your data file had to grow, and it won't grow by just 2 pages, it grows by whatever the autogrow size is. Other than the 2 pages allocated to the table, the rest will be unallocated, empty, available for future tables and data

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

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 2

GilaMonster (1/16/2011)
Probably because your data file had to grow, and it won't grow by just 2 pages, it grows by whatever the autogrow size is. Other than the 2 pages allocated to the table, the rest will be unallocated, empty, available for future tables and data

Well it seems to be that way.With 1 MB auto grow set it grows by 16 extent.From what I have tried it seems that auto grow does not impact number of extent by which the file grows.By default it grows by 16 extent.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

You may wish to read this:

http://technet.microsoft.com/en-us/library/ms190969.aspx

To gain some insight into how a database size grows.

To gain a feeling for your particular tables growth perhaps this will give you some insight,

http://support.microsoft.com/kb/924947

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]