Monitor and Optimize an Analytics Solution practice questions

Increase the OneLake storage limit.

Why wrong: Storage limits have no impact on compute performance. Increasing the storage allocation will not prevent resource contention for CPU or memory during heavy query execution. You need to focus on compute capacity management, not storage capacity, to solve the problem of query performance interference between departments.

Delete all indices on the shared tables.

Why wrong: Deleting indices would hurt query performance across the board for all users. It is not an effective way to manage multi-tenant resource contention, as it does not address the core issue of resource allocation. Instead, it would likely exacerbate performance problems for all departments using the system.

Disable the Spark engine for the workspace.

Why wrong: Disabling the Spark engine would completely stop all data processing for that workspace, which is not a viable solution for managing resource contention. You need a strategy that allows both workloads to run efficiently, not one that eliminates the ability to perform necessary data analysis tasks.

The job will be allowed to run with lower priority.

Why wrong: There is no 'priority' mechanism mentioned in the policy. The effect is explicitly set to 'Deny'. The system is configured to enforce a hard limit, meaning any attempts to exceed the defined concurrency threshold will result in immediate rejection, regardless of the job's priority or importance.

The job will be rejected immediately.

Why wrong: Because the policy effect is set to 'Deny' and the maximum parallel jobs is set to 5, the 6th job violates the condition. The orchestrator will block the request, preventing it from consuming further capacity and ensuring that the defined resource limits remain strictly adhered to at all times.

The job will run, and the capacity will automatically scale.

Why wrong: Automatic scaling is not triggered by a policy rejection. Policies are intended to enforce limits, not to scale the underlying capacity. If you need more capacity, you must manually adjust the SKU or the policy definition; the system will not automatically override the 'Deny' effect.

sys.dm_db_stats_properties

Why wrong: This Dynamic Management View provides metadata about the statistics for a given table or index, including the last time they were updated. By querying this DMV, a data engineer can determine if the statistics are stale and if a manual UPDATE STATISTICS command is needed to improve query performance.

sys.dm_exec_requests

Why wrong: This view is used to monitor currently executing requests in the SQL engine. While it can help identify which queries are currently running and their resource consumption, it does not provide historical information about when statistics were last updated or the quality of the statistics used by the optimizer.

sys.dm_os_wait_stats

Why wrong: This DMV provides information about the types of waits that the SQL engine is encountering globally. It is useful for identifying system-wide bottlenecks like disk I/O or locking issues, but it is not the correct tool for verifying the freshness of statistics on a specific table or column.

Fabric Capacity Metrics app

Why wrong: The Capacity Metrics app is focused on resource consumption, such as Compute Unit usage and throttling. While it shows which items are consuming resources, it does not record administrative actions like item deletion or metadata changes, nor does it log the source IP addresses of the users.

Fabric Admin portal 'Operation ID' search

Why wrong: The Fabric Admin portal provides access to audit logs and activity tracking for the entire tenant. Administrators can search for specific operations, such as 'DeleteLakehouse', to identify the user responsible. It serves as a centralized interface for viewing the activity logs generated by the Microsoft Purview engine.

The 'Recent' list in the Fabric home page

Why wrong: The 'Recent' list is a personalized user interface element that shows items a specific user has recently accessed. It does not provide an audit trail of actions taken by other users, nor does it contain technical metadata like IP addresses or a history of deleted items.

evaluate

Why wrong: The evaluate operator is used to invoke server-side plugins, such as those for machine learning or time-series decomposition. While it is powerful for data analysis, it does not provide diagnostic information about the query execution plan or the resource consumption of the underlying KQL engine.

summarize

Why wrong: The summarize operator is used to aggregate data by calculating counts, sums, or other statistics over groups of rows. It is a fundamental part of data transformation in KQL but provides no diagnostic metadata regarding the efficiency of the query execution or the resources used during processing.

render

Why wrong: The render operator is used to visualize the output of a query as a chart or graph within the Kusto web UI or a Fabric dashboard. It affects how the data is displayed to the user but does not provide any information about the backend performance or execution plan.

Increase the Fabric Capacity SKU size.

Why wrong: While a larger SKU provides more compute power, mirroring issues are usually related to connectivity, source log availability, or schema incompatibilities rather than a lack of Compute Units. Scaling the SKU is an expensive and likely ineffective first step for troubleshooting missing data synchronization.

Run a 'VACUUM' command on the mirrored table.

Why wrong: The VACUUM command is used for cleaning up old, unreferenced files in a Delta table to save storage. It does not affect the mirroring logic or help in synchronizing new data from a source system. Running it will not resolve issues where new records are missing.

Implement a broadcast join if one of the tables is small enough.

Why wrong: A broadcast join sends a copy of the smaller table to every executor, eliminating the need to shuffle the larger table. This is highly effective when one table fits in memory, as it transforms a distributed join into a local one, significantly reducing network I/O and execution time.

Convert the notebook to use Python instead of Scala.

Why wrong: The choice between Python (PySpark) and Scala generally has a minimal impact on the performance of the Spark engine's shuffling mechanism. Shuffling is a core Spark architectural process handled by the JVM; changing the high-level API language will not optimize the data redistribution logic.

Disable 'Dynamic Allocation' in the Spark session settings.

Why wrong: Dynamic Allocation allows Spark to release executors when they are not in use, which helps with resource efficiency. Disabling it does not reduce the amount of data being shuffled; it only ensures that a fixed number of executors remain active throughout the duration of the job.

Increase the number of rows per transaction.

Why wrong: Increasing the number of rows per transaction is generally associated with write-heavy workloads and transactional processing. In a data warehouse environment, this does not help read performance and can actually lead to lock contention, slowing down read queries during concurrent operations.

Use row-based storage exclusively.

Why wrong: Row-based storage is optimized for transactional systems (OLTP) where individual records are frequently updated or retrieved. It is inefficient for analytical workloads because it requires reading entire rows even when only a few columns are needed, resulting in excessive I/O for analytical queries.

Disable data compression.

Why wrong: Disabling compression increases the amount of data read from disk, which severely degrades performance in an analytical environment. Compression is essential for reducing I/O, and since read-heavy workloads rely on efficient scanning, keeping data compressed is vital for keeping queries running quickly.

Increase the number of compute nodes.

Why wrong: While adding compute power can sometimes mask poor query performance, it does not address the underlying issue of an inefficient join algorithm chosen by the optimizer. Improving the query plan through statistics is a more cost-effective and sustainable way to optimize performance.

Disable the query optimizer.

Why wrong: The query optimizer is fundamental to the execution of SQL statements. It cannot be disabled, and even if it could, manually managing execution plans is highly complex, error-prone, and unsustainable, especially given that Fabric handles query optimization automatically based on table metadata.

Rewrite the query using a cursor.

Why wrong: Cursors are row-based operations that are notoriously slow in analytical data warehouses. They perform poorly compared to set-based operations and should be avoided at all costs. Using a cursor would likely make the query performance even worse than the current broadcast nested loop join.

Immediately scale up the capacity SKU.

Why wrong: Scaling up is the most expensive path and should be considered only after you have exhausted optimization opportunities. Without identifying the source of the consumption, you might simply be adding more capacity to support inefficient reports that will eventually overload the new, larger capacity as well.

Clear the browser cache for all users.

Why wrong: Browser cache is client-side and has no impact on the server-side capacity consumption in Microsoft Fabric. Clearing it will not resolve capacity throttling issues, as the server-side calculations and data processing remain unchanged regardless of the state of the user's local browser cache.

Delete all unused reports in the workspace.

Why wrong: While cleaning up unused reports is good practice, it is not the most effective first step for diagnosing a performance issue. You need to identify which items are currently consuming the capacity, as the most resource-intensive items might be active reports, not unused ones.

The Timepoint Detail drill-through

Why wrong: Interactive operations represent tasks triggered by users, such as Power BI report interactions or SQL queries. These operations are prioritized and smoothed over a shorter window compared to background tasks. Monitoring these ensures that the end-user experience remains responsive even when the capacity is under significant load from other sources.

The Storage Consumption report

Why wrong: Storage consumption metrics track the volume of data stored in OneLake across different workspaces and items. While storage costs are important for overall budget management, they do not impact the real-time compute throttling or the performance of active Spark jobs and SQL queries that are constrained by Capacity Unit limits.

The Workspace Role Assignment log

Why wrong: Role assignments determine who can access or modify items within a workspace but have no bearing on the technical performance or throttling of the capacity. Security logs are useful for auditing access but cannot provide technical data regarding CU consumption or the interactive versus background task load on the system.

Increase the 'Max Intermediate Row Set' in the capacity settings.

Why wrong: The 'Max Intermediate Row Set' setting controls the number of rows that can be processed during a query but does not directly influence whether a model can stay in DirectLake mode. DirectLake is primarily constrained by the total memory available to the Analysis Services engine in the capacity.

Change the semantic model to 'Import' mode.

Why wrong: While 'Import' mode would solve the DirectQuery fallback issue, it removes the benefits of DirectLake, such as near real-time data access and avoiding data duplication. It is a workaround rather than an optimization of the DirectLake architecture that Fabric is designed to support.

Reduce the number of measures in the semantic model.

Why wrong: Measures are calculated at query time and have a minimal impact on the initial loading of data into DirectLake. The primary factor for DirectLake memory consumption is the size and number of columns being loaded from the Parquet files in OneLake, not the complexity of the DAX measures themselves.

ALTER TABLE REFRESH

Why wrong: The REFRESH command is typically used to update metadata caches or resolve schema drift issues. It does not perform physical compaction of data files or reorganize the underlying Parquet files to improve read performance for downstream analytics queries.

VACUUM

Why wrong: The VACUUM command is intended to remove stale data files that are no longer referenced by the Delta log and are older than the retention threshold. While it manages storage costs, it does not consolidate small files into larger, optimized files.

ANALYZE TABLE

Why wrong: The ANALYZE command collects table statistics, such as column distributions and counts, which the query optimizer uses to generate efficient execution plans. It does not physically rewrite or consolidate the actual data files stored in the underlying Delta storage.

Data Pipeline

Why wrong: A Data Pipeline is an orchestration tool used to move and transform data. While it performs the work, it is not the visualization tool used to map the flow and dependencies of data objects across the workspace or tenant environment.

OneLake

Why wrong: OneLake is the unified storage foundation of Microsoft Fabric. It serves as the data lake for all items, but it does not provide the visual mapping or dependency tracking capabilities that allow users to view the end-to-end lineage of their analytical solutions.

Notebook

Why wrong: Notebooks are used for data engineering and data science tasks like processing and transformation using Spark or Python. They are the engine for executing code, not the monitoring tool that displays the visual flow and connectivity of data artifacts.

The Fabric Capacity Metrics App

Why wrong: The Capacity Metrics App focuses on compute utilization, throttling, and SKU-level performance. It does not provide granular execution logs for individual Spark notebooks, such as specific job status, cell execution times, or detailed run histories for a specific item over a 30-day window.

Microsoft Purview Compliance Portal

Why wrong: The Purview Compliance Portal focuses on security, data governance, and auditing regulatory compliance. It does not provide the technical performance metrics needed to analyze the execution history or duration of Spark notebook jobs running within a specific workspace.

The OneLake File Explorer

Why wrong: OneLake File Explorer is a client-side tool used to manage and browse files within your OneLake storage from your local machine. It does not track execution history, notebook performance, or job duration statistics for Spark workloads processed in the Fabric cloud environment.

Increase the number of Spark executors indefinitely

Why wrong: Adding executors increases parallelism but eventually hits the point of diminishing returns. Excessive executors can lead to increased overhead for task scheduling and communication latency. Resource allocation should be balanced against the specific workload requirements to maintain cost-effectiveness and optimal cluster utilization.

Avoid caching data in memory

Why wrong: Caching intermediate dataframes in memory is a key optimization for iterative Spark jobs. Re-calculating the same dataset repeatedly is inefficient and CPU-intensive. Proper use of caching ensures that subsequent operations access data directly from memory rather than re-reading or re-computing from the lake.

It restricts total storage capacity usage

Why wrong: The policy specifically monitors data transfer, not total storage capacity. Storage capacity is governed by the Fabric SKU limits and workspace settings, whereas this policy is focused on the volume of data moving out of the resource group, typically representing egress traffic.

It limits the number of concurrent connections

Why wrong: The policy is defined based on the volume of data (100GB), not the number of concurrent connections. Limiting concurrent connections is typically handled by capacity settings or throttling policies, whereas this JSON policy targets the total volume of transferred data.

It disables the Fabric capacity entirely

Why wrong: The policy does not disable the entire capacity; it merely denies the specific operations that trigger the egress threshold. The rest of the Fabric workspace remains functional, and users can continue to perform tasks as long as they do not violate the transfer limit.

Run all pipelines simultaneously at 00:00

Why wrong: Running all pipelines at the same time creates an immediate, massive spike in compute demand. This causes resource contention, potential throttling, and significantly slower execution times for all jobs involved, as they compete for the same limited pool of capacity resources simultaneously.

Increase the refresh frequency for all items

Why wrong: Increasing refresh frequency places a higher continuous load on the capacity. This leads to more frequent resource consumption and increases the likelihood of hitting capacity limits, ultimately degrading the overall performance of the workspace and increasing the risk of service throttling.

Limit access to the workspace for users

Why wrong: Restricting user access does not address the underlying compute demand generated by automated data refreshes. Even if users are blocked, the scheduled jobs will still run and consume the same amount of capacity, providing no relief to the performance bottleneck.

The Microsoft Cost Management portal

Why wrong: While Cost Management provides high-level financial data and overall cloud spend, it lacks the technical granularity needed to link costs to specific Fabric artifacts or workspace activities. It cannot show the item-level breakdown that the Capacity Metrics app provides for Fabric specifically.

Azure Advisor

Why wrong: Azure Advisor provides general recommendations for optimizing costs and performance across Azure resources. It is not designed to provide the specific, item-level analytics for Fabric workspace consumption that are required to monitor and attribute costs within the Fabric environment itself.

The Fabric Admin Portal

Why wrong: The Admin Portal is used for tenant-wide settings, user access, and feature governance. While it shows some high-level subscription information, it does not provide the detailed usage logs and granular consumption reports required to analyze billable capacity performance at the workspace level.

The number of partitions in the table metadata.

Why wrong: While partitioning is important, an excessive number of partitions can lead to large metadata overhead, but it is not the primary cause of slowness for small tables. File count distribution is a more common performance killer when files are not optimally sized for the Spark compute engine.

The CPU utilization of the Spark pool.

Why wrong: CPU utilization is a lagging indicator of performance issues rather than a root cause for slow table scans. If the Spark pool is healthy but queries are still slow, the bottleneck is almost certainly related to data organization, such as file fragmentation, rather than raw compute availability.

The storage account throughput limits.

Why wrong: Storage throughput limits are rarely the bottleneck for small data tables in Fabric. Unless you have reached extreme IOPS concurrency limits across the entire workspace, small tables should easily reside within the default throughput capacity provided by the underlying ADLS Gen2 storage architecture used by Fabric.