The dedicated sql pool memory usage is constantly 95%-96% without any activity. It's running on DW300c service level. Is there a way to find out what exactly is utilizing this much memory?

Question

The dedicated sql pool memory usage is constantly 95%-96% without any activity. It's running on DW300c service level. Is there a way to find out what exactly is utilizing this much memory?

Raj Singh 0

The dedicated sql pool memory usage is constantly 95%-96% without any activity. It's running on DW300c service level. Is there a way to find out what exactly is utilizing this much memory? I'm trying to avoid increasing the service level without finding out what the potential reason could be. I have used the below query from Microsoft documentation to find out the memory usage.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor#monitor-memory

Krupal Bandari 580 Reputation points Microsoft External Staff Moderator

2025-06-05T06:39:47.26+00:00
Hi @Raj Singh

You're observing high memory usage (95–96%) on your Dedicated SQL Pool (DW300c), even when there’s no active workload and that’s a great observation.

Why Memory Appears High Even When Idle:

In Azure Synapse Dedicated SQL Pools, it's normal to see high memory usage during idle times. This is because Synapse:

The system proactively caches data and query plans to improve performance, while also running internal background operations and optimizations. Because of this, memory is not immediately released even when no queries are currently running.

So high memory usage alone isn’t a problem it often means the system is optimized and ready.

check this:

You can run the following queries to confirm what’s occupying memory:

Memory Usage by Component:

SELECT component AS [Component], memory_usage_percent AS [MemoryUsage%], memory_allocation_percent AS [MemoryAllocation%] FROM sys.dm_pdw_memory_distribution ORDER BY memory_usage_percent DESC;

Memory & CPU Trends Over Time:

SELECT TOP 100 snapshot_time AS [Timestamp], avg_cpu_percent AS [CPU%], avg_memory_usage_percent AS [MemoryUsage%], active_request_count AS [ActiveRequests], queued_request_count AS [QueuedRequests] FROM sys.dm_pdw_resource_stats ORDER BY snapshot_time DESC;

Any Active or Waiting Queries:

SELECT request_id AS [RequestID], status AS [Status], command AS [Command], start_time AS [StartTime], total_elapsed_time AS [ElapsedMs] FROM sys.dm_pdw_exec_requests WHERE status NOT IN ('Completed', 'Failed') ORDER BY start_time DESC;

These will show if memory is used by cached data, active queries, or something else.

You Can Do:

If memory is mostly used by cache and performance is fine no action is needed.

If there are long-running or queued queries, consider optimizing or managing concurrency.

Review your table distribution to avoid data skew, which can lead to uneven memory usage.

As a quick reset (optional), you can pause and resume the pool this clears memory but causes brief downtime.

Scaling up (e.g., to DW400c) is only needed if you're seeing performance issues caused by memory pressure.

you can refer this:

Monitor your Azure Synapse Analytics dedicated SQL pool workload using DMVs

If you found this helpful, kindly consider upvoting this response it may help other community members facing similar issues.
Nandan Hegde 35,751 Reputation points MVP Volunteer Moderator

2025-06-05T06:43:01.25+00:00

similar threads :

https://learn.microsoft.com/en-us/answers/questions/1042369/synapse-analytics-dedicated-sql-pool-memory-consum

https://learn.microsoft.com/en-us/answers/questions/1048624/synapse-dedicated-sql-pool-memory-issue

Raj Singh 0

@Krupal Bandari : I can't seem to find this table in the dedicated sql pool.

SELECT 
component AS [Component]
, memory_usage_percent AS [MemoryUsage%]
, memory_allocation_percent AS [MemoryAllocation%] 
FROM sys.dm_pdw_memory_distribution 
ORDER BY memory_usage_percent DESC;

And this,

SELECT TOP 100 snapshot_time AS [Timestamp], 
avg_cpu_percent AS [CPU%], 
avg_memory_usage_percent AS [MemoryUsage%], 
active_request_count AS [ActiveRequests], 
queued_request_count AS [QueuedRequests] 
FROM sys.dm_pdw_resource_stats 
ORDER BY snapshot_time DESC;

Nandan Hegde 35,751 Reputation points MVP Volunteer Moderator

2025-06-05T07:54:13.6533333+00:00

what error are you getting when executing these queries?
Raj Singh 0 Reputation points

2025-06-05T08:45:08.24+00:00

Invalid Object Name.
Raj Singh 0 Reputation points

2025-06-05T08:49:07.4266667+00:00

Thanks for sharing these links. I did go through them before posting my question in this forum. I didn't quite find the answer I was looking for. Since no queries or activities are running on the pool. Yet, I'm seeing a high CPU consumption, and when there is a data pipeline triggered to run, it's taking forever to complete. Hence, I started this investigation but couldn't go through further.
Krupal Bandari 580 Reputation points Microsoft External Staff Moderator

2025-06-05T08:55:44.9233333+00:00
Hi @Raj Singh
Thanks for confirming! The "Invalid object name" error typically means the query is being run from outside the Dedicated SQL Pool.

These DMVs (sys.dm_pdw_memory_distribution, sys.dm_pdw_resource_stats) are only accessible when:

Make sure you're connected to the correct Dedicated SQL Pool (formerly SQL DW), as these DMVs only work there.

These DMVs are not supported in Serverless SQL Pools, the master database, or other non-dedicated contexts.

How to check:

In Synapse Studio, go to the Data hub.

Under Workspace, expand your SQL pools and click on your dedicated pool (e.g., DW300c).

Open a new SQL script, and in the top dropdown, ensure your context is set to that dedicated SQL pool, not "Built-in" or "master".

Then run the query again.

If using SSMS or Azure Data Studio, make sure you're connected directly to the dedicated SQL pool, not the server or master DB.
If you found this helpful, kindly consider upvoting this response it may help other community members facing similar issues.
Raj Singh 0 Reputation points

2025-06-05T09:17:33.4466667+00:00

@Krupal Bandari : Yes, I'm connected to the DB in the dedicated SQL pool where I'm running other DMVs. And that is where I'm getting this error. E.g., I can run queries for this sys.dm_pdw_nodes_os_performance_counters but not the ones which you provided above. Also, I couldn't find them in this documentation - https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-reference-tsql-system-views
Krupal Bandari 580 Reputation points Microsoft External Staff Moderator

2025-06-05T09:44:29.8833333+00:00
@Raj Singh
Thank you for confirming the error.

You're absolutely right not all DMVs are available in every Synapse Dedicated SQL Pool deployment. The error you're seeing (Invalid object name) for sys.dm_pdw_memory_distribution and sys.dm_pdw_resource_stats typically means:

These specific DMVs are not supported in your current Synapse SKU or region, even if you're connected to the correct Dedicated SQL Pool.

You Can Do Instead:

Use sys.dm_pdw_nodes_os_performance_counters This DMV provides valid and useful OS-level metrics like CPU and memory usage across nodes.

Monitor with Synapse Studio (Recommended)

Go to the Monitor tab in Synapse Studio

Use the built-in dashboards for live and historical views of query performance and resource usage

Optional: Use Azure Monitor / Log Analytics For advanced monitoring, integrate Synapse with Azure Monitor to track long-term trends, set up alerts, and analyze logs.

Check for Bottlenecks

Review table distribution (avoid data skew)

Check query concurrency (monitor queue build-up or slow queries)

Note:

These DMV differences are not well-documented across all SKU versions, so your observation is valid. You are already using the correct pool and context; the issue is most likely due to backend limitations.

If you found this helpful, kindly consider upvoting this response it may help other community members facing similar issues.
Krupal Bandari 580 Reputation points Microsoft External Staff Moderator

2025-06-09T04:31:28.31+00:00

Hi @Raj Singh
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Your answer

Nandan Hegde 35,751 Reputation points MVP Volunteer Moderator

2025-06-05T06:43:01.25+00:00

similar threads :

https://learn.microsoft.com/en-us/answers/questions/1042369/synapse-analytics-dedicated-sql-pool-memory-consum

https://learn.microsoft.com/en-us/answers/questions/1048624/synapse-dedicated-sql-pool-memory-issue
Raj Singh 0 Reputation points

2025-06-05T07:45:03.69+00:00

@Krupal Bandari : I can't seem to find this table in the dedicated sql pool.

SELECT component AS [Component] , memory_usage_percent AS [MemoryUsage%] , memory_allocation_percent AS [MemoryAllocation%] FROM sys.dm_pdw_memory_distribution ORDER BY memory_usage_percent DESC;

And this,

SELECT TOP 100 snapshot_time AS [Timestamp], avg_cpu_percent AS [CPU%], avg_memory_usage_percent AS [MemoryUsage%], active_request_count AS [ActiveRequests], queued_request_count AS [QueuedRequests] FROM sys.dm_pdw_resource_stats ORDER BY snapshot_time DESC;
Nandan Hegde 35,751 Reputation points MVP Volunteer Moderator

2025-06-05T07:54:13.6533333+00:00

what error are you getting when executing these queries?
Raj Singh 0 Reputation points

2025-06-05T08:45:08.24+00:00

Invalid Object Name.
Raj Singh 0 Reputation points

2025-06-05T08:49:07.4266667+00:00

Thanks for sharing these links. I did go through them before posting my question in this forum. I didn't quite find the answer I was looking for. Since no queries or activities are running on the pool. Yet, I'm seeing a high CPU consumption, and when there is a data pipeline triggered to run, it's taking forever to complete. Hence, I started this investigation but couldn't go through further.
Krupal Bandari 580 Reputation points Microsoft External Staff Moderator

2025-06-05T08:55:44.9233333+00:00

Hi @Raj Singh
Thanks for confirming! The "Invalid object name" error typically means the query is being run from outside the Dedicated SQL Pool.

These DMVs (sys.dm_pdw_memory_distribution, sys.dm_pdw_resource_stats) are only accessible when:

Make sure you're connected to the correct Dedicated SQL Pool (formerly SQL DW), as these DMVs only work there.

These DMVs are not supported in Serverless SQL Pools, the master database, or other non-dedicated contexts.

How to check:

In Synapse Studio, go to the Data hub.

Under Workspace, expand your SQL pools and click on your dedicated pool (e.g., DW300c).

Open a new SQL script, and in the top dropdown, ensure your context is set to that dedicated SQL pool, not "Built-in" or "master".

Then run the query again.

If using SSMS or Azure Data Studio, make sure you're connected directly to the dedicated SQL pool, not the server or master DB.
If you found this helpful, kindly consider upvoting this response it may help other community members facing similar issues.
Raj Singh 0 Reputation points

2025-06-05T09:17:33.4466667+00:00

@Krupal Bandari : Yes, I'm connected to the DB in the dedicated SQL pool where I'm running other DMVs. And that is where I'm getting this error. E.g., I can run queries for this sys.dm_pdw_nodes_os_performance_counters but not the ones which you provided above. Also, I couldn't find them in this documentation - https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-reference-tsql-system-views
Krupal Bandari 580 Reputation points Microsoft External Staff Moderator

2025-06-05T09:44:29.8833333+00:00

@Raj Singh
Thank you for confirming the error.

You're absolutely right not all DMVs are available in every Synapse Dedicated SQL Pool deployment. The error you're seeing (Invalid object name) for sys.dm_pdw_memory_distribution and sys.dm_pdw_resource_stats typically means:

These specific DMVs are not supported in your current Synapse SKU or region, even if you're connected to the correct Dedicated SQL Pool.

You Can Do Instead:

Use sys.dm_pdw_nodes_os_performance_counters This DMV provides valid and useful OS-level metrics like CPU and memory usage across nodes.

Monitor with Synapse Studio (Recommended)

Go to the Monitor tab in Synapse Studio

Use the built-in dashboards for live and historical views of query performance and resource usage

Optional: Use Azure Monitor / Log Analytics For advanced monitoring, integrate Synapse with Azure Monitor to track long-term trends, set up alerts, and analyze logs.

Check for Bottlenecks

Review table distribution (avoid data skew)

Check query concurrency (monitor queue build-up or slow queries)

Note:

These DMV differences are not well-documented across all SKU versions, so your observation is valid. You are already using the correct pool and context; the issue is most likely due to backend limitations.

If you found this helpful, kindly consider upvoting this response it may help other community members facing similar issues.
Krupal Bandari 580 Reputation points Microsoft External Staff Moderator

2025-06-09T04:31:28.31+00:00

Hi @Raj Singh
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

The dedicated sql pool memory usage is constantly 95%-96% without any activity. It's running on DW300c service level. Is there a way to find out what exactly is utilizing this much memory?

Your answer