“Right now in your data center, a vicious, unforgiving villain is slowly draining the performance from your database servers. It strikes without warning and doesn’t leave a trace in event logs or messages. This killer is fragmentation.” (via SQL Server Fragmentation: The Basics | Brent Ozar PLF.)
A badly-fragmented index can impose a performance penalty of anywhere from 13 to 460%. The impact depends on how many records there are in the table/index, how badly-organized the index has become, and how many times the application itself needs to use that index to carry out a given task. In iManage WorkSite, this means slow-downs in retrieving documents based on their profiles, or interacting with (the largest) folders.
Don’t assume
Don’t assume that WorkSite is installed with every single index that it might probably need. Yes, there are thirty-odd indices installed on mhgroup.docmaster as part of the normal setup, but there is a common setting that requires an additional index.
This setting is the Sort Order Mask. To quote a classic WorkSite blog “One of the most widely adopted uses of the sort order mask feature is to configure the system to return search results sorted by edit date or “most recently edited”. When using this configuration option a recommended approach is to create [an index so that] … data can be retrieved directly from SQL Server sorted by edit date. This also eliminates the need for the WorkSite Server to perform additional sorting of search results.”
Defaults can hurt you
FileSite 8.5 refreshes folders more enthusiastically than 8.2 did – meaning, it refreshes more often. If related indexes are fragmented, this means those performance problems are compounded during refresh.
If you drag-and-drop ten emails to a folder which contains ten thousand emails, by default all the active folders are refreshed ten times – one per email. This quickly compounds into an apparent ‘hang’ in Outlook – five minutes or more of ‘Outlook not responding’.
An unfortunate detail of FileSite’s refresh logic has meant that FileSite 8.5 SP2 would refresh the active folders between eight and eleven times each when a new Word version is saved. This is especially counter-intuitive: your action in Word causes Outlook to freeze, and the duration of the freeze depends on the size of the workspaces you’ve been active in.
You should consider constraining the size of folders and allowed display results. But, if you find these bulging folders too hard to avoid, you can dial-back those auto-refreshes with a few client-side registry settings.
Fragmented is better than missing
The alternative is worse – a missing index on WorkSite’s main document table mhgroup.docmaster. After dragging-and-dropping a couple-dozen emails to an ultra-large folder, you’ll literally experience a half-hour wait for Outlook to un-freeze.
Care and Feeding
An index is not a set-and-forget construction. Indexes should be defragmented on a regular basis! The best approach for defragging depends on the activity and size of the affected tables. Contact me to arrange a quick health-check for your iManage Document Management system, including the underlying indices. Or, if I have already helped you with enough info to fix it alone – ping me with that! I love hearing about good results.
One thought on “Fragmented index can slash your performance within WorkSite”