Efficiently monitor SQL Database usage and consumption in Microsoft Fabric by using capacity metrics app
In November 2024, Microsoft Fabric unveiled the preview of SQL Database in Fabric at Ignite – an exciting milestone that marked a new chapter for SQL Server, Azure SQL, and Microsoft Fabric, as operational and analytical workloads were brought together in one unified platform. Learn more in the announcement: Announcing SQL database in Microsoft Fabric (Preview). Now, we’ll delve into how the capacity metrics app can be used for monitoring usage and consumption of SQL databases in Fabric. We’ll also explore how to use Azure’s existing cost analysis features to correlate usage with spending.
Why monitoring usage and consumption matters
For businesses leveraging SQL database in Fabric, understanding how resources are being used is crucial for maintaining optimal performance and managing costs effectively. Whether you’re a data engineer, an IT administrator, or a developer, tracking SQL database usage allows you to monitor consumption patterns, optimize resource allocation, and ensure that databases are running efficiently.
By monitoring usage through capacity metrics app for SQL Database, Microsoft Fabric now offers deeper insights into both compute and storage usage across your SQL databases. This feature is built to provide you with granular reporting capabilities to track SQL Database consumption and optimize for cost, performance, and scalability.
Capacity metrics app for SQL database in Fabric
The Microsoft Fabric Capacity Metrics app provides visibility into capacity usage for all Fabric workloads in one place. Administrators can use the app to monitor capacity, the performance of workloads, and their usage compared to purchased capacity.
Key Features
1. Compute usage insights
- Monitor compute consumption: You can track compute usage for your SQL databases over time. This feature helps you understand how much processing power is being consumed, whether by active queries or background processes.
- Identify over-provisioning or under-utilization: By analyzing compute consumption patterns, you can determine if your databases are over-provisioned (leading to unnecessary costs) or under-utilized (indicating opportunities to downsize).
- Compute usage breakdown: The reporting interface provides a breakdown of usage per database, helping you pinpoint inefficiencies that may require optimization or scaling adjustments.
- Operation categories: You can analyze universal compute capacity usage by workload category, across the tenant. Usage is tracked by total Capacity Unit Seconds (CUs).By hovering on the individual artifacts in the items table you get the table below that shows aggregated usage across the last 14 days. SQL usage is the charge for all user-generated and system-generated T-SQL statements within a Database. The billing type field is used to determine whether the workload is in preview mode or billable.
2. Storage usage insights
- Track storage usage: You can gain insights into how much storage your SQL databases are consuming. After selecting the capacity, adjust the date range to align with the storage emitted during a billing cycle. The experience slider helps you filter on the workload experience.
- Storage trends over time: Understanding growth rate of your storage requirements can indicate a need for database tuning or scaling. These tiles give you a quick overview of the workspaces consuming storage on your capacity, and you can view both the current amount of storage consumed and the hourly average sent to meters for billing. Current storage metrics align with a graph on the left to show average storage at a daily grain or an hourly grain if you drill in.
- Optimize storage costs: You can also see how storage consumption correlates with your costs. This allows for the identification of opportunities to optimize your storage allocation, such as archiving old data. The billable storage is the sum of fractional storage emitted to the meters for billing. This is computed by taking the average storage used by the capacity every hour and dividing by the total number of hours in a month.
3. Resource utilization trends
- Comprehensive time-based trends: The capacity metrics app experience for SQL database in Fabric allows you to observe resource usage trends over days, weeks, and months. This is crucial for identifying periods of peak demand or idle times where resources could be optimized.
- Granular time windows: You can drill into specific time periods to understand the exact moments when resource utilization spikes, helping you make proactive adjustments to avoid performance degradation or unnecessary cost increases.
- Timepoint explore graph: This graph in the Microsoft Fabric capacity metrics app shows utilization of resources compared to capacity purchased. 100% of utilization represents the full throughput of a capacity SKU and is shared by all Fabric workloads. This is represented by the yellow dotted line. Selecting a specific timepoint in the graph enables the Explore button, which opens a detailed drill through page. In general, similar to Power BI, operations are classified either as interactive or background and denoted by color. Most operations in SQL database category are reported as interactive with 5 mins smoothening of activity.
- Timepoint drill through graph: This table in the Microsoft Fabric Capacity Metrics app provides a detailed view of utilization for SQL database in Fabric at specific timepoints. The amount of capacity provided by the given SKU per 30-second period is shown along with the breakdown of interactive and background operations. The interactive operations table represents the list of operations that were executed at that timepoint and are driven directly by user activity.
4. Cost efficiency monitoring
- Cost vs. resource usage: Beyond just tracking compute and storage, capacity metrics app also allows you to see how resource consumption is affecting your overall costs for your SQL database. This gives you the ability to forecast and plan for scaling needs while managing operational expenses.
How can I correlate costs to usage consumption I see on capacity metrics app?
Under cost analysis side of cost management in Azure, you can select the capacities you are monitoring in metrics app and track the cumulative and daily spend of the resources under these capacities. In the example below the cumulative cost is $48.05, with an average daily spend of $1.68. This daily spend is relatively stable, suggesting that the usage is consistent. However, looking at the chart, there’s no noticeable variation in daily usage, which can sometimes indicate underutilization. If the workload is steady and predictable, there may be an opportunity to optimize costs by adjusting resource allocation. You can also set proactive alerts under the budget option for cost management in Azure and correlate the spend overall with usage consumption in metrics app for SQL database in Microsoft Fabric.
How to get started with Capacity metrics app for SQL database in Fabric
Setting up and leveraging Usage Reporting for SQL Database is straightforward:
- Install Capacity metrics app
- Initially, you must be a capacity admin to install the Microsoft Fabric Capacity Metrics app. Once installed, anyone in the organization can have permissions granted or shared to view the app. For more information, see https://aka.ms/CapacityInstall
- Generate detailed reports:
- The system allows you to generate customized reports based on different filters, to get detailed insights into how your databases are performing.
- Leverage insights to optimize performance:
- Use the trend analysis to optimize your database performance, reduce costs, and ensure that your database infrastructure scales efficiently with your business needs.
Why it’s a game changer
By gaining a deeper understanding of compute and storage consumption, organizations can fine-tune their resources, scale more effectively, and avoid unexpected cost overruns. Capacity metrics app experience for SQL database in Fabric ensures that you can manage all aspects of your data infrastructure in one unified environment.
