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?

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.

@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;

what error are you getting when executing these queries?

Invalid Object Name.

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.