SQL Backend Server - Min & Max Memory Lock Configuration

[AKL]

Hello

We are running Enterprise vault server 9.0.1 with SQL 2008 R2 as backend. Below are hardware configuration for servers:

1. Windows 2008 R2 Standard

2. CPU - 2.66GHz - 4 Processors

3. Physical memory - 4GB for Enterprise vault servers and 8GB for SQL server.

4. 5 Enterprise vault servers with 5 active vault stores - 1 SQL server.

Lately we are seeing that Enterprise vault is losing connections with SQL intermittently and when we check the SQL server - it seems that it is utilizing peak memory and even logs suggest that Memory is maxed out as well on SQL.

Severity:  Warning

Status:  New

Source:  Enterprise Vault

Name:  Connection to SQL Server lost

Description:  The connection 'Provider=SQLOLEDB;Server=evsql01;Database=EnterpriseVaultDirectory;Trusted_Connection=yes' was lost and the system is waiting to reconnect (Thread Id: 6980)

However, it doesnt makes sense because if Enterprise vault was causing max memory usage - it should have been more frequent than once or couple times a week.

To my knowledge, SQL is an intelligent application which reserves memory in advance and doesn’t releases it to avoid reserving it again.

So when i digged deeper, I found out that a SQL bug mentioned with KB : http://support.microsoft.com/kb/931175 still exist with SQL 2008. I have asked respective person to correct this.

Beside same, I am thinking to configure Min and Max Memory lock configuraiton on SQL - so it leaves 2GB for OS and uses 6GB for itself at max. But I am trying to figure its impact on Enterprise vault before configuring.

Any thoughts/recommendations for Min & Max lock configuration for SQL with respect to enterprise vault 9.0.1/9.0.2 or EV10 (if we plan to upgrade in near future)

Thanks in advance

[GertjanA]

Hi, you might want to go to 9SP2, and apply memory hotfixes mentioned in below document. Or wait a little (this quarter) for SP3.

http://www.symantec.com/docs/TECH147778

I believe that 8GB should be sufficient for SQL, but if you have large archiving going on, you might be better of adding some more. Is there any 'logic' in the occurring of the error? Same day? Same time? Same other issue? Think of virusscan, scan for software, sscm, sql maintenance plans, backup's, any other automated process causing this? Is the time where the issue happens perhaps during an archiving run? Discovery Accelerator search?

Try to find some logic in the occurences, and you will probably find the cause too.

[AKL]

Thanks Gertjan

The issue mostly is seen during weekend when archiving tasks run full time AND maintainence is performed over vault store databases to keep fragmentation levels considerable.

Beside same backup on SQL runs daily night but we dont see it during weekdays most of the time. its only during weekend it occurs.

But again, it was working fine so far - it started occuring only since last 2 weeks hence am bit hesitant to say that SQL servers is not able to keep up - I felt the same that 8GB is pretty decent for enterprise vault considering load of around 8K to 10K users (and journaling)

Discovery Searches are done in our environment but DA has its own SQL server and database - though am sure it access the vault store database for search queries, I will confirm more on that.

Regarding Upgrade to 9.0.2, the technote shared mentions that Memory issues are only with 9.0.2. It doesnt specifies that same issue is with 9.0.1 so are we confident that same issue is with 9.0.1 as well?

Just confirming because I have to push my team hard to get to upgraded version with proper reasoning, though with increasing issues it looks fair to upgrade to 9.0.2

[AKL]

Also, if you know or have any recommendations for Min & Max Memory configuration on SQL servers - like if it is recommended with enterprise vault and if yes - what should be values and other related parameters...?

[MichelZ · Accepted solution]

Hi

I don't think that Symantec has a best practice for that.
You should be looking around on the Microsoft sites for this, as this is your best bet.

I think the SQL Best Practice analyzer recommends to set at least a maximum.

Cheers
Michel

[GertjanA]

Hello Ajay,

AFAIK (and I am NOT a DBA), it is NOT a good idea to do archiving AND run maintenance at the same time.

What I would do is to reconsider your archiving window.

Make sure you have a window that allows for archiving all mailboxes. Set the backupmode, then perform maintenance.

[AKL]

Thanks guys. Based on your ideas - I have drafted a plan. But will need a week's time to implement it and then another week to monitor to make sure we don't see meomory issues on SQL.

We will be configuring Max lock to 6.7GB and will be putting all vault stores in backup mode while full backup on EV servers AND Maintainence on SQL server runs.

I am marking solutions for now - but will re-open or keep this thread posted on any further progress from my side.

[AndrewB]

Hi Ajay, I'm curious to know how you came about the 6.7 max figure. Thanks!

[AKL]

Physical RAM                        MaxServerMem Setting 
2GB                                           1500 
4GB                                           3200 
6GB                                           4800 
8GB                                           6400 
12GB                                         10000 
16GB                                         13500 
24GB                                         21500 
32GB                                         29000 
48GB                                         44000 
64GB                                         60000
72GB                                         68000
96GB                                         92000
128GB                                       124000 

So Increased 300MB considering Enterprise vault performance as a rough estimate. Below is a reference blog with same table and other description of this configuration:

http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-fo...

[AndrewB]

Thanks for sharing.