Video Screencast Help

Change "ItemId" and "Properties" fields data types to reduce "SavesetProperty" table size

Created: 16 Nov 2012 • Updated: 27 Feb 2013 | 1 comment
Windows Admins's picture
1 Agree
0 Disagree
+1 1 Vote
Login to vote
Status: Reviewed

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.

Comments 1 CommentJump to latest comment

Andy Nash's picture

Thanks for this observation, while on the face of it this seems like a simple change its unfortunately not as straight forward as a simple field change.

I have passed on this suggestion to our engineering team for future consideration.

Keep this type of observation coming! 


Login to vote