2

My query performs bad when I run it without OPTION (RECOMPILE). Without recompile it take 3-4 minutes to run and with recompile it takes about 15-20sec.

I can’t change the query, I have updated all statistics and rebuilt all indexes. There are only 1 index suggestions with a improvement of 9,4%.

I have tried:

-- clear all plans in cache
DBCC FREEPROCCACHE

-- Clear Buffer pool
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
GO

What more can I do? I can’t edit the query so OPTION (RECOMPILE) is not a valid solution for me. The query might vary some from time to time so "plan guide" won’t work?

Please note, this isn't the actual query. The actual query is executed by Dynamics AX, so is using an api_cursor. I pried this from the sp_cursorprepare and filled in the parameters by hand.

DECLARE @p1 AS BIGINT = 5637144576
DECLARE @p2 AS NVARCHAR(32) = N'1003'
DECLARE @p3 AS NVARCHAR(32) = N'posp%'
DECLARE @p4 AS BIGINT = 5637144576
DECLARE @p5 AS NVARCHAR(32) = N'sv'
DECLARE @p6 AS NVARCHAR(32) = N'posp'
DECLARE @p7 AS BIGINT = 5637144576
DECLARE @p8 AS NVARCHAR(32) = N'1003'
DECLARE @p9 AS NVARCHAR(32) = N'posp'
DECLARE @p10 AS BIGINT = 5637144576
DECLARE @p11 AS NVARCHAR(32) = N'posp'
DECLARE @p12 AS BIGINT = 5637144576
DECLARE @p13 AS NVARCHAR(32) = N'1003'
DECLARE @p14 AS BIGINT = 5637144576
DECLARE @p15 AS NVARCHAR(32) = N''
DECLARE @p16 AS NVARCHAR(32) = N'sv'
DECLARE @p17 AS INT = 0
DECLARE @p18 AS INT = 0
DECLARE @p19 AS INT = 0
DECLARE @p20 AS INT = 0
DECLARE @p21 AS BIGINT = 5637144576
DECLARE @p22 AS NVARCHAR(32) = N'1003'
DECLARE @p23 AS INT = 2
DECLARE @p24 AS BIGINT = 5637144576
DECLARE @p25 AS NVARCHAR(32) = N'1003'
DECLARE @p26 AS BIGINT = 5637144576
DECLARE @p27 AS INT = 1
DECLARE @p28 AS INT = 101


select @p1, @p2, @p5, @p6, @p7

SELECT * 
FROM   ( 
                       SELECT          Row_number() OVER(ORDER BY t2.NAME,t2.itemid) AS rownumber,
                                       t2.itemid                                     AS f1, 
                                       t2.product                                    AS f2, 
                                       t2.NAME                                       AS f3, 
                                       t2.recid                                      AS f4, 
                                       t3.unitid                                     AS f5, 
                                       t3.recid                                      AS f6, 
                                       t4.recid                                      AS f7, 
                                       t5.recid                                      AS f8, 
                                       t6.recid                                      AS f9, 
                                       t7.recid                                      AS f10, 
                                       t7.instancerelationtype                       AS f11 
                       FROM            retailitemname T2 
                       CROSS JOIN      inventtablemodule T3 
                       LEFT OUTER JOIN inventtable T4 
                       ON              ((( 
                                                                                       t4.partition=@P1)
                                                       AND             ( 
                                                                                       t4.dataareaid=@P2))
                                       AND             (( 
                                                                                       t4.itemid LIKE @P3 ESCAPE '\' )
                                                       AND             ( 
                                                                                       t2.itemid=t4.itemid)))
                       LEFT OUTER JOIN ecoresproducttranslation t5 
                       ON              (( 
                                                                       t5.partition=@P4) 
                                       AND             ((( 
                                                                                                       t5.languageid=@P5)
                                                                       AND             ( 
                                                                                                       FREETEXT(t5.NAME,@P6)))
                                                       AND             ( 
                                                                                       t2.product=t5.product)))
                       LEFT OUTER JOIN inventtable t6 
                       ON              ((( 
                                                                                       t6.partition=@P7)
                                                       AND             ( 
                                                                                       t6.dataareaid=@P8))
                                       AND             (( 
                                                                                       FREETEXT(t6.namealias,@P9))
                                                       AND             ( 
                                                                                       t2.itemid=t6.itemid)))
                       LEFT OUTER JOIN ecoresproduct t7 
                       ON              (( 
                                                                       t7.partition=@P10) 
                                       AND             (( 
                                                                                       FREETEXT(t7.searchname,@P11))
                                                       AND             ( 
                                                                                       t2.product=t7.recid)))
                       WHERE           (((( 
                                                                                                       t2.partition=@P12)
                                                                       AND             ( 
                                                                                                       t2.dataareaid=@P13))
                                                       AND             (( 
                                                                                                       t2.partition#2=@P14)
                                                                       OR              ( 
                                                                                                       t2.partition#2 IS NULL)))
                                       AND             ((( 
                                                                                                       t2.languageid=@P15)
                                                                       OR              ( 
                                                                                                       t2.languageid=@P16))
                                                       AND             (((( 
                                                                                                                                       t4.recid<>@P17)
                                                                                                       OR              (
                                                                                                                                       t5.recid<>@P18))
                                                                                       OR              (
                                                                                                                       t6.recid<>@P19))
                                                                       OR              ( 
                                                                                                       t7.recid<>@P20))))
                       AND             ((( 
                                                                                       t3.partition=@P21)
                                                       AND             ( 
                                                                                       t3.dataareaid=@P22))
                                       AND             (( 
                                                                                       t3.moduletype=@P23)
                                                       AND             ( 
                                                                                       t2.itemid=t3.itemid)))
                       AND             EXISTS 
                                       ( 
                                              SELECT 'x' 
                                              FROM   retailitemcategory t8 
                                              WHERE  (((( 
                                                                                 t8.partition=@P24)
                                                                   AND    ( 
                                                                                 t8.dataareaid=@P25))
                                                            AND    ( 
                                                                          t8.partition#2=@P26))
                                                     AND    ( 
                                                                   t2.itemid=t8.itemid))) )t1
WHERE  (( 
                     t1.rownumber>=@P27) 
       AND    ( 
                     t1.rownumber<@P28))
                     OPTION (RECOMPILE)

Plans

Again: Please note these are plans from the query I created, not necessarily the ones used when the application is actually using a cursor.

SQL Version:

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
Feb 20 2014 20:04:26 
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

MAXDOP is set to 1 because it's an AX 2012 (R3 CU10) database. We don't want any big reports or jobs to block all processors. Didn't know you could see more index suggestions in the plan. But the slow one only has one suggestion and the fast one has 2.MAXDOP 0 is faster but still not as fast as recompile.

The AX query runs when you through the real-time service search for a product in AX that doesn't exist in the Channel Database. This search is included in AX 2012 R3 retail. No customization.

The intention is to optimize the process at the SQL Server. The query does exactly what it should do but it's too slow without the recompile. It is using the wrong query plan. If it uses the right query plan it's fast. So there should be no need to do any changes in AX.

optimize for ad hoc workloads is activated.

5
+25

I'm not sure about how the retail component works, but since you say you captured it from the cursorprepare and rewrote it, you seem to imply it was sent from the AOS anyway.

One thing I would suggest you do is see if it helps to execute

UPDATE SYSGLOBALCONFIGURATION SET [VALUE] = 1 WHERE NAME IN ( ‘DATAAREAIDLITERAL’,’PARTITIONLITERAL’) 

and then restart your middle tier servers.

Typically in an AX environment (as you explained in the comments) there are big discrepancies in the data distribution between companies, and reusing the plan for a company containing 0 records in retail for a query executed in a company containing tens of thousands of records is going to run into serious parameter sniffing issues.

Please have a look at SQL Server Parameter Sniffing with Dynamics AX, just plain evil for more details.

Please note I'm unsure if this setting would affect the retail component, but I expect it will since you stated in comments that the query is actually executed by the AOS, but the setting could be helpful for your Dynamics installation as a whole.

You state that there shouldn't be any reason to change anything in AX but there could very well be, since you also state the query runs fine when recompiled.

Relevant points from the linked article:

  • Does running DBCC FREEPROCCACHE seem to correct the performance issues?
  • Do you have more than one Company and is the data distribution between those companies VERY uneven?

If that doesn't help you should look in the AX code where the query is coming from. Please read through the instructions in How to Monitor for Long Running Queries in AX for instructions on how to find the call stack where the query originates. Once you know where the query is in code you could (or have the developers) add the forceliterals keyword (again documentation) to disable parameters and fill in the actual values to have the execution plan compiled for every combination of parameters. This should resemble OPTION (RECOMPILE) fairly closely, except it won't recompile for exact same values.

With regards to your MAXDOP remark, this wouldn't make a lot of difference since AX creates a FAST_FORWARD cursor which don't support parallelism as Paul white explained in this answer to a question of mine.

  • Thanks Tom, Yes its the AOS that runs the query. So i will look into this. DBCC FREEPROCCACHE doesnt help, already tried that. Yes, there are 4 companies in the database and its "very" uneven distributed. Its only one company that have data in the related tables. – Daniel Björk Nov 28 '16 at 19:34
  • @DanielBjörk please try filling in the dataareaid string as a literal anyway – Tom V Nov 28 '16 at 20:41
  • 2
    @DanielBjörk now that the question has been clarified I tried to make my answer more complete, please have a look at what I added – Tom V Nov 29 '16 at 18:50
  • Parameter sniffing is an issue found in stored procedures. Without the RECOMPILE hint SQL Server creates the query plan before knowing what the values of the local variables are. Based on the code provided by Daniel it's more likely that he's running into that issue (and getting the same bad plan each time despite clearing the cache) as opposed to parameter sniffing. – Joe Obbish Nov 30 '16 at 4:31
  • @JoeObbish I agree, but the query isn't the actual query the application is executing, but some recontruction of the actual cursor. He said the recompile fixes his version, and my suggested application change results in a query that isn't parametrized, which could solve his problem with not being able to add the hint (in his actual situation) – Tom V Nov 30 '16 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.