Blog Post

Enterprise Data Services Community Blog
2 MIN READ

SQL Maintenance for Enterprise Vault

dcVAST's avatar
dcVAST
Level 5
10 years ago

The Knowledge Base doesn't have any current documentation for Dynamic Management Views.

If you have really high fragmentation you may need to manually perform maintenance first via http://www.symantec.com/docs/TECH168905

To check fragmentation run DBCC SHOWCONTIG with ALL_INDEXES on each EV database.

You will want to follow this technote: SQL Maintenance/Backups: This should be done daily against the Vault Store Database(s) and weekly against the Directory Database: http://www.symantec.com/docs/TECH74666

IMPORTANT NOTE:  If the values for Extent Switches, Logical Scan Fragmentation, and Extent Scan Fragmentation are not within recommended values after performing scheduled maintenance, you may need to modify the 'Rebuild Index' maintenance task settings or perform the task more frequently.

You probably want have maintenance run per the TECH74666 when services are stopped for EV and with high fragmentation situations have the databases offline; or you can have maintenance run during the window in Backup Mode once fragmentation is reduced.

The first part of the TECH74666 is for backups; the second half is for maintenance:

For maintenance, from the list of tasks, choose

Shrink Database (this process is optional, but highly recommended)
Rebuild Index
Update Statistics
Back Up Database (Full)
Back Up Database (Transaction Log)
Maintenance Cleanup Task

After running the DBCC SHOWCONTIG query, examine the results for tables that have over 1,000 pages scanned.

- Extent Switches - Value should be 1 less then Extent Pages Scanned
The higher this value is above the Extent Pages Scanned, the more fragmented the page order
- Logical Scan Fragmentation - under 1%
Latency occurs once logical scan fragmentation exceeds 10%
Once fragmentation exceeds 50%, indexes are no longer used
- Extent Scan Fragmentation - under 50%
- Avg. Page density (full) - over 90%

 

Also see:
Recovery Model: http://msdn.microsoft.com/en-us/library/ms189275.aspx
Routine monitoring and maintenance for the Accelerator environment http://www.symantec.com/docs/TECH63230
SQL Index Fragmentation and Statistics http://www.symantec.com/docs/TECH168905

Published 10 years ago
Version 1.0
No CommentsBe the first to comment