Speed up your SQL databases with the Performance Dashboard
Performance Troubleshooting of SQL database in Microsoft Fabric
The Performance Dashboard offers robust diagnostic tools to identify and address performance issues in your web application’s SQL database in Microsoft Fabric. This built-in monitoring solution provides detailed performance metrics, facilitating the systematic detection of bottlenecks.
The Performance Dashboard serves as an essential troubleshooting tool, providing detailed analytics and performance indicators that help pinpoint the root cause of database slowdowns and query response issues. By leveraging these diagnostic features, database administrators and developers can efficiently analyze and optimize database performance.
Navigate to the SQL database in Microsoft Fabric homepage and select the database where you are facing this problem. If there is an ongoing message or a bell icon beside Performance Summary Button.
If you see no such indicator, there’s a high chance that the issue might be with some other database that is throttling the performance of this database but nevertheless, check the vitals of this database to be sure that your database is running fine.
If you find one of these indicators as true, then your database is facing some problem, and we need to identify that and solve it. Once you select Performance Summary, it should give you a high-level insight of your database vitals and also alert you if there are issues which need your immediate attention.
The Performance Summary page gives you a quick overview of your database’s health. Each section on the page, called a tile, shows important information about a different aspect of your database. These tiles work like a health report card, making it easy to spot areas that need attention.
CPU Consumption
When you receive an alert about high CPU consumption reaching throttling levels, you can investigate this issue in two ways. For historical analysis, you can examine the specific time period when the incident occurred. Alternatively, you can identify which specific queries are causing the spike in CPU usage. This targeted approach helps you diagnose and address the root cause of performance issues.
The CPU consumption tab would show you a graph indicating the usage of CPU capacity over a period. You can customize the period based on your choice and drill down to the time when you want to analyze the CPU of the database.
Once you are there at the right time interval where the CPU spiked, you can check the corresponding queries which resulted in the spiked CPU from the list below.
You can identify the Query and open its overview page to view more details.
How slow is too slow?
We might often think that a query is a culprit of all the problem but in actual it might not be. How to identify that?
In the Queries Window, we have a Compare Performance tab, which can help you compare the performance of the query in two different periods of time to identify if your query performance has degraded over time or an anomaly which you were not expecting.
After identifying a problematic query, you have several options to analyze it further. You can copy the query and paste it into the Fabric SQL Database editor, Visual Studio Code, or SQL Server Management Studio (SSMS) for detailed examination and optimization.
User Connections
If CPU analysis reveals no significant issues, continue the investigation by examining the User Connections tab. This metric provides valuable insight into database access patterns. Review the connection graph to validate whether the number of concurrent users aligns with expected patterns. Should you observe an unexpected spike in user connections, it’s crucial to conduct a thorough audit of database access and implement appropriate security measures to prevent unauthorized connections.
This systematic approach to troubleshooting ensures comprehensive evaluation of potential performance factors while maintaining database security protocols.
Requests Per Second
Your search for the culprit causing your database to be slow continues and you reach ‘Requests per Second’ tab where you will see the number of times your queries are executed. If you find an anomaly in the execution count, which might just be because of a bug in the front end that is resulting in some extra executions, you now know how to solve it.
Blocked Queries per Second
The ‘Blocked Queries per Second’ tab provides critical diagnostic information for resolving database performance issues. This section displays a comprehensive list of both blocked queries and their blocking counterparts. By identifying these query conflicts, you can determine which sessions are causing resource contention and impeding optimal database performance. This analysis enables you to implement appropriate measures to resolve deadlocks and optimize query execution efficiency.
The list of queries shown are only current ones, multiple details of every session are provided in the list of Blocking queries like Session ID, Blocking Session ID, Request Type, Request Status etc.
Allocated Size
Now that the other clues are exhausted, you need to see if your database has grown beyond limits and capacity. You can find which tables are contributing to this unprecedented growth and plan to deal with it accordingly. The Largest tables are listed in the descending order of size with count of rows as well.
Automatic Index
The Performance Dashboard takes it a step further with its Automatic Index feature. This feature automatically creates indexes when necessary and drops them when they haven’t been used for a long period of time. The Automatic Index does not interfere with any user-created indexes.
Getting started with Performance Dashboard for Fabric SQL database
Get started with Performance Dashboard for SQL database in Microsoft Fabric today! Learn more about it using the resources below:
- Begin by signing up for a 60-day Fabric free trial.
- Enable features in your tenant settings:
- Explore documentations:
- Visit the Fabric community forums to join discussions and share feedback.
- Contribute and vote for product improvement ideas.
