Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy


  SQL Server Tips by Burleson

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  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks

Hit Counter