OnBase V18 Custom Query Performance and SQL Server Database Compatibility Level

[Dale_Rohr]

We installed OnBase V18 (18.0.1 - Build #: 46) to Production on 4/12.  New environment is running Windows 2016 OS and using SQL Server 2016 database.

Following implementation custom query performance was unacceptable - Various queries taking 15 - 90 seconds to render the results Hit List.  CPU utilization on Database was averaging 60-70%.

We subsequently changed the Compatiblity Level within SQL Server on the OnBase database to '2008' and saw immediate improvement - Queries began rendering Hit List results in the 1-2 second range (which aligns with expectations.)  CPU utilization on the Database has subsided and is now averaging 20% also consistent with expectations.

While overall application performance is now back to normal, we are unclear as to why a SQL Server compatibility level needed to be set to '2008' in order to restore normal query performance.

Welcome any guidance here AND any recommendations on how we can position ourselves to take full advantage of SQL Server 2016 (with 2016 compatibility level.)

Thank You

[Joel_Moore1 · Accepted solution]

Hi Dale,

It is possible that setting the Compatibility Level to 2008 is forcing the use of the Legacy Cardinality Estimator. I have worked on a number of cases where the Cardinality Estimator introduced in SQL Server 2014 (also leveraged in SQL Server 2016) can produce less optimal Execution Plans, and using the Legacy Cardinality Estimator resolved the issue.

In these cases, a systematic approach was taken to determine the Cardinality Estimator was the culprit, though. I would advise reaching out to your FLOS for assistance if you are not sure where to start looking in order to prove this out, as they will be able to help. 

I hope this helps!

-Joel 

[Dale_Rohr]

Thanks Joel for your timely reply!  Appreciate the lead and we will further investigate referenced Cardinality Estimator and will advise if this is deemed the culprit. 

Thank You

Dale 

[Chris_Hardenbur]

We ran into a similar type of issue (we're on SQL 2014) and we are using the legacy cardinality estimator. 

[Ryan_Coyne]

Hi Dale,

In addition to what Joel mentioned, you can reference the following blog post from Community.  Note that the post was originally written following the release of SQL Server 2014, where the only options to revert this behavior were to lower the database compatibility level (as you have), or enable SQL Server trace flags: https://community.hyland.com/blog/posts/52735-sql-server-2014-cardinality-estimator-performance-issu...

With SQL Server 2016 (and higher), a new option to enable the Legacy Cardinality Estimation was introduced at the database level, which will allow the compatibility level to match that of the SQL Server instance (e.g. 130 for 2016) while still using the Legacy Cardinality Estimator.

That option can be found in: SQL Server Management Studio | Database Properties | Options | Database Scoped Configurations | Legacy Cardinality Estimation.  Making this change does not require SQL Server or OnBase service restart(s), but I'd suggest working with your DBA team before making the change to Production.

Thanks,
Ryan