In Sql Server Data and Information can be stored in Extents
Pages are organized into extents. Each extent contains exactly eight contiguous pages. Extents
are the basic unit of allocation in SQL Server. When the data engine allocates disk space to an
artifact such as a table or index, it will generally allocate an entire extent. This reduces the frequency
of allocation and also ensures a greater degree of contiguity in the physical data structure.
These extents that are allocated entirely to one artifact are referred to as uniform extents.
In some cases, where artifacts are very small and would not require an entire extent, it is
possible for the data engine to allocate pages for multiple artifacts from a single extent. This is
referred to as a mixed extent. Mixed extents are particularly useful when there are a large number
of smaller artifacts that would not themselves fill an entire 64KB block.
Because the SQL Server data engine will need to be able to locate any data value at any time,
it is important for SQL Server to keep an accurate recording of exactly which extents are allocated
to which artifacts. This is also important because once an extent has been allocated to an
artifact, that extent is no longer available to be allocated to another artifact.
SQL Server manages extent allocation information through the use of two allocation maps:
the Global Allocation Map (GAM) and the Shared Global Allocation Map (SGAM). Each map
stores allocation data for up to 64,000 extents by using bit settings to track each extent. This
means that a single map page can support almost 4GB of data. The GAM uses a bit to track
whether each extent is free or allocated. The SGAM specifically monitors mixed extents, using
a bit to identify any mixed extent that has free pages available.
There are also special pages called Page Free Space (PFS) pages, which are used to track the
amount of free space on each individual page and whether or not that page has been allocated.
Once the data engine has allocated an extent, it uses this PFS data to determine which of the
pages in the extents are being used and how much of each page is being used.
The entire allocation tracking system in SQL Server is designed to make the process of allocating
new data structures as fast as possible and minimize exposure to error. Typical production
databases are getting larger and a data explosion has occurred as a result of data warehousing;
therefore, it should be obvious how important managing the data allocation process really is.
Extent Get Data When Overflow of Information happen in pages.
8 continuous pages made a one extent.
Types of Extent.
1) Uniform Extent:- IN This a Single extent only single object info is stored.
2) Mixed Extent :- (Is Also Single Extent) In a Single extent multiple objects info can be stored, but not more than 8 objects.
Definition of Pages.
1) Data Page :- Store Info about other data types except text and image.
2) Text/Image Pages :- In this large memory objects pages are stored.
3) Global Allocation Map: – This gives the information regarding which extent is used or which extent is free. This gives info in bits or 0 and 1.
0 indication of used extent
1 indication of free extent
1 global allocation map is equal to 64000 extent
Approximate 4gb of data can be stored in global allocation map
4) Index Allocation Map :- This gives the info regarding action which extent is used for which purpose. I.e. Which extent is used for which object and which type of data is stored in these extent.
5) Index Pages :- It is used to store index info.
6) Page free space pages :- It is used to store info of free pages i.e. the pages which are not used.
1 Page free space page is equal to 8000 page free info
We can store 8000 free page info in 1 page free space.
- Number of Instance that can be Formed by Sql Server are 32767 Instances.
- One Instance can create 32767 Database of SQL Server.
- Master Database Store Path of Datafile of User Defined Database.
- File Groups used for Permissions to Database.
- We Can Store Datafiles at different Path With the Help of FileGroup and maximum 32767 File group can be made.
- One Data File can Store 16TB Data
- 1 Log File Size or Datasotrage Capacity will be 2TB
- Database Name Can’t be exceeded from 128 characters
Purpose of Default Databases of Master Databases
1) Master Database :- It is Compulsory .If we delete this database then Sql Server Will not Start. It contain all entries like which database is stored at which location, login account info, link server info, instance info etc.
2) Model Database :- It is Common Templates for all databases. Database of size if not mentioned , then all such as properties or setting are stored in model Database and By Default these are setting are used by Database(Like Size of Transaction log File etc).
3) MSDB Database :- It is used for Scheduling.
4) Tempdb Database :- Used for temporary storage.Once the Service of SQL is stopped or Reset then all data in Tempdb Database Destroy or deleted.
5) Resource Database :- All System objects are stored in resource database physically.Logically these are present in all databases and these can be accessed by sys.schema