Data Storage Internals - Pro SQL Server Internals - page 17

Table 1-1. Select *: Number of reads and execution time of the queries

select EmployeeId, Name from dbo.Employee select * from dbo.Employee

Number of reads 7 90,895

Execution time 2 ms 3,343 ms

As you see, the first select, which reads the LOB data and transmits it to the client, is a few orders of magnitude
slower than the second select. One case where this becomes extremely important is with client applications, which
use Object Relational Mapping (ORM) frameworks. Developers tend to reuse the same entity objects in different parts
of an application. As a result, an application may load all attributes/columns even though it does not need all of them
in many cases.
It is better to define different entities with a minimum set of required attributes on an individual use-
case basis. In our example, it would work best to create separate entities/classes, such as EmployeeList
and EmployeeProperties . An EmployeeList entity would have two attributes: EmployeeId and Name.
EmployeeProperties would include a Picture attribute in addition to the two mentioned. This approach can
significantly improve the performance of systems.

Extents and Allocation Map Pages
SQL Server logically groups eight pages into 64KB units called extents . There are two types of extents available: Mixed
extents store data that belongs to different objects. Uniform extents store the data for the same object.
When a new object is created, SQL Server stores first eight object pages in mixed extents. After that, all
subsequent space allocation for that object is done with uniform extents.
SQL Server uses special kind of pages, called Allocation Maps, to track extent and page usage in a file. There are
several different types of allocation maps pages in SQL Server.
Global Allocation Map (GAM) pages track if extents have been allocated by any objects. The data is represented
as bitmaps where each bit indicates the allocation status of an extent. Zero bits indicate that the corresponding extents
are in use. The bits with a value of one indicate that the corresponding extents are free. Every GAM page covers about
64,000 extents, or almost 4GB of data. This means that every database file has one GAM page for about 4GB of file size.
Shared Global Allocation Map (SGAM) pages track information about mixed extents. Similar to GAM pages, it is a
bitmap with one bit per extent. The bit has a value of one if the corresponding extent is a mixed extent and has at least
one free page available. Otherwise, the bit is set to zero. Like a GAM page, SGAM page tracks about 64,000 extents, or
almost 4GB of data.
SQL Server can determine the allocation status of the extent by looking at the corresponding bits in GAM and
SGAM pages. Table 1-2 shows the possible combinations of the bits.

Table 1-2. Allocation status of the extents

Status SGAM bit GAM bit

Free, not in use 0 1

Mixed extent with at least one free page available 1 0

Uniform extent or full mixed extent 0 0