Result Set Caching Analysis for Azure Synapse Analytics

When result set caching is enabled in Azure Synapse Analytics dedicated SQL pools, certain query types are excluded from caching based on Microsoft's documented limitations. The key determining factors are:

Eligible Queries:

• Query 1: SELECT COUNT(*) FROM dbo.FactInternetSales - This query is eligible for caching because it uses deterministic functions and doesn't include any non-cacheable elements.
• Query 2: SELECT COUNT(*) FROM dbo.FactInternetSales OPTION (LABEL = 'MyQuery') - The OPTION clause with LABEL doesn't affect cache eligibility. This remains a deterministic query that can be cached.

Ineligible Queries:

• Query 3: SELECT COUNT(*) FROM dbo.FactInternetSales WHERE OrderDateKey = '20210101' - This query contains a WHERE clause with a specific date filter. While deterministic, queries with WHERE clauses that filter specific values may not be cached due to the potential for numerous similar queries with different filter values, which could overwhelm the cache.
• Query 4: SELECT COUNT(*) FROM dbo.FactInternetSales WITH (NOLOCK) - The WITH (NOLOCK) hint indicates dirty reads and non-deterministic behavior, making it ineligible for caching.
• Query 5: SELECT COUNT(*) FROM dbo.FactInternetSales WHERE CustomerKey = 12345 - Similar to Query 3, this contains a specific filter condition that prevents caching.

Key Cache Eligibility Rules:

• Queries must be deterministic (same inputs always produce same outputs)
• No non-deterministic functions (GETDATE(), NEWID(), etc.)
• No user-defined functions
• No row-level security implementations
• Result size must be under 10GB
• Row size must be under 64KB

Only Query 1 and Query 2 meet all the criteria for result set caching in Azure Synapse Analytics dedicated SQL pools.