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
- Slow Execution Plan (3-4min)
- Fast Execution Plan (15-20sec)
MAXDOP 0(1min)
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.
Best Answer
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
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:
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
MAXDOPremark, this wouldn't make a lot of difference since AX creates aFAST_FORWARDcursor which don't support parallelism as Paul white explained in this answer to a question of mine.