2

I have a question on SQL Server page, I have created a new database with only one new table as below
Here is the Code:

CREATE TABLE PAGETEST
(
    ID int primary key
)

When i see my staring page number= FileID:PageNumber it is starting 1:285.
Q 1). Why my page not starting from 1:0 or 1:001?
Q 2).Will this page number have dependency from model database? Kindly find the below images

enter image description here

  • 1
    Any db have ,from start, some objects from provider (from ms); maybe this give hint to you select TOP (300) allocated_page_page_id ,OBJECT_NAME(O.object_id) from sys.all_objects AS O CROSS APPLY sys.dm_db_database_page_allocations(DB_ID('Market_Analytics'), O.object_id,NULL,NULL,'LIMITED'/*'DETAILED'*/) where is_ms_shipped = 1 ORDER BY allocated_page_page_id ASC – Sabin Bio Apr 21 '17 at 7:59
  • Thanks for your answer now i am able to see some information in ms shipped tables which occupied the data pages. I have also tried creating a new datafile and checked it, Now PageNumber = 5:8, Your answer gave me to research now, let me find more on this. – info.sqldba Apr 21 '17 at 8:08
  • @info.sqldba, be aware that data files include specialized pages that don't belong to any user or system objects. These are used to track database state and space allocation. – Dan Guzman Apr 21 '17 at 10:56
2

Let me preface this answer with the statement that there could be entire books written on this subject and this is without a doubt some of the most interesting and complicated parts of SQL Server.

This answer will not attempt to go into great lengths of detail, but a simplified overview of certain items.

Q 1). Why my page not starting from 1:0 or 1:001?

In SQL Server there is the logical structure of your database and the physical. Since you're asking about the physical structure, there are two things at play here:

  1. Internal data structures and tracking
  2. Internal metadata storage

Your table doesn't start at page 0 of file 1, however that space in indeed occupied and used. This page is reserved and called the file header page, each file will have one of these.

Additionally there are GAM intervals which happen roughly every 4 GB, PFS that happen every roughly 64 MB, etc.

Finally there are the metadata tracking tables, such as sys.sysschobjs, which aren't readily available for consumption yet do need space in the database.

Q 2).Will this page number have dependency from model database?

Simplistically speaking, yes. The more objects created and inherited from model, the more internal tracking and space that will be needed. This is grossly over simplified but should do, given the previous explanation as data can be extrapolated from it.

That's just the beginning ...

Allocation algorithms in SQL Server may be different between base objects. For example, when to allocate a new page may differ between heaps and clustered indexes. Where the data is allocated depends on how long the database has been in use, where it has been internally last filled to, and a host of many other different variables.

It truly is very interesting stuff and it's great to have fun with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.