SQL Server Architecture - SQL Server 2012 Internals and Troubleshooting - page 22

These bulk operations include the following:
➤

BULK INSERT

➤

Using the bcp executable

➤

SELECT INTO

➤

CREATE INDEX

➤

ALTER INDEX REBUILD

➤

DROP INDEX

BULK-LOGGED AND TRANSACTION LOG BACKUPS

Using bulk-logged mode is intended to make your bulk-logged operation complete
faster. It does not reduce the disk space requirement for your transaction log
backups.

Simple
When the simple recovery model is set on a database, all committed transactions are truncated from
the transaction log every time a checkpoint occurs. This ensures that the size of the log is kept to a
minimum and that transaction log backups are not necessary (or even possible). Whether or not that
is a good or a bad thing depends on what level of recovery you require for the database.

If the potential to lose all changes since the last full or differential backup still meets your business
requirements, then simple recovery might be the way to go.

SQL SERVER'S EXECUTION MODEL AND THE SQLOS

So far, this chapter has abstracted the concept of the SQLOS to make the l ow of components
through the architecture easier to understand without going off on too many tangents. However, the
SQLOS is core to SQL Server's architecture so you need to understand why it exists and what it does
to complete your view of how SQL Server works.

In short, the SQLOS is a thin user-mode layer that sits between SQL Server and Windows. It is used
for low-level operations such as scheduling, I/O completion, memory management, and resource
management. To explore exactly what this means and why it's needed, you i rst need to understand
SQL Server's execution model.

Execution Model
When an application authenticates to SQL Server it establishes a connection in the context of a
session, which is identii ed by a session_id (in older versions of SQL Server this was called a SPID).
You can view a list of all authenticated sessions by querying the sys.dm_exec_sessions DMV.