© Tillypad, 2008-2015
In order to ensure high performance on the SQL server on which the Tillypad XL database is installed, you need to take the following steps:
In the control panel of the server operating system, select the High performance power plan.
To configure these settings manually, select the High performance plan in the Power Options section of the control panel (Start - Control panel - Control and Safety System - Power Options or Start - Control Panel - Hardware - Power Options).
Set the Max Degree of Parallelism parameter to 1 in the properties of the SQL server instance.
You can assign this value manually on the Advanced page in the Parallelism parameter group of the server properties. Alternatively, you can do so via the stored procedure
tpsys_SetSQLServerSettings.Cancel the automatic database shrink.
You can assign this value manually on the Options page in the Automatic parameter group of the database properties. Alternatively, you can do so using the stored procedure
tpsys_SetSQLServerSettings.If possible, enable the fast database files initialisation for the server.
To configure startup for the SQL server instance, you need to use an account that belongs to the Administrator group.
Configure settings for the tempdb database:
Set the tempdb database recovery model to
SIMPLE.Set up the incremental increase step for the tempdb system database file size:
tempdb file size
FILEGROWTH increment
from 0 to 100 MB
10 MB
from 100 to 200 MB
20 MB
200 MB or higher
10%
Create as many files as you need to maximise the capacity of the drive. The number of these files should correspond to the number of logical central processing units on the server.
Create files of the same size to ensure optimal efficiency and proportional space utilisation. The auto incrementation parameter (
FILEGROWTH) value should be the same for all these files.
You can configure these settings automatically, using the
tpsys_SetSQLServerSettingsprocedure, or manually.Configure tasks to rebuild database indexes and reduce the sizes of the database and logs.
The
tpsys_Reindexprocedure helps you reindex the database, while thetpsys_ShrinkDBprocedure shrinks the log and database files.To regularly reindex and shrink the database and its log, create a task in SQL Agent that will run daily.
We recommend creating one shared task that will shrink the database immediately after it is reindexed.
Exec tpsys_Reindex Exec tpsys_ShrinkDB
To launch this task, select the time period with the lowest server load when no one is working with the database and no other tasks are running on it (in order to avoid conflicts).
The most efficient way of creating this task and scheduling its execution is to use the
Job_optimization.sqlscript.The user can create either one task that executes both procedures sequentially or two independent tasks.
If you choose to use two separate tasks to reindex and shrink the database and its logs, keep in mind that:
tasks should not be launched simultaneously with each other or with other tasks that load the database (e.g. at the same time as the database backup),
one task should be completed before the next task is started,
tasks should be started in a time period when the load on the server is minimal,
tasks should be launched once per day.
After configuring all settings, restart the server completely and check the operating speed of the system.