Excessive Memory Grant

[Al_gar]

For a particular task on Qlik Compose for DW (December 2024) we need to do full loads for a table. When this task runs the generated INSERT SELECT statement for a process with description "Adding staging rows to TSTG_ENTITY_NAME_P" it's requesting an excessive amount of memory on SQL Server 2019. On another post with similar issue for a Qlik Replicate task I got a reply that "Qlik Replicate does not influence memory allocation". However, our DBAs findings were that there are some queries that needs to be optimized to avoid excessive memory grants. What can be done on Qlik Compose so that the auto generated queries are optimized and to avoid requesting Excessive Memory Grant?

These are the statistics and query plan warning message when executing the SELECT statement generated by Qlik Compose:

[Dana_Baldwin]

Hi @Al_gar 

Please lower the JVM memory settings as this directly affects the memory allocation request to the database.

Thanks,

Dana

[Al_gar]

Hi @Dana_Baldwin , I ran the SELECT statement directly from SSMS.

[Dana_Baldwin]

Hi @Al_gar 

Yes, I understand - but the JVM settings in Compose affect the memory allocation in SQL Server. What is the full version number of Compose you are running?

Thanks,

Dana

[Al_gar]

We have Qlik Compose December 2024 (2024.12.0.21). 

[Dana_Baldwin]

Hi @Al_gar 

I apologize for the delayed response. There is a service pack available for Compose 2024.12 but I don't see any memory related items in the release notes.

Under Manage Date Warehouse, select Manage, then go to the Advanced tab. What is the value specified for JVM memory? This has a direct impact on the allocated memory requested by Compose. It includes a minimum and maximum value. You might lower the max from 4096 to 3072 for example.

Thanks,

Dana

[Al_gar]

@Dana_Baldwin , We have JVM configured at 3072M:

[Dana_Baldwin · Accepted solution]

Thanks @Al_gar 

In that event, please test with 2048M as the upper limit and let us know the results.

Thanks,

Dana