web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / SQL-Server Plan Guide ...
Microsoft Dynamics AX (Archived)

SQL-Server Plan Guide for DAX x++ Select Statement

(0) ShareShare
ReportReport
Posted on by bnorma01 119

I have a select statement in DAX X+ which in one environment performs well but in another does not.  I can see the SQL plan when I view the select using SQL Server Profiler in both environments.  I want to take the plan from the one environment and force it on the query in the other environment.  Does anyone have a good step by step to implement a plan guide for a DAX X++ select in SQL-Server ?  I have already compared indexes and such.  The data in each environment is fairly close given one is a copy of the other from a few weeks ago.

*This post is locked for comments

I have the same question (0)
  • Mea_ Profile Picture
    Mea_ 60,284 on at

    Hi bnorma01,

    There is not way to do this, AX does not control plan generation at all, all is done by SQL base on statistic and index fragmentation. Do you have SQL maintenance plan in place to update statistic and defragment indexes on both environments ?

  • Verified answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    With the first script I get the plan into an XML variable on the source environment, by searching through cached execution plans where the text matches.

    Then once you have your xml_showplan, you could declare that in the other environment (which must match SQL Version and Trace flags to ensure the query rendering engine is the same), set the parameters to match your original plan, add the exact select statement how AX would send it in to SQL, then pass in the XML as the query hint:

    DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'(@P1 nvarchar(5),@P2 int,@P3 bigint,@P4 int)SELECT T1.BALANCE01,T1.RECID,T2.AMOUNTCUR,T2.RECID,T2.DATAAREAID,T3.RECID,T3.VOUCHER,T3.ACCOUNTNUM,T3.APPROVED,T3.CLOSED,T3.DATAAREAID,T4.PARTY,T4.RECID,T4.DATAAREAID FROM SPECTRANS T1%' AND st.text LIKE N'%CROSS JOIN CUSTTABLE T4%');
SELECT @xml_showplan

EXEC sp_create_plan_guide 
    @name = N'Settlements', 
    @stmt = N'SELECT T1.BALANCE01,T1.RECID,T2.AMOUNTCUR,T2.RECID,T2.DATAAREAID,T3.RECID,T3.VOUCHER,T3.ACCOUNTNUM,T3.APPROVED,T3.CLOSED,T3.DATAAREAID,T4.PARTY,T4.RECID,T4.DATAAREAID FROM SPECTRANS T1 CROSS JOIN CUSTTRANSOPEN T2 CROSS JOIN CUSTTRANS T3 CROSS JOIN CUSTTABLE T4 WHERE (((T1.SPECCOMPANY=@P1) AND (T1.SPECTABLEID=@P2)) AND (T1.SPECRECID=@P3)) AND (((T1.REFCOMPANY=T2.DATAAREAID) AND (T1.REFTABLEID=@P4)) AND (T1.REFRECID=T2.RECID)) AND (T2.REFRECID=T3.RECID AND (T2.DATAAREAID = T3.DATAAREAID)) AND (T3.ACCOUNTNUM=T4.ACCOUNTNUM AND (T3.DATAAREAID = T4.DATAAREAID))', 
    @type = N'SQL',
    --@module_or_batch = NULL, 
    @params = N'@P1 nvarchar(5),@P2 int,@P3 bigint,@P4 int', 
    @hints = @xml_showplan;
GO
  • bnorma01 Profile Picture
    bnorma01 119 on at

    Yes we have the proper SQL maintenance plans.  SQL-Server is just not always the best at picking plans.  The below worked.

  • bnorma01 Profile Picture
    bnorma01 119 on at

    Thank you Vilmos this worked perfectly.   I just had to do a global replace on the database name in the plan given my production database has a different name than my test database.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans