You have an Azure data solution that contains an enterprise data warehouse in Azure Synapse Analytics named DW1.
Several users execute adhoc queries to DW1 concurrently.
You regularly perform automated data loads to DW1.
You need to ensure that the automated data loads have enough memory available to complete quickly and successfully when the adhoc queries run.
What should you do?
Click on the arrows to vote for the correct answer
A. B. C. D.B
To ensure the loading user has enough memory to achieve maximum compression rates, use loading users that are a member of a medium or large resource class.
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/guidance-for-loading-dataTo ensure that automated data loads to the enterprise data warehouse in Azure Synapse Analytics named DW1 have enough memory available to complete quickly and successfully when concurrent adhoc queries run, the best approach is to assign a larger resource class to the automated data load queries. Option B is the correct answer.
When multiple users execute adhoc queries concurrently, it could cause contention for resources, resulting in performance degradation of automated data load queries. A larger resource class assigned to automated data load queries would ensure that they have enough memory available to complete quickly and successfully when concurrent adhoc queries run.
Hash distributing the large fact tables in DW1 before performing the automated data loads (option A) would not help with ensuring memory availability during automated data loads when adhoc queries run concurrently. Hash distribution is used to distribute data evenly across multiple nodes in a distributed system, and it can help improve query performance by reducing data movement during query execution.
Creating sampled statistics for every column in each table of DW1 (option C) would not directly help with ensuring memory availability during automated data loads when adhoc queries run concurrently. However, statistics on column values can help the query optimizer generate more efficient execution plans, which can help with overall query performance.
Assigning a smaller resource class to the automated data load queries (option D) would not help with ensuring memory availability during automated data loads when adhoc queries run concurrently. A smaller resource class would limit the resources available for automated data load queries, resulting in longer execution times and increased contention for resources when adhoc queries run concurrently.