Optimizer Changes / New need for join hints on views?

  • I have a complex database that I have been working with since SQL2005...many years on SQL2008R2, and then recently (a few months ago) moved to SQL2016.

    On SQL2016 I have been noticing that the optimizer is picking poor query plans much more frequently than on past versions of SQL.

    This database makes extensive use of views, and frequently joins multiple views (sometimes several dozen) in a query.  The base tables are appropriately indexed (both sides of the join), with good primary/foreign keys... and SQL has performed fairly well until SQL2016.  The views themselves are not indexed.

    I have discovered that on queries that perform poorly on SQL2016 (that used to perform well on SQL2008R2), if I use a join hint (specifically an INNER MERGE JOIN) the performance often improves dramatically.  For example given a query that joins 10 views, with perhaps a few hundred thousand rows involved, adding an INNER MERGE JOIN to one of the joins can drop execution time from maybe 15+ seconds down to 2 seconds.

    It seems like the SQL2016 optimizer is gun-shy about using MERGE joins when joining views, even if both base tables have indexes on the joined fields.  It seems like SQL2008R2 did not (noticeably) have this problem.

    Has anyone else noticed this change of behavior?  Is it documented anywhere?

    I am nervous about habitually specifying join hints, but find that they are frequently needed.  I haven't comprehensively found the optimal pattern, but have an emerging "best guess" of where to specify an INNER MERGE JOIN to restore performance.

    I have not yet done a lot of work with the actual query plans:  the views themselves are fairly complex, and the plans that join many views get cluttered in a hurry.

    I know actual query plans, actual table, index and view definitions, etc., etc. would be important to a detailed investigation.  These are difficult to provide out of this complex database, and at this point I am just asking conceptually about what changes in the SQL2016 optimizer might have a detrimental impact on JOIN performance of views.

    Anyone here have any observations, experience, or information to share?

  • Are they covering indexes?  Just having a nonclustered index on a joining column(s) is often not useful for SQL, unless that index fully covers the query.

    I have had times even in SQL2008 where I had to explicitly specify "MERGE" join as well. Rare, but it can make a huge difference at times.

    I'd start by reviewing the indexes, particularly the clus indexes, in conjunction not just with this query plan but will all uses of the table.  SQL provides index usage stats, missing index stats and operational index stats that allow you to do this.

    You have to decide which type of queries are the most critical and tune for those.  Typically it's day-to-day usage or reporting, not initial INSERT.  Tune accordingly.

    Sorry, I know this is somewhat vague ... but then again your issue is too as well 😀

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • David Rueter - Tuesday, March 20, 2018 2:27 AM

    I have a complex database that I have been working with since SQL2005...many years on SQL2008R2, and then recently (a few months ago) moved to SQL2016.

    On SQL2016 I have been noticing that the optimizer is picking poor query plans much more frequently than on past versions of SQL.

    This database makes extensive use of views, and frequently joins multiple views (sometimes several dozen) in a query.  The base tables are appropriately indexed (both sides of the join), with good primary/foreign keys... and SQL has performed fairly well until SQL2016.  The views themselves are not indexed.

    I have discovered that on queries that perform poorly on SQL2016 (that used to perform well on SQL2008R2), if I use a join hint (specifically an INNER MERGE JOIN) the performance often improves dramatically.  For example given a query that joins 10 views, with perhaps a few hundred thousand rows involved, adding an INNER MERGE JOIN to one of the joins can drop execution time from maybe 15+ seconds down to 2 seconds.

    It seems like the SQL2016 optimizer is gun-shy about using MERGE joins when joining views, even if both base tables have indexes on the joined fields.  It seems like SQL2008R2 did not (noticeably) have this problem.

    Has anyone else noticed this change of behavior?  Is it documented anywhere?

    I am nervous about habitually specifying join hints, but find that they are frequently needed.  I haven't comprehensively found the optimal pattern, but have an emerging "best guess" of where to specify an INNER MERGE JOIN to restore performance.

    I have not yet done a lot of work with the actual query plans:  the views themselves are fairly complex, and the plans that join many views get cluttered in a hurry.

    I know actual query plans, actual table, index and view definitions, etc., etc. would be important to a detailed investigation.  These are difficult to provide out of this complex database, and at this point I am just asking conceptually about what changes in the SQL2016 optimizer might have a detrimental impact on JOIN performance of views.

    Anyone here have any observations, experience, or information to share?

    You're most likely the victim of the changes made to the optimizer back in SQL 2014.   The newer version provides some true gains in many situations, but at the expense of taking what appear to be good queries, and exposing their weaknesses.   Nested views have never been a good idea, and it may just be that this "bad apple" was simply "you got lucky" up until now.  Nested views make the optimizer's job a heck of a lot harder, with zero benefit.   You may want to re-write the query without using the views, and take out the stuff you don't actually need, and probably gain performance in the process.   There is an alternative for SQL 2016 and above that allows you to turn on a trace flag for a specific query, that then gives you back the old version of the optimizer for just that query.   You'll have to Google that, as I don't recall the details.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply