tpsys_SetSQLServerSettings stored procedure
Prev Chapter 32. How to improve system performance  Next
tpsys_SetSQLServerSettings stored procedure

© Tillypad, 2008-2015

tpsys_SetSQLServerSettings stored procedure

The tpsys_SetSQLServerSettings stored procedure is used to assign values that ensure high performance to the SQL server and database parameters.

To create the tpsys_SetSQLServerSettings stored procedure in the database of the segment, execute the tpsys_SetSQLServerSettings.sql script.

Attention!

This was tested on Microsoft SQL Server 2008 and Microsoft SQL Server 2012. Errors may appear in earlier versions of Microsoft SQL Server.

After the script is successfully completed, run the tpsys_SetSQLServerSettings procedure.

exec tpsys_SetSQLServerSettings

The tpsys_SetSQLServerSettings procedure must be executed once.

As a result, the tpsys_SetSQLServerSettings procedure will configure the optimal settings:

  • The max. degree of parallelism for the SQL server is set to 1.

  • Auto shrink is disabled for the system database.

  • Settings configured for the database of the segment:

    • the Recovery model parameter is set to Simple ,

    • the Auto shrink parameter is set to False,

    • a minimum size of 1024 Мb is set for .mdf datafiles (only if the existing file is smaller than that), and auto shrink is set to 100 Мb,

    • a minimum size of 100 Мb is set for .ldf datafiles (only if the existing file is smaller than that), and auto shrink is set to 10%.

If the following error message appears during execution of the procedure: tempdb files cannot be configured because the current size of tempdb datafiles must be reduced, restart the SQL server and run the procedure again.

After the tpsys_SetSQLServerSettings procedure is successfully completed, the following messages will appear:

  • The Show advanced options configuration parameter has been changed from 0 to 1. Follow the RECONFIGURE installation manual.

  • The Max degree of parallelism configuration parameter has been changed from 0 to 1. Follow the RECONFIGURE installation manual.

You do not need to run RECONFIGURE because it is started automatically after all settings have been saved.