Troubleshooting
Problem
User opens an Excel link report (XLSX file). User presses F9 (to run/refresh the report). This takes a long time.
Symptom
In one real-life customer example:
- Before running "DBCC FREEPROCCACHE", the report seemingly never finished running (it took more than one hour, after which the end user gave up waiting)
- After running "DBCC FREEPROCCACHE", the same report took 210 seconds to run.
Cause
There are many possible causes for slow Excel link report speed.
- TIP: See separate IBM Technote #1347497 for more examples.
This Technote specifically relates to the scenario where the cause is old/out-of-date 'cached' Microsoft SQL execution plans (which are still in use, despite changing the SQL database compatibility level to 2012 - or earlier).
More Information:
A similar problem has been seen to be cured by changing the SQL database compatibility level to 2012 (or earlier).
- For more details, see separate IBM Technote #2015368.
However (in some environments) performing that change may not solve the problem completely. In one real-life customer example, the SQL server somehow used the new cardinality estimator for building the execution plans for the report queries even though the database was configured to use SQL compatibility level 2008 (and therefore the old cardinality estimator)!
Environment
The problem is most likely to be seen when customers upgrade from an older version of Microsoft SQL, to Microsoft SQL 2014 (or later).
- This is because Microsoft introduced a new cardinality estimator (from SQL Server 2014 onwards) which can sometimes choose a bad execution plan for some queries.
Resolving The Problem
Perform *both* of the following:
(1) Modify the database to use the legacy cardinality estimation
- If using SQL 2014, this means changing the 'Compatibility level' to be 'SQL Server 2012 (110)' (or earlier)
- If using SQL 2016 (or later), this means changing the database option "Legacy Cardinality Estimation" to be "ON".
(2) Delete all SQL cached execution plans.
- This will force all new queries to use the old/legacy (fast) cardinality estimator.
Steps:
1. To force the database to use the legacy cardinality estimation, perform the steps inside separate IBM Technote #2015368.
2. Inside 'SQL Server Management Studio', right-click on the database 'master' and click 'New Query':
3. Type in the following: DBCC FREEPROCCACHE
4. Press 'Execute':
5. Finally, as a precaution, perform a database optimise, and choose 'Rebuild Indexes' option.
- For instructions, see separate IBM Technote #1367485.
6. Test.
Related Information
Was this topic helpful?
Document Information
Modified date:
19 December 2018
UID
ibm10792049