January 16, 2011 at 10:45 am
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
January 16, 2011 at 10:54 am
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
January 16, 2011 at 11:46 am
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
January 16, 2011 at 1:22 pm
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,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy