1. Support
  2. Knowledge base
  3. 100038534

Configuring the SQL fragmentation System Status hidden monitors

Article: 100038534
Last Published: 2015-05-26
Ratings: 0 0
Product(s): Enterprise Vault

Description

Monitor 1: SQL fragmentation computation (SFC) monitor:


This monitor starts the SFC for Enterprise Vault databases (Directory, Vault Store, and Fingerprint). One entry for each Enterprise Vault Site is created in the Monitoring table of the EntepriseVaultDirectory database.
 
Here are the default values at the time of fresh or upgrade installation:
  • MonitoringItemType: 33
  • Enabled: 1
  • Threshold: -1
  • FrequencyPeriod: 1 (1st day of week, i.e. Sunday)
  • FrequencyUnit: 4 (Day of Week)
  • StartAt: 21:00 (9 p.m., UTC)
  • StartAtEnabled: 1
  • MonitoringHistoryResults: 20
  • Hidden: 1 (System-define monitor, so hidden from Vault Administration Console)
     

Monitor 2: SQL fragmentation levels monitor
 

This monitor reads the SQL fragmentation results of Enterprise Vault databases (Directory, Vault Store, and Fingerprint). One entry for each Enterprise Vault Site is created in the Monitoring table of the EntepriseVaultDirectory database.
 
Here are the default values at the time of fresh or upgrade installation::
  • MonitoringItemType: 34
  • Enabled: 1
  • Threshold: 30
  • FrequencyPeriod: 1 (Everyday)
  • FrequencyUnit: 2 (Days)
  • StartAt: 09:00 (9 a.m., Local)
  • StartAtEnabled: 1
  • MonitoringHistoryResults: 20
  • Hidden: 1 (System-define monitor, so hidden from Vault Administration Console)
 
You can change the following settings to suit your environment:
  • Threshold (only for SQL fragmentation levels monitor)
  • FrequencyPeriod
  • FrequencyUnit
  • StartAt
     

Additional Conditions:
 

  • Make sure that all Enterprise Vault Sites within an Enterprise Vault Directory have the same configuration for SQL fragmentation computation (SFC) monitor.
    • If there are different configurations for different Enterprise Vault Sites in an Enterprise Vault Directory the behavior is unpredictable.
       
  • SFC is a resource costly operation, care should be taken when configuring StartAt time:
    ​
    • The  StartAt  time stored in the database for  SFC  monitor is in UTC time zone.
    • If SQL Server and the Enterprise Vault Servers are in different time zones, make sure that the SFC executes on non-core hours.
    • Make sure that, SFC does not execute within Enterprise Vault archive window, database backup and maintenance window, or any other activity that is critical.
       
  • Due to the fact that  SFC  is a time consuming operation and might impact the SQL Server performance, avoid using  <NewFrequencyUnit>  set to 1 (Hour) for the  SFC  monitor. Support recommends that the SFC monitor is not executed more than once a week.
     
  • Use <NewFrequencyUnit> as 4 (Day of Week) or 5 (Day of month) to control the execution on non-core working days.
     
  • Based on product default settings for fragmentation computations, alerts may persist for up to a week, even after remedial action to correct them has been taken.​

Changing Settings:
 

Setting changes are controlled executing the SQL queries on the SQL Server that hosts the  EnterpriseVaultDirectory  database. The new setting takes effect at the next SQL fragmentation execution.

USE [ EnterpriseVaultDirectory ]
GO
 
UPDATE [dbo].[Monitoring]
SET [Enabled] = <NewEnabledValue>,
[FrequencyUnit] = <NewFrequencyUnit>,
[FrequencyPeriod] = <NewFrequencyPeriod>,
[StartAt] = <NewStartAtTimeInUTC>
WHERE [MonitoringItemType] = 33
GO 

<NewEnabledValue> counter value set to 1 enables or 0 disables.

<NewFrequencyUnit> has multiple configuration values
 
Value
Description
1 (Hours)
Every x hours
2 (Days)
Every x days
3 (Weeks)
Every x weeks
4 (Day of week)
Day of week from 1 – 7
5 (Day of month)
Day of month from 1 – 31
 
<NewFrequencyPeriod> this value will be in correspondence with  <NewFrequencyUnit>
 
When <NewFrequencyUnit>
Valid range for <NewFrequencyPeriod>
1 (Hours)
1 to x (Number of Hours / Days / Weeks)
2 (Days)
3 (Weeks)
4 (Day of week)
1 – 7 (1 as Sunday and 7 as Saturday)
5 (Day of month)
1 – 31 (when set to 31, and the days of current schedule month are less than 31, it is automatically set to the maximum days of the current month. So effectively, it will work as last day of every month.)
 

Recommendations:

  • Do not use <NewFrequencyUnit> as 1 (Hour) because fragmentation computation is a time consuming operation that might impact the SQL Server performance.
     
  • Set SFC monitor to execute once a week at the maximum. You can use <NewFrequencyUnit> as 4 (Day of week) or 5 (Day of month) to control the execution on non-core working days.
     
  • <NewStartAtTimeInUTC> is the UTC time at which to start the SFC
     

Examples:

To update computation start time to 11:00 pm (UTC) for all the sites:
 
USE [EnterpriseVaultDirectory]
GO
 
UPDATE [dbo].[Monitoring]
SET [StartAt] = '23:00'
WHERE [MonitoringItemType] = 33
GO
 
To update settings of all the sites to run computation on first day of every month:
 
USE [EnterpriseVaultDirectory]
GO
 
UPDATE [dbo].[Monitoring]
SET [FrequencyUnit] = 5,
[FrequencyPeriod] = 1
WHERE [MonitoringItemType] = 33
GO
 
To update SQL fragmentation levels monitor configuration settings:
 
USE [EnterpriseVaultDirectory]
GO
 
UPDATE [dbo].[Monitoring]
SET [Enabled] = <NewEnabledValue>,
[FrequencyUnit] = <NewFrequencyUnit>,
[FrequencyPeriod] = <NewFrequencyPeriod>,
[StartAt] = <NewStartAtTimeLocal>,
[Threshold] = <NewThresholdValue>
WHERE [MonitoringItemType] = 34
GO where <NewEnabledValue> can be 1 to enable the counter or 0 to disable the counter.
 
<NewFrequencyUnit> has following options
 
Value
Meaning
0 (Minutes)
Every x minutes
1 (Hours)
Every x hours
2 (Days)
Every x days
3 (Weeks)
Every x weeks
4 (Day of week)
Day of week from 1 – 7
5 (Day of month)
Day of month from 1 – 31
 
<NewFrequencyPeriod> works with  <NewFrequencyUnit>
 
When <NewFrequencyUnit>
Valid range for <NewFrequencyPeriod>
0 (Minutes)
1 to x (Number of Minutes/Hours/Days/Weeks)
1 (Hours)
2 (Days)
3 (Weeks)
4 (Day of week)
1 – 7 (1 as Sunday and 7 as Saturday)
5 (Day of month)
1 – 31 (when set to 31, and the days of current schedule month are less than 31, it is automatically set to the maximum days of the current month. So effectively, it will work as last day of every month.)
 
<NewStartAtTimeLocal> is the local time at which SQL fragmentation levels should be checked
<NewThresholdValue> is the new threshold to be applied
 
Examples:
To update fragmentation level check start time to 7:00am (Local), and modify threshold to 40 for all the sites:
 
USE [EnterpriseVaultDirectory]
GO
 
UPDATE [dbo].[Monitoring]
SET [StartAt] = '7:00',
[Threshold] = 40
WHERE [MonitoringItemType] = 34
GO
 
To update settings of all the sites to run fragmentation level check every alternate day:
 
USE [EnterpriseVaultDirectory]
GO
 
UPDATE [dbo].[Monitoring]
SET [FrequencyUnit] = 2,
[FrequencyPeriod] = 2
WHERE [MonitoringItemType] = 34
GO

Was this content helpful?

Article Languages