What is GAM page in SQL Server?

GAM pages record what extents have been allocated. Each GAM covers 64,000 extents, or almost 4 gigabytes (GB) of data. The GAM has 1-bit for each extent in the interval it covers. If the bit is 1, the extent is free; if the bit is 0, the extent is allocated, either in GAM or SGAM.

What is a GAM page?

GAM(Global Allocation Map): GAM pages records what extents have been allocated for any use. GAM has bit for every extent. If the bit is 1, the corresponding extent is free, if the bit is 0, the corresponding extent is in use as uniform or mixed extent. A GAM page can hold information of around 64000 extents.

What is GAM in SQL?

SQL Server uses a structure called the Global Allocation Map (GAM) to keep track of all free extents in a database file. The GAM is consists of several GAM pages, one for each GAM Interval in each file.

What is Page in SQL Server?

The page is the fundamental unit of data storage in SQL Server. An extent is a collection of eight physically contiguous pages. Extents help efficiently manage pages. This guide describes the data structures that are used to manage pages and extents in all versions of SQL Server.

INTERESTING:  How do I connect two SQL servers?

What is PFS page in SQL Server?

Page Free Space (PFS) pages

Each page in a database file is associated with a single byte in one of the PFS pages. The byte contains information about how much space is still available on the page as well as a few status bits: Bit 0 is unused. Bit 1 is set if the page is allocated.

What is Page and types of pages in SQL Server?

Every piece of data in SQL Server is stored in 8 KB database pages. A page is a basic unit of I/O operation. A page starts with a 96-byte header in which all system information (the amount of free space, the page type, the page number, and the allocation unit ID of the object that owns the page) is stored.

What are the types of pages in SQL Server?

So here is a good start for folks who would like to know, common page types:

  • 1 – data page.
  • 2 – index page.
  • 3 and 4 – text pages.
  • 8 – GAM page.
  • 9 – SGAM page.
  • 10 – IAM page.
  • 11 – PFS page.

What is page count in SQL?

Page count is the number of pages that the data in the table takes up, each page is 8kb. The only reliable way to decrease that is to delete data. Don’t fuss over indexes with 3 pages, they won’t defrag.