Skip to Content
0

SQL Server query option from BO Universe

Mar 06, 2017 at 12:57 PM

72

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Luis Darui
Mar 06, 2017 at 06:01 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded