Database table partitioning
Overview
- The search runs faster.
- Data management policies run faster.
- Maintenance jobs run faster.
Prerequisites
Partitioning is only available in specific versions and editions of SQL Server:
- Standard Edition: version 2016 SP1 or later
- Enterprise Edition: all version
When to enable partitioning
We recommend enabling database partitioning when more than 100 million conversations are expected to be stored in the database. It is best to enable partitioning during installation. Partitioning can also be enabled later, retrospectively on existing data. However, this requires careful considerations as partitioning can take a lot of time depending on the size of the database,
Enabling database table partitioning
The following script will install partitioning, and it should be executed in the Verba database: c:\Program Files\Verba\resources\db\manual-partitioning.sql
Use the SQL Server Management Studio to execute the script.
The first partition border will be the first day of the next month by default, so it will not take a long time to execute even on a large database.
DECLARE @first_partition VARCHAR(100) = CONVERT(VARCHAR, DATEADD(m, 1, DATEADD(m, DATEDIFF(m, 0, GETUTCDATE()), 0)), 112), @second_partition VARCHAR(100) = CONVERT(VARCHAR, DATEADD(m, 2, DATEADD(m, DATEDIFF(m, 0, GETUTCDATE()), 0)), 112) -- or manually: '20190201';
In the following example, the first partition border can be changed manually.
DECLARE @first_partition VARCHAR(100) = '20190101', @second_partition VARCHAR(100) = '20190201';
Enabling database table partitioning for existing data
If existing data should be split to partitions too, then it is recommended to do it out of business hours, considering it can take several hours to finish. It is recommended to split one month as the first step and measure how long does it take to execute.
On average, a million record in a month takes ~30 minutes to partition.
The following script can be used to split the existing data in the past:Â c:\Program Files\Verba\resources\db\manual-extend-partitions-past.sql
Use the SQL Server Management Studio to execute the script.