Disk Storage
Sql server stores database objects in Pages and Extents.
Each page is 8K with a 96 byte header. The data is stored immediately after the header. No data row can cross a single page, so the maximum size for a data row is 8060 bytes.
Eight contiguous pages or 64K is an extent. There are two kinds of Extents: Uniform and Mixed. Uniform extents are owned by a single table or index, while mixed extents are owned by up to 8 tables and/or indexes. New space is always given to a table in Extents. A new page or table is always allocated space in a mixed extent first. Aftewards, if it is greater than 8 pages, it is switched to uniform extents.
There are different kinds of pages:
- Data pages
- Index pages
- Text/image pages
- Global Allocation Map Pages
- Page Free Space pages
- Index map allocation pages
Data pages contain, as mentioned before, a 96 byte header and the data, and a row offset table that acts as an index to the rows stored in that page.
Below is a list of header elements. It is not important to understand them to administer a database.
| Header Information | Description |
|---|---|
| pageid | Database file number plus number of the page |
| level | for index pages the level of the page |
| flagbits | Additional information about the page |
| nextPage | Datebase page number plus next if clusted index |
| prevPage | Page number plus prev if clustered index |
| objID | Id of the database object that the page belongs to |
| lsn | Long sequence number |
| slotCnt | Total number of slots used on this page |
| indexID | The index id of the page |
| Free data | Byte offset of the first available free byte on the page |
| pminlen | Number of bytes in fixed length parts of rows |
| freeCnt | Count of free bytes on the page |
| reservedCnt | Number of bytes reserved by all transactions |
| xactReserved | Number of bytes reserved by the most recently started transaction |
| xactID | Id of the most recently started transaction |
| tornBits | On bit per section detecting torn page write |
Large objects, such as pictures, binary objects, large blocks of text, etc., are stored seperate from the other columns in the data rows. Instead the column contains a pointer to where the data is stored.
SQL Server 2005 has six large object data types. TEXT, NTEXT and IMAGE exited in SQL server 2000. 2005 has them but also adds VARCHAR(MAX), NVARCAR(MAX) and VARBINARY(MAX)
System Databases
Every Instance of SQL Server installs four system Databases:
- master
- This is the most important database in SQL Server. It holds all the system databases that are necessary to work with the server and database objects.
- model
- Is the model used when a user creates a new database. If the user doesn't define options, the new database will be a copy (structurally) of the model database. The system administrator can change the properties of this database to change the model.
- tempdb
- This database stores temporary tables and temporary objects (open transactions, intermediate steps in complex calculations etc.) Every database uses this database for its temp objects. The objects are destroyed as soon as the action is completed.
- msdb
- This database is used by the Server Agent to store jobs, notifications and logging operations.
Default server and named instances
The first instance of SQL server on a machine is the default instance . It can be referred to as (local) or localhost. Additional instances of the server on the same machine are named instances. That is they must be given a unique name. You can have as many named instances as you wish on a machine. (SQL Server Express is always named SqlExpress, and you cannot rename it. So only one instance of SQL server Express is allowed.)
MultiProcessing, Threading
SQL Server makes use of Windows threading. This means a couple of things. It means, for one that several processes can run in parallel. In particular Data Loading, backup and recovery, Query executions and index operations can run in parallel. It also means that SQL server can take full advantage of multi-processor servers.
The SqlCmd Utility
The SQL command utiltity is a command line utility that can be used to query server instances, To use the SqlCmd utiltity Named Pipes must be enabled in the Surface Area Configuration tool.
sqlcmd help
SqlCmd Query
