Report Services computation takes longer than it used to

Problem: If Report Services uses SQL Server 2014 or above, you might notice that Report Services spends more time on computation.

Explanation: In SQL Server 2014, Microsoft introduced a new cardinality estimator (CE). This cardinality estimator was redesigned to improve query performance, and there may be some performance degradation for some SQL statements.

Solution: If you notice some report services computation performance issues, set the database compatibility level to 110 to force SQL Server to use the old cardinality estimator.

To set the database compatibility level to 110:

  1. In SQL Server Management studio, run the following before Report Services synchronizes with Active Directory:

    ALTER DATABASE <the database name deployed by Report Services>
    SET COMPATIBILITY_LEVEL = 110