SQL 101 : Storage

SQL Server Storage : Pages

A SQL database is made up of logical pages. A page is 8Kb (8192 bytes) in size and is used by both tables and indexes to store information. 8Kb is therefore the size of an input/output unit in SQL server.

*** Some special pages also exist for systems management purposes for example the ‘Global Allocation Map’, ‘Index Allocation Map’ and ‘Page Free Space’.

In versions up to SQL 2000, 8kb was the maximum row size in a table.

Prior to SQL 2005, defining a table with a larger row size is possible, but populating the columns fully was not.

For example, in SQL 7 - 2000 , the sql >

create table rubbishtable (

rubbishcolumn1 varchar (8000),
rubbishcolumn2 varchar (8000)

)

Returns the warning >

Warning: The table 'rubbishtable' has been created but its maximum row size (16023) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

SQL 2005

In SQL 2005, the page size is still 8Kb but you can exceed 8k per row.
This is achieved by moving the largest column to a second page into a ‘ROW_OVERFLOW_DATA unit’. The original page is then updated by adding a 24 byte pointer to this second page. This is known as the ‘IN_ROW_DATA allocation unit’.

You don’t see this functionality happening. ‘Oh great’ , ‘I can now fit more data into a row’ is likely to be a developer’s response to this new feature.

From a DBA’s point of view however, utilising this new functionality has performance implications.
Performing queries on tables where the data is split across pages will be marginally slower due to 2 pages being returned for each offending record.

Also, when updates are done which force the creation or deletion of overflow pages this will cause fragmentation in the database.



SQL Server Storage : Extents

An Extent is a group of 8 pages and hence is (8x8kb) 64Kb in size. This is the smallest unit of data SQL Server can allocate.

There are two types of extent.

Uniform Extent All 8 pages are assigned to a single object.

Mixed Extent More than 1 (small) object is placed inside the extent, ensuring space is not wasted.




SQL Server Storage : Files

Each datafile in a SQL database has 2 names, a logical file name (used to refer to the data store from transact SQL) and a physical one. The Physical one is the one stored on the operating system.


SQL databases have 3 filetypes.

Every database has a primary data store. The recommended (but not enforced) file extension is .MDF.

Some databases have (1 or more) secondary data files. The recommended (but not enforced) file extension is .NDF.

Log Files should have a .LDF extension. The log file (also known as the transaction log) holds a log of changes which may be helpful in recovering the datanase. When the recovery model is set to ‘simple’ they contain minimal information however.


SQL Server Storage : File Groups

File groups come in 2 flavours, ‘Primary’ or ‘User Defined’. Either of these can be the default file group i.e where all user objects are created (unless filegroup is specified).

File groups allow you to place heavily accessed tables in a different filegroup. Reasons for this could be to improve database performance (e.g housing the filegroup on a different disk array) or to backup that data separately.