Manage the Privilege Management Database Cache
When Privilege Management Reporting is deployed for larger corporate networks the amount of data can affect the performance of the reports. Reports are less interactive and load times can increase.
Upgrades to the processor, disk, or memory only make a negligible difference to the load times of reports once the database gets to a certain size. BeyondTrust has developed a solution to improve the user experience. This new feature introduces tables that store summary data to help return the information quickly.
The data summarization is linked to the current mechanism that normalizes data from the staging area. This is called by either the SQL Agent or Service Broker. Service Broker is installed by default but you can use the script Create_ER_Database_Agent.sql to use the SQL Agent.
The data is stored against the following intervals:
- 12 Months
- 6 Months
- 30 Days
- 7 Days
- 24 Hours
Each interval has an associated configuration that determines the amount of time until the data needs to be refreshed.
The data is refreshed depending on the interval:
- Once a day for 12-month, 6-month, and 30 day intervals
- Once an hour for 7-day interval
- Refreshed with new data for a 24-hour interval
Summarizing the grouping data adds overhead to the CopyFromStaging process. Summarizing is not done for every iteration on large installations.
The 24-hour interval is an exception; all data is refreshed since only cached data is stored for the Discovery dashboard.
Enable and Disable Report Caching
The cache feature can be enabled or disabled when you install the Privilege Management Reporting database. After installation, change cache settings using one of the following ways:
- In SQL Server Management Studio, connect to the database and run the following query:
UPDATE Config SET BitValue = 1 WHERE ConfigId = 'CacheEnabled'
- Open SQL Server Reporting Studio and navigate to the Admin folder. Open the ErpCacheAdmin report and click Enable or Disable, depending on the current state.
The grouping data is cached in a table and refreshed at a configurable interval. This can be every x minutes or at a specific time of the day. The values that determine this behavior are in the dbo.Config table.
The values can be manually edited by running the following script in SQL Server Management Studio.
DECLARE @Interval NVARCHAR(40)= NULL -- Set the interval Last12Months, Last6Months, Last30Days, Last7Days Set the local time of the server to run fresh cache -- daily for 12 / 6 Month, 30 Day eg '13:00' -- Time on the hour for 7 Day eg '13:05' DECLARE @LocalRefreshTime TIME = NULL DECLARE @ForceCache BIT = NULL -- Reset the force cache DECLARE @MyRefreshTime DATETIME IF @LocalRefreshTime IS NOT NULL AND @Interval IS NOT NULL BEGIN DECLARE @UTCOffset INT = DATEDIFF(MINUTE, GETDATE(), GETUTCDATE()) DECLARE @MinutesPastMidnight INT = DATEDIFF(MINUTE, '00:00:00.000', @LocalRefreshTime) SET @MyRefreshTime = DATEADD(MINUTE, @MinutesPastMidnight + @UTCOffset, CAST(CAST(GETUTCDATE() AS DATE) AS DATETIME)) UPDATE [dbo].[Config] SET DateTimeValue = ISNULL(@MyRefreshTime, DateTimeValue) WHERE ConfigId = 'Cache' + REPLACE(REPLACE(@Interval, 'Last', ''), ' ', '') + 'Refresh' END IF @ForceCache IS NOT NULL UPDATE [dbo].[Config] SET BigValue = @ForceCache, DateTimeValue = GETUTCDATE() WHERE ConfigId = 'ForceCache'