Versions Compared

Version Old Version 1 New Version Current
Changes made by

Mahnoor Khan (Deactivated)

Greg Walsh

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • Check the EngageIP Event Log (Reports tab > Event Log) to see if any major activity is occurring (billing, credit card processing, etc.)

  • Determine if any reports are being run which can temporarily tie up system resources (Accounting personnel running an aged report for example)

  • Determine if any jobs are in the job queue which the EngageIP is processing (Reports tab > Jobs). If so ensure performance issues do not continue when jobs are complete

  • Ensure EngageIP Services are running and operating normally. E.g. if the Event Service is running but workflows are not executing or emails being sent then restart the Event Service. For more information on EngageIP Windows Services see the EngageIP Windows Services Overview article

 

Configuration Tweaks:

The blue boxes in the image below indicate the locations you can adjust to best fit your requirements and limit or manage database size and data retention.

 

  • Logging Check Boxes: Do you need to use domain model logging which is a deeper level logging than just web UI logging?

  • Retention Periods: Do you need to maintain data beyond 2 or 3 months? Minimize the retention period as much as possible. Remember that if you're storing backups of the database, those too will have record of logs if you need to locate information.

...

  • If possible, separate Operating system files and EngageIP billing files (program folders or database files) onto different physical hard drives (unless you're using a RAID configuration)

  • Ensure that there is enough RAM on the server for processes. RAM is usually easy to increase on servers and provides a tangible performance increase. If there are is only a small amount of free RAM on the server, consider increasing the RAM so that the system has a 'fair amount' of free RAM available for any periods of high activity (processing spikes)

    • If there are further questions about whether there is enough RAM on the server, please consult LogiSense Customer Care for suggestions and assistance
      LogiSense Support   email: support@logisense.comweb: Live Supportphone (Canada): 1-519-249-0508 •phone (USA): 1-917-410-3584

  • Ensure that the server(s) are only runing running EngageIP and that there are no other processes taking up CPU / RAM / IO from hard drives. Processes unrelated to EngageIP can leach performance from EngageIP, therefore it is recommended that EngageIP servers (web and SQL) are dedicated to run the EngageIP platform alone

  • Check for Windows Event Log errors that may indicate a problem on the server which may impact performance

  • Check that the connection method between SQL and web servers is based on IP address. In some cases using host names can slow communication if the hostname needs to be resolved each time. A slow/busy DNS server can also be the cause of performance issues when a hostname is used instead of an IP

  • Create SQL maintenance plan (as described below) to keep SQL running at peak performance

  • Add OFF server and OFF site backup systems or drives in case of hard drive or file corruption or damage. Some examples are as follows:

    • External USB drives

    • Network backup servers

    • Mirrored / Clustered SQL services

...

  1. Setup SQL server maintenance plan to index, backup, and check integrity weekly or twice a week, whichever is desired. The databases for EngageIP 8 include (though all may not be installed): BOSS, BOSSCollector, BOSSRate (usually this db is not present) and EngageIP Master

    Image Modified

...

  1. Setup Microsoft Backup to keep weekly backups of EngageIP Billing folder, etc. that may need backing up. This is located in 'Programs / Accessories / System Tools / Backup'

    Image Modified

...

  1. Perform analysis of system performance including hard drive space and RAM

    1. Ensure that SQL is setup optimally for use of RAM, CPU attenuation, overall integrity of SQL installation. Maximum server memory should be set to 90% of available RAM, minimum server memory should be set to 80% of available RAM. A minimum of 2GB should be left for the operating

...

    1. system

      Image Modified

    2. Review running applications and tasks on the server to ensure they are not interfering with EngageIP (e.g. anti virus, scheduled tasks, etc.). For example, ensure that real-time virus scanning is not configured to scan the EngageIP Billing folders as files may need to be read or written to as part of regular processes, antivirus real-time protection may inhibit this action

...

  1. Perform analysis of SQL Server CPU Configuration

    1. Set the "max degree of parallelism" SQL Server can use for a parallel query to 50% of available CPU Cores

    2. Increase the default "cost threshold for parallelism" from 5 to 20

...

  1. Perform an analysis of the SQL Server tempdb Configuration

    1. Ensure that the tempdb files are multiplexed. A good rule of thumb is one tempdb file for every two CPU cores up to eight tempdb files. Beyond eight tempdb files brings limited benefits

    2. All tempdb files should be the same size after a system start. Tempdb files will individually grow in size to the maximum transaction size. Ensure there is sufficient disk space to allow this growth to happen

Real-Time Database Indexing

...

  1. Open Studio Management

  2. Click the 'New Query' button

  3. Then paste in the SQL below and select the appropriate database (typically named 'boss')

    Code Block
    USE BOSS --Enter the name of the database you want to reindex 
DECLARE @TableName varchar(255) 
DECLARE TableCursor CURSOR FOR 
SELECT table_name FROM information_schema.tables

WHERE table_type = 'base table'  
OPEN TableCursor  
FETCH NEXT FROM TableCursor INTO @TableName
  WHILE @@FETCH_STATUS = 0 
  BEGIN 
  DBCC DBREINDEX(@TableName,' ',90) 
  FETCH NEXT FROM TableCursor INTO @TableName
   END

  4. Update the database name in the query if its not ‘boss

  5. Execute
    Note: this query may take several minutes, wait until it's complete and then review invoices / the interface to determine if there is any performance improvement

...

  1. Open Query Analyzer or  a new query in SQL 2005

  2. Paste the following code
    The below indexes the WHOLE database including usage table

    Code Block
    USE BOSS --Enter the name of the database you want to reindex
DECLARE @TableName VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END


    The below index script skips the usage tables so as to shorten the run time and index the critical GUI critical tables

    Code Block
    DECLARE @sql NVARCHAR(500)
DECLARE @TableName varchar(255)
DECLARE @Message varchar(255)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Message = 'Starting ... '+ @TableName
IF (CHARINDEX('CDR',@TableName) <>0 OR CHARINDEX('Log',@TableName) <>0 OR CHARINDEX('UDR',@TableName) <>0)
BEGIN
SET @Message = 'Skipping '+ @TableName
RAISERROR (@Message , 0, 1) WITH NOWAIT;
END
ELSE
BEGIN
RAISERROR (@Message, 0, 1) WITH NOWAIT;
SET @sql = 'ALTER INDEX ALL ON [' + @TableName + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
END
SET @Message = 'Done ... '+ @TableName
RAISERROR (@Message , 0, 1) WITH NOWAIT;
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
EXEC sp_updatestats

...

  2. Update the reference of the database (in this case it reads 'BOSS')

...

  1. Click Execute

...

  1. Wait for it to finish, then you can close the window

Database Backup Process

For MSSQL 2014, see this article: http://msdn.microsoft.com/en-us/library/ms187510.aspx

...

UDR Records on a system may take significant amounts of space. For usage, the file EngageIPUDRFILEGROUP.ndf may get very large. This is a data storage format file for SQL server which houses data related to the UDR table in the EngageIP database. If this file gets large, you will need to purge / archive UDR data. Please contact LogiSense for details on archiving data or reference this article: https://developer.logisense.com/archiving-old-records-udr-system/