Troubleshooting
Problem
User opens spreadsheet (XLSX file) Excel-link report. User logs onto Controller, and presses F9. The report takes a long time to finish.
Symptom
In one real-life example, a typical report took the following approximate time to run:
- 230 seconds when "Legacy Cardinality Estimation" = "OFF"
- 15 seconds when "Legacy Cardinality Estimation" = "ON"
Cause
There are many different possible causes for slow Excel link performance.
- TIP: See separate IBM Technote #1347497 for more details.
This Technote specifically relates to the scenario where the cause is slow SQL database responses, triggered by a Microsoft change to how their SQL indexes work (for SQL versions from 2014 onwards).
- In other words, the cause is that the Microsoft SQL Cardinality Estimation is causing very slow SQL performance.
More Information:
Microsoft made a large change (from SQL Server 2014 onwards) in the design of its the cardinality estimation. This is a major change to the optimisation space. In theory, the new estimates fit the actual data distribution better leading to a fast query execution.
- However, for some situations it can cause slow execution for some SQL queries / stored procedures.
Unfortunately IBM R&D cannot modify the Controller product to change its behaviour so that it causes SQL to use different cardinality estimations. Therefore, customers must change their SQL database setting so that it forces the use of the 'old' (SQL 2012 and earlier) type of cardinality estimation.
- IBM R&D state that this change has no negative effects. For example, the change is only on a single database level (not server-wide) and we believe that it has no negative side-effects for Controller.
Environment
SQL 2016
Both of the following are true:
(1) Controller database's 'Compatibility level' is set to either 'SQL Server 2014 (120)' or 'SQL Server 2016 (130)':
(2) Controller database's setting "Legacy Cardinality Estimation" is configured as "OFF":
SQL 2014
If using SQL 2014, then the problem occurs when compatibility level is set to SQL Server 2014 (120):
Resolving The Problem
There are two different methods to fix the problem:
- Method #1 (recommended for SQL 2016 and later):
Steps:
Ask your I.T. department's SQL administrator (DBA) to:
1. Launch SQL Management Studio
2. Right-click on the Controller database and choose 'Properties'
3. Click "Options"
4. Change the setting "Legacy Cardinality Estimation" to be "ON".
TIP: The change is instant. There is no need to restart Controller or reboot the SQL server.
- Method #2 (works for SQL 2014 as well as later versions):
Steps:
1. Logon to the SQL database server
2. Launch SQL Management Studio
3. Right-click on the 'bad' database and choose 'properties'
4. Click on 'Options'
5. Modify the 'Compatibility level' from the current to "SQL server 2012 (110)":
- TIP: The change is instant. There is no need to restart Controller or reboot the SQL server.
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg22015368