Database pages simplified..

Database Pages
A Page is the unit by which SQL Server data is stored. SQL Server 2000 views each data file and breaks it down to 8K byte pages. There are 128 pages stored in 1MB. A page can only belong to one object. The beginning of each page has a header that contains information about the characteristics of that page. The page header takes up the first 96 bytes of each page, leaving 8060 bytes for actual data. The header contains information such as PageID, Previous Page, Next Page, amount of free space available, the object ID of the object that owns the page, etc. By checking the header, SQL Server can quickly determine the status of each page, making it easily identifiable.
To keep track of allocations, SQL Server stores allocation information in certain page types. Lets have a look at them:

1) Global Allocation Map (GAM) Pages
A GAM page reports whether an extent is free or allocated. It has a bit (1 or 0) to keep
track of extent allocations in the interval it covers. A bit set to 1 represents a free extent
and a bit set to 0 represents an extent that is in used. Each GAM page contains information up to about 64,000 extents, which is about 4GB of data. Therefore, a data file could have a GAM page for every 4GB of data. GAM pages reside on page 2 of each data file and appear again every 511,230 pages after the first GAM page.

2) Shared Global Allocation Map pages (SGAM)
SGAM pages record which extents are currently used as mixed extents and have at least
one unused page. A bit set to 1 represents a mixed extent with at least one free page and a bit set to 0 represents an extent which is either not a mixed extent or it is mixed but has no free pages. SGAM pages contain information up to about 64,000 extents, which is about 4GB of data. SGAM pages reside on page 3 of each data file and appear again every 511,230 pages after the first SGAM.
The following shows the bit setting for each extent in the GAM and SGAM pages:
Current Status of an Extent GAM bit setting SGAM bit setting
Free Extent  has GAM bit setting of 1 and Mixed extent that has free pages has SGAM setting of 1. Other bits are zero for each.

3) Index Allocation Map (IAM) Page
The IAM pages mark the extents that are allocated to a heap or index, as well as marking
the extents that are allocated to an ntext, text, and image page chain for any table that has
columns of these types. Each heap, index and table with the above data types has a chain of one or more IAM pages to keep track of extents that are allocated to it. The number of IAM pages for an object depends on whether the number of extents exceeds the range that an IAM can record, or if there are multiple data files that host the allocated extents for that object. An IAM page is needed for every data file that hosts extents for that object. IAM
pages are allocated as needed and are located randomly in the data file.

4) Page Free Space (PFS)
PFS pages indicate whether any pages in a Heap or an ntext, text, or image column are
allocated. They also report the amount of free space on each page. Every PFS page covers around 8,000 pages and the next one appears about 8,000 pages after the first page. For every page, PFS has a bitmap that tracks the following space information:
a) Empty page
b) 1-50% Empty
c) 1-20% Empty
d) 1-5% Empty
e) 0-4% Empty
Once an extent is allocated to an object, SQL Server uses the PFS pages to keep track of
free and used pages. PFS pages are the first page in the data file after the file header page.

Comments

Post a Comment

Popular posts from this blog

Automatic Replenishment ~ Warehouse Management (WM) in SAP

Image
This article is intended for SAP functional consultants to configure automatic replenishment of a particular storage type for transfer orders. Whenever the stock in a bin falls below the MIN value (predefined minimum quantity), it will trigger replenishment or transfer request (TR) which will in turn get converted into a transfer order, so that the workers in the warehouse can carry out the actual physical transfer of stock. We have a provision of assigning a definite storage type from where the stock will be picked up for replenishment.   Step 1 : Material Master has to be maintained with Warehouse Views. Under Warehouse Mgmt 2 Storage bin, Maximum bin Quantity, Minimum bin Quantity and Replenishment Quantity has to be maintained. Configuration Changes: Step 2 : Movement type 320 is copied from existing movement type 319 under Logistics Execution ->Warehouse management->Activities->Transfers->Define Movement Types. Movement type 320 is configured as the following
Read more

Teradata - Month End Date

Sql Server: Calculating the last day of the month in SQL Server is pretty simple. Run the following piece of code and its done. SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) CurrentMonthEnd_Day ResultSet: CurrentMonthEnd_Day ———————– 2010-08-31 23:59:59.000 Teradata: However, with reference to Teradata SQL, in our day to day queries in a datawarehouse environment very frequently we need to play with the month end dates that are mostly kept in the date dimension table with one field, i.e. the month end date field. You might require to fetch the month end date which is 4 years back. usually the simplest way in this case will be to calculate 48 months back from the current month end date (in case the monthe end date that long is not stored into the database). This is fair. The piece of code required to do is simple. SELECT (ADD_MONTHS(CAST('2012/08/31' AS DATE),-48)) Result: 2008/08/31 Now trying this simple thing will not work if its a leap year a
Read more

Cross Domain Webix Post

Recently, we were working with Spring Boot and Webix framework using Ajax. We had quite some trouble to POST data back to the Server with Spring Boot.  Error: No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:7100' is therefore not allowed access. We realized the problem was because of our AJAX cross-domain requests that are forbidden by default by the same-origin security policy. Since browsers need to “preflight” any asynchronous or rather HTTP request methods that modify data and also fetch an “approval” the problem had to be addressed both way i.e. Server side and Browser i)                     We had to enable the CORS assertion in Spring Boot i.e. @CrossOrigin(*) ii)                    Enable Cross-origin resource sharing (CORS) from the Chrome browser. You need to add an extension or CORS enabler from the Chrome Web Store for this. Thereafter using the following code the JSON data wa
Read more