Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  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  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter