SQL Server query option from BO Universe

[former_member226964]

Hello

We have identified some queries generated by our universe that has performance issues.

We have also discovered that those queries is performing a lot better with an older SQL Server compability level. We are currently running 2014 but the queries performs best with compability level 2012.

When running queries against a SQL Server you can manually tell the database to use the 2012 compability level by specifying:

OPTION (querytraceon 9481)

Is there a way to tell our universe to add that line at the end of each Query generated from the universe? Some option somewhere in Information Design Tool?

Thanks!

Jonathan

[luisdarui]

Hi Jonathan,

SQL Server 2014 introduced a new Cardinality Estimation (aka CE). You can disable it with the trace flag 9481.

Read SAP Note "1961467 - SQL Server 2014 and later - New Cardinality Estimation in Query Optimization" (this note is for SAP NetWeaver based systems, but the information about the CE is relevant for your case) and also this blog.

For all SAP systems based on SAP NetWeaver they are disabled by default.

Instead adding this option to the statement, you can set the trace flag at the SQL Server startup.

Follow SAP Note "1482275 - Setting a trace flag for SQL Server" to setup the trace flag 9841 on SQL Server startup (use the option for SQL Server 2005 and higher). It will revert to the old CE logic for all queries in this SQL Server instance.