SQL Server Best Practice - Database Option

AUTO_SHRINK

Frequently shrinking and expanding a database can lead to physical fragmentation. We recommend you to set the AUTO_SHRINK database option to OFF. If you know that the space that you are reclaiming will not be needed in the future, you can reclaim the space by manually shrinking the database.

AUTO_CLOSE

The AUTO_CLOSE database setting can cause performance degradation. This is caused by the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection. We recommend you to set the AUTO_CLOSE option to OFF for the database if a database accessed frequently.

PAGE_VERIFY

When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity. Wer recommend you to set the PAGE_VERIFY database option to CHECKSUM.

Max Degree of Parallelism

This rule determines whether the max degree of parallelism (MAXDOP) option for a value greater than 8. Setting this option to a larger value often results in unwanted resource consumption and performance degradation. We recommend you to Set the max degree of parallelism option to 8 or less by using sp_configure. Here it is:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 8;
GORECONFIGURE WITH OVERRIDE;GO

Auto Update Statistic

SQL Server uses the statistic to optimize the query, which is the statistic is up-to-date. Thereby it can make the smart query optimization decisions to generate query. Using the Auto Update Statistic ON can make your statistic always up-to-date, so SQL Server can uses the statistic for optimize the query. And also Turn Auto Update Statistic ON has a drawback, where it is running when your server has a heavy load data. Thus, your server performance will fall-down. Sometimes you should turn OFF the option to better performance. We recommend you to investigate first before decide the options. But as a rule of tumb, if your server is not maxed out, then leaving this option ON is probably the best decision.

Default Trace Log Files

This rule checks the value of the sp_configure option, default trace enabled, to determine whether default trace is turned ON (1) or OFF (0). When enabled, default tracing provides information about configuration and DDL changes to the Database Engine. Use the sp_configure stored procedure to enable tracing by setting the value of default trace enabled to 1. Here it is:

sp_configure 'default trace enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

AWE Properties

When you are running SQL Server under stress conditions, an instance of SQL Server might exhibit unpredictable behavior. This problem occurs when you use the Intel Physical Addressing Extensions (PAE) specification. The PAE specification is a 36-bit physical addressing mode. This problem occurs even if you do not turn on the Address Windowing Extension (AWE) option to address large memory. Enabling AWE properties will helps SQL Server to allocate memory themself. We recommend you to set memory 4 GB (6 GB for total) at minimum server memory and maximum server memory.