uspd_saveset

[Kosachev_Pavel]

Hi all, we are trying to delete large numbers of item from EV 10.0.4 on windows 2008 r2 about 110 millions items. All of it are expired by Retention Policy, but deletion process are very slow (about 10 item per second or 110.000.000/864000(10*60sec*60min*24hours) = 127 days).  

Out investigation are showing that StorageDelete.exe is invoke uspd_saveset procedure. 

Also we know which item are expired (from SQL query).

1. Is there any way to speed up this operation?

2. Which arguments are required to manual delete items with uspd_saveset?

Another problem, slow deletion from indexes:

Eventid 41022 - There are 5650462 items that have been deleted from Vault Store 'Mail' but have not yet been deleted from the indexes.

Eventid 41021 - There are 48.125.320 archived items in Vault Store 'Mail' that are waiting to be indexed.

Enterprise vault is "all in one" (SQL, Archiving, Indexing)  Hardware Server - 2cpu Xeon 5670, 48 Gb RAM cpu utilization about 20%, memory usage 40Gb . There is no problem in Disk subsystems SQL on raid 10 (8 disks) disk queue length about 1. Our DBA  corresponding that main loss on database locks.

[AndrewB · Accepted solution]

what can speed up the EV processes?

sql maintenance

check schedules to make sure expiry/archiving/backup/etc dont overlap

faster storage

more memory

dedicated sql and EV servers (not shared)

more CPU cores

[Kosachev_Pavel]

AndrewB, what sql maintance we need to implement?

Now we reindex DB every night.

Also we check schedules - Archiving and indexing from 17-00 to 10-00 , Storage Expiry from 00:00 to 23:59.

CPU is not loaded (20%)  and 8 gb of Free Memory on server.  

16 Gb of RAM dedicated to SQL Server.

SQL use separate disk group on our Storage, but we cannot place DB on SSD because we haven't it. 

My opinion this is a problem in process StorageDelete.exe: small level of paralelism or something else. It is possible to run more than one instance of StorageDelete?

[Merv]

it's one of the reasons why this blog post was created - https://www-secure.symantec.com/connect/articles/gentle-approach-storage-expiry

This can be inherrant naturre as looks like you have not enabled it storage expiry gradually and trying to delete 110 million items at one go.

1. I would get a Dtrace of storagedelete process done - let it run for a good 30 mins to an hour to analyse the the various function calls.

2. Check total number of items awaiting deletion and awaiting index deletion

Select count(*) from JournalDelete 

Select count(*) from JournalDelete 
Where IndexCommitted = 1 

3. Ensure SQL and EV is optimized and best practices all applied especially  ensure vault store DB maintenance , i.e. index reorg/rebuild and update statistics is done every few days as massive DB changes are on going and so this needs updating frequently.Best to schedule storage expiry to run not 24x7 but give a break  every few days in order to put EV into backup mode and run SQL maintenance.

 - http://www.symantec.com/business/support/index?page=content&id=TECH56172

- http://www.symantec.com/business/support/index?page=content&id=TECH168905

If  you are sure that SQL performance is optimized and you need better performance I would log a support call to check why uspd_saveset is holding things up. Support are better able to interpret the storagedelete dtrace and also possibly get a SQL profiler trace.

4. Do you use DA/CA - any items on legal hold? this will slow down storage expiry

[Kosachev_Pavel]

1. DTrace in Attachment

2.

select count(*) from journalarchive

206455106

select count(*) from journalarchive where indexcommited = 1

158592258

select count(*) from journalarchive where indexcommited = 0

48085474
 

3. Ok, we will check recomendation again...

4. We don't use DA/CA with legal hold.

[Rob_Wilcox1]

I could be wrong, but I don't think stuff will be deleted, if it's not indexed.

That's what I saw the other day when deleting an archive -- it was marked for deletion for *ages* but nothing would delete, until it had finished being indexed.

[Kosachev_Pavel]

I checked all of you recomendations - our server is compliant with it.

Also we are found that sometimes EV invokes  uspu_RollbackCommittedWork SQL procedure (about 2 minutes), which are locked up dbo.JournalArchive, dbo.JournalUpdate, dbo.JournalDelete and deletion operations are stuck.  

I found http://www.symantec.com/business/support//index?page=content&pmv=print&impressions=&viewlocale=&id=T... , but not found working solution.

Yes, i have over 200 millions item in tables.

[Kosachev_Pavel]

As experement we are enable backup mode on all index partitions:

As result

uspu_RollbackCommittedWork not invoked anymore

Speed increased up to 2,5X (25 items per second)

But i ready to consider another recomendations to intensify this process.