DA performance issues - recommendation
Hi
Running EV/CA/DA 9.0.2
Have a single EV SQL server, and a single CA/DA SQL server.
SQL servers are 2008 R2 VMs, 4 CPU with 32GB of RAM. Planning on upping CPUs to 8GB.
We've had some performance issues recently in the environmnent around CA and DA, mainly due to a recent 10-fold increase in DA usage, with massive searches, acceptances, legal holds, etc. We went from 7 million messages searched for in Feb to 100 million messages searched for in Mar, and some of these massive searches were accepted, put on legal hold etc.
DA database size went from 200GB in early Mar, to 500GB in late Mar, now around 385GB. CA database is fairly constant around 68GB.
Question 1 - what reduces DA database size? Deleting cases? Taking legal holds off?
Other than not increasing DA usage 10-fold and being mindful of massive legal holds, etc., I am wondering what to do to improve performance, outside of the standard tech docs such as http://www.symantec.com/business/support/index?page=content&id=TECH63230
With a DA database of 500GB, maintenance is very time consuming with the time needed for SQL dumps, index reorgs, etc.
DA10 SQL best practise guide states:
You may need to roll over customer databases to remain within the available storage capacity. The database storage requirements are high. To reduce storage costs, you may want to roll over the database periodically to a new customer database (that is, create all new cases in a new database), and then archive the old database when it is no longer active. Alternatively, you can move the old customer database to slower storage and disable the associated Discovery Accelerator customer tasks. This would allow the customer database to be brought online quickly.
Question 2 - at what point should database be rolled over? My understanding is that there isn't a real way to "close" or "archive" a DA customer database. The best approach is to stop any activity in it (e.g. scheduled searches) and advise users to use a new database. What does disabling the DA customer task do? Does that make the database inaccessible to end users?
The "Co-locating multiple customer databases" section says it may pay to split DBs between different SQL servers, but doesn't give much guidelines around at what point this should be done.
Question 3 - "Additional customer databases may create an impact at the Discovery Accelerator server and the underlying Enterprise Vault infrastructure, which needs to be considered before implementation" - what sort of impact? Is this if multiple customer databases are created on a single SQL server instead of multiple servers?
Any advice appreciated. Thanks.
Here are my replies:
Question 1 - what reduces DA database size? Deleting cases? Taking legal holds off?
Deleting the cases will remove those entries from the database. The log file will grow whilst the items are deleted so you need to account for that.
Question 2 - at what point should database be rolled over?
This is going to depend on your SQL Server capicity, as you have noted, your current server is being impacted so you may have reached this point.
Question 3 - "Additional customer databases may create an impact at the Discovery Accelerator server and the underlying Enterprise Vault infrastructure, which needs to be considered before implementation" - what sort of impact? Is this if multiple customer databases are created on a single SQL server instead of multiple servers?
You would not want to put more active customer databases on the same sql server, that would defeat the purpose. If you are going to put the current customer database into a read only type role then that might be ok.
One question, is it possible to leave this current SQL Server in place for CA and then move DA to a SQL server with more resources? If the current search load is going to remain you might need to consider that.
Greetings, goatboy;
Tony is correct in the information he has provided. For example, the Customer Background Tasks are what controls what a DA Reviewer can do inside the customer and what background operations run. When the Customer Background Tasks are stopped, users can still get into the Customer through the DA Client, but they cannot run new searches or exports. They cannot mark items as reviewed, but they can review items and search criteria. They can also see what searches and exports were run.
When a search runs and is in a pending state, we collect somewhere between 5 KB and 25 KB of data for each item. When a search is rejected, this data is removed from the database. When the search is accepted, this data is moved from a holding table to the tblIntDiscoveredItems table. It is from the tblIntDiscoveredItems table that we obtain information used in the preview and export processing for each item.
For your database's mdf file to shrink from 500 GB to 385 GB, you would have had to have either massive numbers of pending items that were rejected, or you had Research Folders that contained 1000's of items that were deleted, or you had Cases that contained 1000's of items that were deleted, or any combination of these 3 possibilities.
You can check the tblCase table to see if any cases or Research Folders have been deleted (ever) by looking for any entry with a Status ID of 24. To do this, just run the following SQL query against your DA Customer database:
SELECT CaseID, Name, ModifiedDate, EndDate, FolderType FROM tblCase WHERE StatusID = 24;
You can also check to see if any searches were rejected that may add up to the number of hits needed to reach 115 GB of data (at 25KB each). Do do this, just run the following SQL query against your DA Customer database, too:
SELECT SearchID, Name, CaseID, NumHits FROM tblIntSearches WHERE StatusID = 858;
As your mdf file size shrank, your SQL DBA must have performed our recommended maintenance with the 'DB Shrink' option selected and configured to return the space to the operating system. We normally don't recommend returning the space to the operating system unless absolutely necessary for the SQL Server's continued operation as the db would have to request new space into which it can grow when it uses what ever available space was left in the db. This growth request has an impact on the application performance as processing can slow while the storage subsystem allocates the space for the db.
If you are able to stand up a new SQL Server to host just the DA databases (i.e., the DA configuration and customer dbs), you may be able to continue using your existing DA customer. It may also be that you are at the effective limit for that customer and need to create a new DA customer. If you chose to create a new DA customer but want to keep access to the current DA customer, I would recommend that you stop the current customer's Customer Background Tasks through the EVBAAdmin site and make sure anyone with access to the Vault Service Account's credentials knows not to start them again. Note that you need to evaluate what is best for your environment, though. My recommendation to just stop the Customer's Background Tasks may not be the best solution for your environment. Maybe changing permissions within roles is the best solution for your environment. You need to determine what is best.
I hope this helps. Sorry for the length if this posting. Please let us know if you need anything more for this posting.