511
Chapter 19: Confi guring SQL Server
19
Best Practice
Do not set auto_close ON for production SQL Server databases. There may be situations in which
you may have hundreds or even thousands of databases with archived data that are never all used at
the same time. For these situations you may benefi t from having auto_close on for some or even all
databases. By default, auto_closeis on only for SQL Server Express Edition and is off by default for
all other editions.Auto Shrink
If the database has more than 25 percent free space, then this option causes SQL Server to
perform a data and log fi le shrink operation. This option also causes the transaction log to
shrink after it’s backed up.Performing a fi le shrink is a costly operation because several pages must be moved within
the fi le. Plus, it’s probable that the fi les have to grow again later (another costly operation),
causing fi le fragmentation at the OS level. This option also regularly checks the status of
the data pages to determine whether they can be shrunk.Shrinking the data and transaction log fi les is discussed in detail in Chapter 21, “Backup and Recovery
Planning.”To set the auto shrink option off for the AdventureWorks2012 sample database in code, do
the following:ALTER DATABASE AdventureWorks2012 SET AUTO_SHRINK OFF;Best Practice
Use of auto_shrink can cause severe performance issues as it can cause index fragmentation as
well disk fragmentation at the operating system level. It’s generally best practice that you do not
have this enabled on your databases. For more information on this topic, see Paul Randal’s blog: www
.sqlskills.com/blogs/paul/post/Auto-shrink-e28093-turn-it-OFF!.aspx.Auto Create Statistics
Data-distribution statistics are a key factor in how the SQL Server Query Optimizer creates
query execution plans. This option directs SQL Server to automatically create statistics for
any columns for which statistics could be useful. The default for this option is set to ON.To set auto create statistics on for the AdventureWorks2012 sample database in code, do the
following:ALTER DATABASE AdventureWorks2012 SET AUTO_CREATE_STATISTICS ON;c19.indd 511c19.indd 511 7/30/2012 5:43:08 PM7/30/2012 5:43:08 PM
http://www.it-ebooks.info