Top 10 Things to Investigate for Optimum Azure SQL Performance:
1. Query Optimization
2. Index Fragmentation
3. Resource Constraints
4. Blocking and Deadlocks
5. Network Latency
6. Insufficient Memory
7. Storage Latency
8. Outdated & Duplicated Statistics
9. Security Configuration
10. Resource Throttling
Click your top 10 items above to go straight to the topic’s content below.
Azure SQL Running Slow? Poor Performance? Read on!
As businesses move towards cloud-based services, Microsoft’s Azure SQL Database has become a popular choice for many organizations. It provides scalability, security, and high availability while reducing the need for on-premises infrastructure. However, like any other technology, Azure SQL Database may experience performance issues. In this blog post, we will explore the top ten reasons why Azure SQL may be running slow or performing poorly and what you can do to investigate and resolve these issues.
1. Query Optimization
One of the primary reasons for slow SQL performance, regardless of Azure or On-Prem, is inefficient queries. Poorly optimized queries cause excessive CPU and memory usage, resulting in slow database performance, resource contention, and blocking. You can investigate this issue by using Query Store to identify the problem queries, then refactor the TSQL and/or make careful index adjustments.
2. Index Fragmentation, Page Splits/Fill Factor, and Routine Index Maintenance
There is an increasing amount of chatter about the importance (or lack thereof) of index rebuilds to control fragmentation. Fragmented indexes can slow down database performance on spinning media, particularly if you have large tables with millions of rows. But with the widespread adoption of Solid State Drives (SSD), fragmentation now takes the back seat to Intermediate-level Page Splits. The first step to getting Page Splits under control is to set up an extended event to monitor all indexes: LOP_DELETE_SPLIT activity (aka. Bad Page Splits). Then focus on those indexes that are page-splitting, and, starting at 100% – incrementally reduce the fill factor by 1% at a time, over the course of several days until page splits go away. Then regularly rebuild that index at its newly tuned Fill Factor “sweet spot” to keep page splits at bay.
3. Resource Constraints
Azure SQL database instances have limited resources, and if these resources are constrained, performance can be affected. You can investigate resource constraints by monitoring resource utilization using the Azure portal or using dynamic management views like sys.dm_os_performance_counters.
4. Blocking and Deadlocks
Blocking and deadlocks can significantly affect database performance. To investigate this issue, you can use SQL Server Management Studio’s Activity Monitor or Azure Portal’s Query Performance Insight to identify any blocking and deadlocks. Once identified, resolve these issues by tuning queries, modifying index structures, or adjusting transaction isolation levels. Also, consider if using read_commited_snapshot and/or snapshot_isolation is right for your workload.
Blocking and Deadlocks can be dramatically reduced by deduping the indexes and statistics and removing unused indexes. On highly concurrent systems, consider disabling page locking on heavily used indexes and tables.
5. Network Latency
Azure SQL databases are hosted in the cloud, and network latency can impact database performance. Investigate network latency issues by monitoring network performance metrics like asynch_network_io, round-trip time, packet loss, and bandwidth usage. Consider aligning SQL Server TDS Network Packet Size with your network’s MTU (Maximum Transmission Unit).
Need Help?
You can talk with a team member about any questions you might have regarding database management or remote DBA services.
6. Insufficient Memory
Insufficient memory can also impact database performance. You can investigate this issue by monitoring memory usage using dynamic management views like sys.dm_os_process_memory, Monitor SQL Server: Buffer Manager: Page Reads/sec, Buffer cache hit ratio, and sys.dm_os_memory_clerks. If memory usage is high, consider increasing the memory allocation for the database instance. It’s also a good idea to check memory utilization for the instance to ensure the box is not exceeding 80% utilization. When memory utilization passes 80% of allocated memory, memory management kicks in and starts making internal operational structures smaller, which negatively impacts performance. Ensure Maxmem is properly configured for the workload and allocated resources.
7. Storage Latency
Storage latency can impact database performance, particularly if you have many read-and-write operations. Investigate this issue by monitoring storage performance metrics like average I/O time and I/O operations per second. Separate the I/O patterns via disk scale-out. TLogs are sequential write, while Data is random read-write. Those two workloads do not play well together on the same controller. Tempdb is usually heavily used and benefits from having its data files isolated and placed on its own separate controller.
8. Outdated and Duplicated Statistics
Outdated and/or duplicated statistics can cause the SQL query optimizer to generate inefficient execution plans resulting in erratic query performance and high CPU usage. Remediate this issue by removing duplicate statistics and updating stale statistics regularly.
9. Security Configuration
Misconfigured security settings can impact database performance. Investigate this issue by ensuring that security settings are correctly configured and permissions are appropriately set up. If all logins are assigned the default database of Master, the DQ (distributed query) waits can impact performance.
10. Resource Throttling
Azure SQL database instances have resource throttling features that limit resource usage to prevent excessive resource consumption. Investigate this issue by monitoring resource utilization using the Azure portal or dynamic management views like sys.dm_os_performance_counters.
In conclusion
Azure SQL databases offer several benefits, but performance issues can occur. Investigating and resolving these issues is critical to ensure that your Azure SQL database is performing optimally. By addressing the ten reasons mentioned above, you can improve Azure SQL database performance and provide better experiences for your users.
Contact a Team Member
We would love to help you with your database project or manage your environment, call us at 888-685-3101 ext. 2 or complete the form and start a conversation with Virtual-DBA today!
Have a complex or urgent need?
We’re on standby to answer any questions you may have.