Microsoft® SQL Server® 2012 Bible

510

Part V: Enterprise Data Management

Confi guring Database Auto Options

Five database-confi guration options determine the automatic behaviors of SQL Server data-

bases (see Table 19-7). In Management Studio, they are all set in the Options tab of the

Database Properties page (refer to Figure 19-2).

TABLE 19-7 Index-Confi guration Properties

Property Level* Graphic Control Code Option

Auto Close D Management Studio ALTER DATABASE <DB Name> SET

auto_close

Auto Shrink D Management Studio ALTER DATABASE <DB Name> SET

auto_shrink

Auto Create

Statistics

D Management Studio ALTER DATABASE <DB NAME> SET

auto_create_statistics

Auto Update

Statistics

D Management Studio ALTER DATABASE <DB NAME> SET

auto_update_statistics

Auto Update

Statistics

Asynchronously

D Management Studio ALTER DATABASE <DB Name> SET

auto_update_statistics_async

* The confi guration level refers to server, database, or connection.

Auto Close

Auto close directs SQL Server to release all database resources (cached data pages, compiled

stored procedures, and saved query execution plans) when all users exit the database and

all processes are complete. This frees memory for other databases. Although this option can

slightly improve performance for other databases, reloading the database can take longer,

as will recompiling the procedures and recalculating the query execution plans, after the

database is again opened by a SQL Server when a user accesses the database again.

If the database is used regularly, do not enable auto close. If the database is used occasion-

ally, then auto close might be appropriate to save memory.

Many front-end client applications repeatedly open and close a connection to SQL Server. Setting auto close ON in

this type of environment is a sure way to kill SQL Server performance.

Use the following to set auto close ON for the AdventureWorks2012 sample database in

code:

ALTER DATABASE AdventureWorks2012 SET AUTO_CLOSE ON;

c19.indd 510c19.indd 510 7/30/2012 5:43:07 PM7/30/2012 5:43:07 PM

http://www.it-ebooks.info