SQL Server recommended parameter configuration and log shrinkage issues

Recently, there are periodic project feedback system Overall performance degradation, the analysis is due to the database environment, poor configuration of parameters. For example, SQL Server log files by default by percentage growth, when log files are relatively large, each time the extension time is longer, the system overall lag; In addition, if there is no dedicated log backup, shrinking the log and database will not significantly reduce the log size, resulting in a large full backup, long backup time, and so on. Recommended configuration

Simply tidy up some of the more basic, common configurations as follows:

1. Recommended SQL Server version (x64): SQL Server 2008 and later (most notably the parameter sniffing feature)

2. Minimum memory and maximum memory are set to 80% of physical memory

3. The initial size of the data and log files is set to 10G and 2G respectively, which are set to grow at a fixed 200M size without limiting the maximum value;

4. The recovery model of the tempdb database is set to simple, and the initial size of the data and log files is set to 2G and 1G respectively, set to grow at a fixed 200M size, without limiting the maximum value;

5. The number of data files in tempdb = The number of CPUs of the database server, the initial size and increment of all data files must be consistent, the number of data files should not exceed 4;

6. The maximum degree of parallelism is set to 1, or the parallel cost threshold is set to 100 (set as appropriate)

7. After a full backup of the database, a log backup should be made, and then the log will be shrunk.

Log shrinkage

Normally, after you complete a full backup, you should perform a log backup before you shrink the log file. Only log backup After the record will be truncated, only a full or differential backup, do the log shrinkage is not effective.

The procedure is as follows:

 Use [Master]GOBACKUP DATABASE [DbName]  to DISK='XXX'GOBACKUP LOG [DbName]  to DISK='XXX'GO Use [DbName]GO--determine the logical name of the database log file, shrink the log fileDECLARE @logName NVARCHAR( -);SELECT @logName =Name fromSys.database_filesWHEREType_desc= 'LOG';DBCCShrinkfile (@logName,1024x768);GO

If you do not back up the logs and truncate the logs directly (deprecated), there are two workarounds:

1. Write the log to the NUL virtual file (for SQL Server, NUL, like any other real file, SQL Server scans all active logs, formats the log and writes the nul file)

2. Change database to Simple recovery mode and change to full recovery mode

The SQL2005 with TRUNCATE_ONLY option has the same effect. Run under the simple recovery model, all activity logs are discarded after checkpoint;

 --  Back up the database log to nul virtual file  backup  log  [ dbname  ]  to  disk  =   " nul   " --  Back up the database log, truncate the log (sqlserver2005 support)  backup  log   [ dbname  ]  with   truncate_only  //2008 after  

--Change the database recovery model to simple (that is, truncate the log) before reverting to full mode Use [Master]GOALTER DATABASE [DbName] SETRECOVERY Simple withno_waitGOALTER DATABASE [DbName] SETRECOVERY Simple--Simple ModeGO Use [DbName]GO--determine the logical name of the database log fileDBCCShrinkfile (N'Dbname_log',1024x768)GO Use [Master]GOALTER DATABASE [DbName] SETRECOVERY Full  withno_waitGOALTER DATABASE [DbName] SETRECOVERY Full --revert to Full modeGO

SQL Server recommended parameter configuration and log shrinkage issues