VERIFIED SOLUTION i

EngageOne Designer performance poor with SQL 2014

Product Feature: Designer

 

Issue

Various performance issues were reported when EngageOne Designer is hosted on SQL Server 2014.
 

Cause

This is due to changes in the Microsoft SQL Server 2014 Query Cardinality estimator performance.

Resolution

UPDATED: June 11, 2018
The resolution is documented in the EngageOne Designer Release Notes - as listed below:

Default Designer performance on Microsoft SQL Server 2014 has been observed to be noticeably slower compared to SQL Server 2012 when opening items such as Public Documents and Keyed Active Content.This is due to changes in the Microsoft SQL Server 2014 Query Cardinality estimator performance.

For details refer to:
https://blogs.msdn.microsoft.com/psssql/2015/06/16/identifying-sql-server-2014-new-cardinality-estimator-issues-and-service-pack-1-improvement/

Workarounds:
• Update SQL Server (Affects ALL Databases)

The following SQL Server 2014 Query performance -T9481 Startup Parameter requires - and has been tested on Microsoft SQL Server 2014 SP1 or higher;

1. Invoke Microsoft SQL Server 2014 Configuration Manager.
2. Select SQL Server Services on the SQL Configuration Manager Left hand pane.
3. Select SQL Server <Instance Name> on the SQL Configuration Manager. Right hand pane - usually the first entry in the list.
4. Invoke SQL Server <Instance Name> context menu and select Properties context menu.
5. On resulting SQL Server <Instance Name> Properties dialog , select the Startup Parameters page.
6. In the Specify a startup parameter entry field , type -T9481 and press Add button – the new entry should then appear in the existing parameters list.
7. Press SQL Server <Instance Name> Properties dialog Apply and OK buttons.
8. Press OK on 'Changes will not take effect until SQL Server Service is restarted' Message.
9. From Windows Server Services, Restart the SQL Server Service.

OR

• Update Individual Designer Database SQL Compatibility level version

1. Invoke Microsoft SQL Server 2014 Management studio and connect to the relevant Designer SQL ServerName.
2. In the Object Explorer Pane , select and expand the Databases Folder such that all Databases are visible.
3. Select the required Designer Database, invoke context menu and select Properties context menu option.
4. On Database Properties dialog, select Options setting under the Select a page pane.
5. Select the Compatibility level field and choose option SQL Server 2012 (110) option.
6. Press OK button on the Database Properties dialog.

Important Compatibility Setting note:
An issue has been observed with the Designer Repository Configuration Tool being unable to perform a Backup in SQL 2014 with SQL 2012 Compatibility option selected. User must use SQL 2014 Management studio to perform a Manual SQL Backup and ZIP Filestore if this Individual Database Compatibility option is used.
The Backup issue does not occur if the "-T9481" Startup Parameter is used instead of setting individual Database Compatibility level.

Performance improvements with Designer V6.6.7 when used with SQL2016, so it is recommended to upgrade to these versions if possible.