topic Re: Excessive Memory Grant in Qlik Compose

Excessive Memory Grant

Al_gar — Thu, 19 Jun 2025 16:54:53 GMT

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:

Re: Excessive Memory Grant

Dana_Baldwin — Thu, 19 Jun 2025 17:28:36 GMT

Hi @Al_gar

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

Thanks,

Dana

Re: Excessive Memory Grant

Al_gar — Thu, 19 Jun 2025 17:57:54 GMT

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

Re: Excessive Memory Grant

Dana_Baldwin — Thu, 19 Jun 2025 18:05:12 GMT

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

Re: Excessive Memory Grant

Al_gar — Fri, 20 Jun 2025 17:01:17 GMT

We have Qlik Compose December 2024 (2024.12.0.21).

Re: Excessive Memory Grant

Dana_Baldwin — Mon, 07 Jul 2025 20:21:32 GMT

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

Re: Excessive Memory Grant

Al_gar — Tue, 08 Jul 2025 20:59:50 GMT

@Dana_Baldwin , We have JVM configured at 3072M:

Re: Excessive Memory Grant

Dana_Baldwin — Tue, 08 Jul 2025 21:02:52 GMT

Thanks @Al_gar

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

Thanks,

Dana