Plan_Guide not working for cross DB queries

  • Investigation on longer running queries has shown high compile times for some adhoc queries sent from a front end app.

    I have added Plan Guides and they seem to work for all but one query.

    This query is a cross DB join (on the same server) but does not seem to want to use the Plan Guide.

    The query is like this:

    SELECT SUM(QUANTITY) AS COUNTER

    FROM vw_TRANS_LINE_CATEGORY a

    ,vw_DLG_Participants c

    WHERE QUANTITY > 0

    AND a.IS_USEDUP != 1

    AND a.TRANSACTION_ID = '123456'

    AND a.TOTAL_AMOUNT >= 0.01

    AND c.DP_DG_ID IN (1234)

    AND c.CUSTOMER_ID = a.CUSTOMER_ID

    So as you can see it is an Old Style join.

    I bring this up because when I change it to this :

    SELECT SUM(QUANTITY) AS COUNTER

    FROM vw_TRANS_LINE_CATEGORY a

    JOIN vw_DLG_Participants c ON c.CUSTOMER_ID = a.CUSTOMER_ID

    WHERE QUANTITY > 0

    AND a.IS_USEDUP != 1

    AND a.TRANSACTION_ID = '123456'

    AND a.TOTAL_AMOUNT >= 0.01

    AND c.DP_DG_ID IN ( 1234)

    and add a new Plan Guide it seems to only have high compilation times on the first couple of attempts then it has no compilation time, which is probably normal (I am using optimise for adhoc workloads).

    I have also noticed that in some cases the execution plan is the same between the two joins and sometimes it is different for the ANSI 92 JOIN.

    I think it might not be an old style join issue but an old style join issue between databases trying to use a Plan Guide?

    Any ideas??

    thanks

  • A bunch of things going on with this one. First off, you're joining on views, which presumably have multiple tables joining together within them. That would reflect in the high compile times for such simple seeming queries. I imagine if you looked at the Reason For Early Termination on the execution plans you'd see that many (most?) are TimeOut. This means that the optimizer did as much work as it possibly could before it simply gave up and took the best plan it had available at the time. In your case, joining views, it's likely that it's running out of time during the Simplification process where it tries to eliminate tables that are not needed to satisfy the query. It's a very common issue when joining or nesting views (or when joining nested views which makes the problem even worse). Best solution in this case, simplify the queries.

    Using Plan Guides, I'm pretty sure you can't cross databases. They are scoped to that they are created in. It's not something I've tried though. I'd suggest running sys.fin_validate_plan_guide to see if the ones giving you trouble pass.

    As to the ANSI-92 vs ANSI-89 join syntax, except for the fact that outer joins will cause you no end of trouble, inner joins will usually resolve the same way within the optimizer... usually. Because of the issues with joining views, it's likely that you can get different plans for the same query.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks for the reply.

    As you allude to at least one view has multiple joins in it.

    If I move the data from to second database and alter the join (so it is not cross database) ,then create a new plan_guide this works fine.

    So as you say, it looks like Plan_Guides are not effective across databases.

    As in a lot of cases it is not that easy to "simplify" a query because the person that is lumbered with Performance Tuning is not always the person writing the queries orsupplying the DDL (damn developers!!).

  • Ha!

    Well developers have their cross to bear as well as us. I get it. Entirely. However, I'm pretty sure, based on the info provided, that's where the problem lies.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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