DATABASE OPTION
A database has numerous options that control a variety of behaviors. These
options are broken down into several categories,
including the following:
- Recovery
- Auto options
- Change tracking
- Access
- Parameterization
The recovery options determine the behavior of the transaction log and how
damaged pages are handled.
Recovery Models
Every database within a SQL Server instance has a property setting called
the recovery model.
The recovery model determines the types of backups you can perform against
a database.
The recovery models available in SQL Server 2008 are:
THE FULL RECOVERY MODEL
When a database is in the Full recovery model, all changes made, using both
data manipulationlanguage (DML) and data defi nition language (DDL), are logged to the
transaction log. Because all changes are recorded in the transaction log, it is possible to recover
a database in the Full recovery model to a given point in time so that data loss can be minimized.
THE BULK-LOGGED RECOVERY MODEL
Certain operations are designed to manipulate large amounts of data.
The Bulk-logged recovery model allows certain operations to be executed
with minimal logging. The operations that are performed in a minimally logged manner with the database set in the Bulk-logged recovery model are:
BCP
BULK INSERT
SELECT. . .INTO
CREATE INDEX
ALTER INDEX. . .REBUILD
THE SIMPLE RECOVERY MODEL
The third recovery model is Simple. A database in the Simple recovery model
logs operations to the transaction log exactly as the Full recovery model does. However, each
time the database checkpoint process executes, the committed portion of the transaction log
is discarded. A database in the Simple recovery
model cannot be recovered to a point in time because it is not possible to issue a transaction log backup for a database in the simple
recovery model.
Because the recovery model is a property of a database, you set the
recovery model by using the ALTER DATABASE command as follows:
ALTER DATABASE database_name
SET RECOVERY { FULL | BULK_LOGGED | SIMPLE }
Damaged Pages
It is possible to damage data pages during a write to disk if you have a
power failure or failures in disk subsystem
components during the write operation. If the write operation fails to complete, you can have an incomplete page in the database that cannot be
read. Because the damage happens to a page on disk, the only time that you see a result
of the damage is when SQL Server attempts to read the page off disk.
The default confi guration of SQL Server does not check for damaged pages
and could cause the database to go off-line if a damaged page is encountered.
The
PAGE_VERIFY CHECKSUM option can be enabled, which allows you to discover and log
damaged pages.
When pages are written to disk, a checksum for the page is calculated and
stored in the page header. When SQL Server reads a page from disk, a checksum is calculated
and compared to the checksum stored in the page header. If a damaged page is
encountered, an 824 error is returned to the calling application and logged to the SQL Server error
log and Windows Application Event log, and the ID of
the damaged page is logged to the suspect_pages table in the msdb database.
Auto Options
There are five options for a database that enable certain actions to occur
automatically or Control certain automatic behaviors.
AUTO_CLOSE
When set to ON, the database is shut down cleanly and its resources are
freed after the last user exits. The database automatically reopens when a user
tries to use the database again.When set to OFF, the database remains open after the last user exits.True for all databases when using SQL Server 2000 Desktop Engine or SQL
Server Express, and False for all other editions, regardless of operating
system.
AUTO_CREATE_STATISTICS
When set to ON, statistics are automatically created on columns used in a
predicate.
When set to OFF, statistics are not automatically created; instead,
statistics can be manually created.
AUTO_UPDATE_STATISTICS
When set to ON, any missing statistics required by a query for optimization
are automatically built during query optimization.
When set to OFF, statistics must be manually created.
AUTO_SHRINK
When set to ON, the database files are candidates for periodic shrinking.
Both data file and log files can be shrunk automatically by SQL Server.
AUTO_SHRINK reduces the size of the transaction log only if the database is set
to SIMPLE recovery model or if the log is backed up.
When set to OFF, database files are not automatically shrunk during
periodic checks for unused space.
Change Tracking
One of the challenges for any
multiuser system is to ensure that the changes of one user do not accidentally overwrite the changes of another. To prevent the changes
of multiple users from overriding each other, applications are usually built within
mechanisms to determine whether a row has changed between the time it was read and the time it is
written back to the database. The tracking mechanisms usually involve columns with either a
datetime or
timestamp column and also might include an entire versioning system.
SQL Server 2008 introduces a new
feature implemented through the CHANGE_TRACKING database option.
Access
Access to a database can be
controlled through several options.
The status of a database can be
explicitly set to ONLINE, OFFLINE, or EMERGENCY.
When a database is in an ONLINE state, you can perform all operations that would
otherwise be possible.
A database that is in an OFFLINE state is inaccessible.
A
database in an EMERGENCY state can be accessed only by a member of the db_owner role, and the only
command allowed to be executed is SELECT.
You can control the ability to
modify data for an online database by setting the database to either READ_ONLY or READ_WRITE.
User access to a database can be controlled through the SINGLE_USER,
RESTRICTED_USER and MULTI_USER options. When a database is in SINGLE_USER mode, only a
single user is allowed to access the database. A database set to RESTRICTED_USER only
allows access to
members of the db_owner, dbcreator, and sysadmin roles.
If multiple users are using the
database when you change the mode to SINGLE_USER or users that conflict with the allowed set for RESTRICTED_USER, the ALTER
DATABASE command is blocked until all the non-allowed users disconnect.
Parameterization
One of the “hot button” topics in application development is whether to parameterize calls
to the database. When a database call is parameterized, the values are passed as variables.
SQL Server caches the query plan for every query that is executed. Unless there is pressure on the query cache that forces a query plan from the cache, every query executed since the instance started is in the query cache. When a query is executed, SQL Server parses and compiles the query. The query is then compared to the query cache using a string-matching algorithm. If a match is found, SQL Server retrieves the plan that has already been generated
and executes the query.
A query that is parameterized has a much higher probability of being matched because the query string does not change even when the values being used vary. Therefore parameterized queries can reuse cached query plans more frequently and avoid the time
required to build a query plan.
Forced Parameterization
Not all applications parameterize calls to the database, you can force SQL Serverto parameterize every query for a given database by setting the PARAMETERIZATION
FORCED database option.
The default setting for a database is not to force parameterization. The reuse of query plans provides a benefi t so long as the query plan being reused is the most effi cient path through the data. For tables where there is signifi cant data skew, one value produces an efficient
query plan, whereas another value causes a different query plan to be created. In addition,applications see the effect of parameterization only if the majority of database calls have an extremely short duration.
REFRENCES
Microsoft SQL Server 2008 Implementation and Maintenance
