A default installation of SQL Server 2008 will contain a number of pre-defined databases:

master

Contains information about core objects in a SQL Server instance and is required for SQL Server to run

model

Serves as a template for use when creating new databases. Template can be customised with additional objects that will be applied to new databases

msdb

Used by SQL Server Agent and Integration Services. Stores:

• Jobs and Schedules used by SQL Agent
• Backup and Restore history per database
• Alerts
• Proxy Accounts
• DTA reports
• Database Mail logs
• tracking information for Log Shipping
• Maintainence Plans
• Integration Services packages

tempdb

Provides temporary storage for SQL Server operations such as sorting or aggregation. Database is cleared down on each SQL Server restart

distribution

Created on the distributor server when replication is enabled

Dynamic Management Views

Dynamic Managements Views store data about SQL Server objects and states in the sys schema. DMVs provide a rich source of information for a SQL Server DBA.

Space utilisation for a database can be found in the size field of sys.database_files DMV. By joining sys.partitions and sys.allocation_units on hobt_id = container_id, space utilisation can be determined for other objects.

The sys.dm_db_index_operational_stats function provides run-time activity statistics for indexes, and the sys.dm_db_index_physical_stats returns physical characteristics for indexes such as the fragmentation and number of pages in use. The sys.dm_index_usage_stats view will provide information on the usage of the indexes.

All queries on SQL Server pass through the Optimizer before execution. If the optimizer fails to find an index to use to satisfy a query, the query is registered as an 'index miss' in the sys.dm_db_missing_index_* views.

Information about connections and queries currently being executed are found in sys.dm_exec_* views.