The SQL Server storage engine manages all data access, both through transactions based commands and bulk operations such as backup, bulk insert and certain DBCC commands. It contains the components needed to actually access and modify data on disk.
In SQL Server 2008, storage engine contains three main components:
In SQL Server 2008, storage engine contains three main components:
- Access methods: They are used to locate data. When a page needs to be retrieved, access methods makes a request to the buffer manager, which brings them in.
- Locking & transaction services: All SQL Server transactions must adhere to the ACID properties. To achieve this, the locking and transaction services come into play.
- Utility Commands: This contains components for controlling utilities like bulk-load, DBCC commands, backup-restore operations etc.
The SQL Server storage engine(2008) needs 3 system catalogs to function:
- sys.sysallcounts
- sys.sysrowsets
- sys,sysrcols
These catalogs contain all the metadata that the storage engine needs to find its way around the table and index structure.
To put it in simple terms, think of SQL Server storage as a courier company.It gets the data from relational engine, packages it properly and sends it over to the client.It also receives requests from the client, converts then into OLE DB rowsets and feeds them into the relational engine.In doing all of these operations, it interacts with many other components of the SQL Server engine, like the buffer manager, transaction services etc.
