Hi Again, You guys were so helpful with my last question, here I am again
Do excuse me, I 'm just a DBA so understand I don't have much (any) knowledge on application side.
We upgraded our Database from SQL Server 2014 to 2016 and have enabled query store, but it keeps filling up.
Investigation shows almost all queries are of format as below:
(@P1 int,@P2 int,@P3 nvarchar(11),@P4 datetime2(7),@P5 datetime2(7),@P6 datetime2(7),@P7 nvarchar(21),@P8 nvarchar(21),@P9 nvarchar(5))SELECT T1.ITEMRELATION,T1.ACCOUNTRELATION,T1.AMOUNT,T1.INVENTDIMID,T1.FROMDATE,T1.TODATE,T1.ITEMCODE,T1.ACCOUNTCODE,T1.UNITID,T1.RECID,T1.CURRENCY,T1.DATAAREAID FROM PRICEDISCTABLE T1 WHERE (((PARTITION=5637144576) AND (DATAAREAID IN (SELECT DATAAREAID FROM ##Tmp75697757FA474E828188E79A5D23CE16) )) AND (((((((ITEMCODE=@P1) AND (ACCOUNTCODE=@P2)) AND NOT ((UNITID=@P3))) AND (FROMDATE<=@P4)) AND ((TODATE>=@P5) AND (TODATE<=@P6))) AND (ACCOUNTRELATION=@P7)) AND ((ITEMRELATION=@P8) AND (DATAAREAID=@P9)))) ORDER BY T1.RELATION,T1.ACCOUNTCODE,T1.ACCOUNTRELATION,T1.CURRENCY,T1.ITEMCODE,T1.ITEMRELATION,T1.UNITID,T1.QUANTITYAMOUNTFROM,T1.FROMDATE,T1.AGREEMENTHEADEREXT_RU
(@P1 int,@P2 int,@P3 nvarchar(11),@P4 datetime2(7),@P5 datetime2(7),@P6 datetime2(7),@P7 nvarchar(21),@P8 nvarchar(21),@P9 nvarchar(5))SELECT T1.ITEMRELATION,T1.ACCOUNTRELATION,T1.AMOUNT,T1.INVENTDIMID,T1.FROMDATE,T1.TODATE,T1.ITEMCODE,T1.ACCOUNTCODE,T1.UNITID,T1.RECID,T1.CURRENCY,T1.DATAAREAID FROM PRICEDISCTABLE T1 WHERE (((PARTITION=5637144576) AND (DATAAREAID IN (SELECT DATAAREAID FROM ##Tmp6A9EE1F985E947B2A2C26885B3F5B164) )) AND (((((((ITEMCODE=@P1) AND (ACCOUNTCODE=@P2)) AND NOT ((UNITID=@P3))) AND (FROMDATE<=@P4)) AND ((TODATE>=@P5) AND (TODATE<=@P6))) AND (ACCOUNTRELATION=@P7)) AND ((ITEMRELATION=@P8) AND (DATAAREAID=@P9)))) ORDER BY T1.RELATION,T1.ACCOUNTCODE,T1.ACCOUNTRELATION,T1.CURRENCY,T1.ITEMCODE,T1.ITEMRELATION,T1.UNITID,T1.QUANTITYAMOUNTFROM,T1.FROMDATE,T1.AGREEMENTHEADEREXT_RU
Notice the global temporary table for the DATAAREAID is different each time.
Is this standard Dynamics AX process or query, or do you think it might be some customization? If the latter where might we look to change it?
Thanks in advvance
Not sure but most likely it's related to using a (standard) feature called "Virtual companies". That allows you to share some data between many legal entities.
Note I've been doing some tracing, beleive that this is the sql statement that creates/populates the GTT
SELECT D.ID AS DATAAREAID, VIRTUALDATAAREA = CASE WHEN V.VIRTUALDATAAREA IS NULL THEN D.ID ELSE V.VIRTUALDATAAREA END INTO ##Tmp0BB4E28BF15941CFB9A10BA48DDC7F0D FROM DATAAREA D LEFT JOIN VIRTUALDATAAREALIST V ON D.ID = V.ID WHERE D.ID IN(N'7110')
Yep, I'm reaching out and trying to get this resolved as you suggest, just wondered if someone on this forum might also have some clue. If not then no worries.
I suggest you talk with your AX team (or partner) about this. That way you can together pinpoint the exact process that does this query, and they can tell you if there's some customization involved or not.
Yes, I understand that, but the question is whether for this specific query it is the way it has to be? The problem really seems isolated to the specific query against PRICEDISCTABLE (only difference being the name of the GTT). So I'm trying to understand this is a customization, or standard process, and whether any way to adjust to just use the DATAAREAID from a parameter rather than creating and populating GTT and joining to that each time.
The statements are different all the time since the tempdb tables are created/recreated by AX a lot.
I have no concern about the queries themselves (well I might be if I started looking but let's not get side-tracked).
The problem is that because for each execution the SQL statement is slightly different (GTT name is different), it means that it is filling up SQL Server Query Store, one record for each execution.
These are 99.9% of Query Store contents, and it's filling query store up (I suspect) faster than the purging mechanism is working.
AX uses TempDB heavily. Are you conserned about these two queries, or large amount of TempDB queries in general?
No, that's the full query :)
So the "AND (DATAAREAID IN (SELECT DATAAREAID FROM ##Tmp75697757FA474E828188E79A5D23CE16)" is bit is just the way that Dynamics AX handles the fact there may be one or more DATAAREAID's to query? Ie it creates a Global Temporary Table, populates it, and then joins to it.
It's a right pain because all these queries are swamping our Query Store
Yes, seems pretty normal. Unfortunately you didn't share full queries, only part of it but to me it looks normal.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,867 Super User 2024 Season 2
Martin Dráb 229,173 Most Valuable Professional
nmaenpaa 101,156