| |
 |
|
SQL Server Tips by Robin Schumacher
|
A Quick SQL Server Storage
Primer
A database is the logical SQL Server container that holds user data
and other necessary objects such as stored procedures. Beginning in
SQL Server 7, Microsoft did away with the old Sybase device method
of storage definitions and went to a cleaner implementation of one
or more database files, which reside on the server. Each database
begins life with a primary database file that is used to house
system tables and other metadata, and a log file that is used for
database recovery purposes. Other files may be created as well. The
SQL Server database and log files have a fair amount of flexibility
in terms of being increased or decreased in size.
The DBA can also make use of filegroups, which are collections of
database files. Administrators can use filegroups to explicitly
place objects for load balancing and performance purposes.
Unfortunately, many DBAs do not smartly utilize filegroups, which is
a shame because Microsoft has given the DBA even more flexibility in
filegroup usage in SQL Server 2005 where table and index
partitioning across filegroups, sometimes called data_spaces in
2005, is supported. Transaction log files are never members of
filegroups, and a database file can only be a member of one
filegroup.
Databases are comprised of logical pages, each of which is a fixed
eight KB in size. The eight KB page is also a unit of I/O for the
SQL Server, which affects performance and locking. The two
fundamental units of database storage, the table and index, make use
of pages to hold their information. There are also specialty
database pages such as Global Allocation Map, Page Free Space, and
Index Allocation Map, that are used for system management purposes.
As filegroups are collections of database files, extents are
collections of database pages. An extent is made up of eight, eight
KB pages and is therefore 64 KB in size. Extents come in two types,
uniform and mixed. Uniform extents ascribe all eight database pages
to a single object; whereas, mixed extents have the potential to
hold multiple objects. With mixed extents, the SQL Server tries to
preserve space by not allowing very small objects to take up an
entire extent and therefore, waste a lot of space.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
