Finance | Project Operations, Human Resources, ...

Query using Global Temporary Table

(0) Share

Report

Posted on by patrickjolliffe

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

All responses (10)

Answers (0)

Suggested answer

nmaenpaa 101,156 on at

Like (0)

Report

RE: Query using Global Temporary Table

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.
patrickjolliffe 20 on at

Like (0)

Report

RE: Query using Global Temporary Table

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')
patrickjolliffe 20 on at

Like (0)

Report

RE: Query using Global Temporary Table

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.
Suggested answer

nmaenpaa 101,156 on at

Like (0)

Report

RE: Query using Global Temporary Table

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.
patrickjolliffe 20 on at

Like (0)

Report

RE: Query using Global Temporary Table

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.
Suggested answer

nmaenpaa 101,156 on at

Like (0)

Report

RE: Query using Global Temporary Table

The statements are different all the time since the tempdb tables are created/recreated by AX a lot.
patrickjolliffe 20 on at

Like (0)

Report

RE: Query using Global Temporary Table

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.
Suggested answer

nmaenpaa 101,156 on at

Like (0)

Report

RE: Query using Global Temporary Table

AX uses TempDB heavily. Are you conserned about these two queries, or large amount of TempDB queries in general?
patrickjolliffe 20 on at

Like (0)

Report

RE: Query using Global Temporary Table

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
Suggested answer

nmaenpaa 101,156 on at

Like (0)

Report

RE: Query using Global Temporary Table

Yes, seems pretty normal. Unfortunately you didn't share full queries, only part of it but to me it looks normal.