SQL maintenance might be one of the most important things in an Enterprise Vault environment. It’s something you truly cannot neglect or the performance and functionality of Enterprise Vault can be severely affected. Unfortunately, the lack of SQL maintenance is a big problem in many environments out there.
Here is some helpful information on SQL maintenance for Enterprise Vault:
First, if you’re not familiar with this technote (http://www.veritas.com/docs/000040169) you should review it in-depth. The first half of the SQL technote mentioned is for backups; the second half is for maintenance.
Many environments are not able to perform daily maintenance, offline maintenance and so forth. Your goal should be to come up with a regular maintenance schedule that works for your environment and keeps the database fragmentation near the recommended levels.
Veritas pushes the use of DBCC SHOWCONTIG with ALL_INDEXES to review fragmentation levels since Enterprise Vault indices do not span multiple data files, and the Extent fragmentation value provides a valuable overview of the index fragmentation health (though see DMVs below).
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 – Value should be under 1%
Latency occurs once logical scan fragmentation exceeds 10%
– Extent Scan Fragmentation – under 50%
Performance issues may occur for fragmentation over 50%
As of SQL Server 2005 the Data Collector can be used to gather and store performance metrics in order to monitor trends over time. The SQL Server 2005 Data Collector can be downloaded from Microsoft, and is integrated into SQL Server 2008 onward.
Dynamic Management Views can also be used to provide current performance metrics and fragmentation review, as from SQL Server 2005 onward, Microsoft vastly expanded the range and depth of metadata that could be exposed regarding the SQL connections, sessions, transactions, statements, processes and their current state (i.e. Waits, locks and blocks) and so forth, that are (or have been) executing against a database instance. DMVs are basically a set of System Views like any other table view to view the state or past statistics of the SQL Server.
The sys.dm_db_index_physical_stats function is the preferred DMV to use to check index fragmentation, with three modes:
1. Detailed Very extensive and check both Internal and logical fragmentation at all levels
2. Limited Very fast. Checks only logical fragmentation at the leaf level i.e. Index Level 0 – recommended way.
3. Sampled Detailed mode will be used if the index has less than 10K pages. Otherwise a 1% sample of all the pages in the index is returned
In using the DMV there is often an Extent fragmentation misunderstanding, which sparks the debate of DBCC SHOWCONTIG. However, the Logical Fragmentation will essentially include Extent Fragmentation. An extent is a cluster of 8 pages. The algorithm will check if the pages are in order irrespective of the extent. The fragment count provides further insight about this. A fragment consist of a batch of pages which are in consecutive order. If the extent was out of order, or if pages within the extent were out of order, this would affect the number of fragments and hence affect the fragmentation level. Those values are provided by fragment_count and avg_fragment_size_in_pages. (See https://msdn.microsoft.com/en-us/library/ms188917.aspx)
For further information on SQL maintenance for Enterprise Vault; or for health checks, ways to monitor your environment, custom scripts and ways to perform maintenance: contact us at any time.
Also see:
SQL Index Fragmentation and Statistics: http://www.veritas.com/docs/000013531
Recommended Steps to Manually Rebuild SQL Indexes for Enterprise Vault and Accelerator Databases: http://www.veritas.com/docs/000008215
