Change "ItemId" and "Properties" fields data types to reduce "SavesetProperty" table size
1. Field ItemId, defined as NVARCHAR(255), always contain the hexadecimal representation of a SHA256 hash
Considering the type of values kept in ItemId, it could have been defined as a VARCHAR(255), which would require half the space currently used to store ItemId values.
In our database, switching the field to VARCHAR(255) would free 1.5GB. Moreover, ItemId is the first key of index IX_ItemId_Qualifier: this index, currently using 6GB, would be reduced by about 3GB. Total space saved: 4.5GB
2. Field Properties, defined as NVARCHAR(2500), contains XML data.
The Properties field value is always a XML structure, but is stored as NVARCHAR instead of native XML data type. By using the native XML data type, SQL Server can optimize how the XML structure is stored, which is not possible with NVARCHAR
In our environment, converting Properties from NVARCHAR(2500) to XML would save 8GB.
If both ItemId and Properties data types were modified, the SavesetProperty table would be at least 12GB smaller in our environment, which would not only save disk space, but also improve Enterprise Vault database efficiency.
The problem we are facing is that during the weekend when EV FSA Database is performing index maintenance tasks on the SQL Server the database grows big and takes a lot of space to the extent that the disk runs out of space.