A slow application or a mobile app can be annoying. Imagine a user waiting for updates in the final minutes of a cricket match. Isn’t it? A slow application can lead to bad customer experience and thereby impacting user visits to the site. The slowness of application needs to be tackled in a multi-layered approach. e.g. Application Tuning, Network, Database performance, etc.
Performance Tuning of SQL database in Azure can be a tricky affair, unlike on-premises where you have control over the infrastructure. Azure SQL database makes your life easier by providing a huge number of recommendations. Also, we have various tools/DMVs to troubleshoot performance issues. Let’s see various ways of troubleshooting and tuning performance issues.
Azure SQL Database analyzes historic data and workload patterns and comes up with recommendations to improve performance. The recommendations can be
Based on query execution patterns, it recommends the creation of indexes that may improve the performance of your workload.
Redundant, duplicate or unused Index in database may consume resources. These recommendations may help to free up resources that can be used in other operations.
Frequent recompilation of queries with the same query execution plan outcome adds a burden on the engine. Forced parameterization can help you avoid this.Frequent recompilation of queries with same query execution plan outcome adds additional burden on engine. Forced parameterization can help you avoid this.
Improves performance by continuously monitoring the queries and dynamically adapting the database by applying performance recommendations to changing workload patterns. This service may help you with below activities
- Performance tuning
- Verification of performance gains
- Rollback and self-correction
- Enforcing last good execution Plan
- Tuning T-SQL scripts for manual deployments
- Workload performance monitoring
- Scaling out of databases
- Optimal utilization of DevOps resources thereby reducing the total cost.
This feature is disabled by default and should be enabled on the recommendation of a DBA. To have more control over performance tuning you may ignore this option.
Stale statistics might aid in bad execution plans. Review the Last updated dated for the statistics. Consider updating the statistics as per DBA recommendations.
Query to fetch Last updated date of statistics
SELECT obj.name, sysstats.name AS stats_name, STATS_DATE(object_id, stats_id) AS statistics_update_date FROM sys.stats sysstats inner join sysobjects obj on sysstats.object_id = obj.id WHERE obj.type ='U'
Query to update statistics
Query to Create Missing Index
SELECT mid.database_id as "Database_ID", mid.[object_id] as "Object_ID", CONVERT (varchar, getdate(), 126) AS Runtime , mig.index_group_handle as Index_Group_Handle, mid.index_handle as Index_Handle, CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS Improvement_Measure , migs.unique_compiles as Unique_Compiles, migs.user_seeks as User_Seeks, migs.user_scans as User_Scans, migs.avg_total_user_cost as Avg_Total_User_Cost, migs.avg_total_system_cost as Avg_Total_System_Cost, migs.avg_user_impact as Avg_User_Impact, migs.avg_system_impact as Avg_System_Impact FROM sys.dm_db_missing_index_groups AS mig INNER JOIN sys.dm_db_missing_index_group_stats AS migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
Query to create missing Index
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[tablename] ([col_name1], [col_name2])
Data is stored sequentially in a physical order. Fragmentation may when the logical order of pages in an index does not match the physical order in the data file. This can affect the performance of some queries. Review the Indexes which need defragmentation and Rebuild/Reorganize them.
Query to fetch indexes with fragmentation percent
SELECT DISTINCT DB_NAME() AS DBName , OBJECT_NAME(ps.object_id) AS TableName , i.name AS IndexName , ips.index_type_desc , ips.avg_fragmentation_in_percent FROM sys.dm_db_partition_stats ps INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
Query to Reorganize
ALTER INDEX <index_name> ON <table_name> REORGANIZE
Query to Rebuild
ALTER INDEX <index_name> ON <table_name> REBUILD
Query Store Query Store retains multiple execution plans per query, it can enforce policies to direct the Query Processor to use a specific execution plan for a query Enable Query Store ALTER DATABASE AdventureWorks2012 SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );
You can fetch multiple execution plans for a query from query store and enforce the good one using Query hint USE PLAN
SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.* FROM sys.query_store_plan AS Pl INNER JOIN sys.query_store_query AS Qry ON Pl.query_id = Qry.query_id INNER JOIN sys.query_store_query_text AS Txt ON Qry.query_text_id = Txt.query_text_id ;
Review Long running queries to take troubleshoot the issue and take corrective action
Query to fetch Long running queries:
SELECT TOP 30 rs.avg_duration, qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime, rs.last_execution_time FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id WHERE rs.last_execution_time > DATEADD(day, -60, GETUTCDATE()) ORDER BY rs.avg_duration DESC;
Review the queries with number of executions. Take corrective action.
Query to Fetch number of executions for Query
SELECT q.query_id, qt.query_text_id, qt.query_sql_text, SUM(rs.count_executions) AS total_execution_count FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text ORDER BY total_execution_count DESC;
Review highest average I/O read for a query with corresponding average row count and execution count. These may impact the performance to a great extent
Query to fetch highest average I/O reads:
SELECT TOP 50 rs.avg_physical_io_reads, qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id, rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id WHERE rsi.start_time >= DATEADD(day, -60, GETUTCDATE()) ORDER BY rs.avg_physical_io_reads DESC;
Query to Fetch highest Wait time Queries
SELECT TOP 10 qt.query_text_id, q.query_id, p.plan_id, sum(total_query_wait_time_ms) AS sum_total_wait_ms FROM sys.query_store_wait_stats ws JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id JOIN sys.query_store_query q ON p.query_id = q.query_id JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id GROUP BY qt.query_text_id, q.query_id, p.plan_id ORDER BY sum_total_wait_ms DESC
When a column is wrapped around in a function call within a predicate, the processing can be extremely slow due to the potential resultant scan. Using persisted computed columns or indexed views might be an alternative.
A LIKE predicate with a leading % wildcard can lead to non-usage of otherwise suitable indexes, thereby reducing performance. Using Full-Text Search might be more appropriate.
There is a small but distinct performance hit if you do not mark a UDF (which does not access any table data) as SCHEMABINDING.
The UDF will be evaluated once per output row. If the UDF is complex, processing can be extremely slow, almost like a cursor usage scenario. Using it on smaller result sets is less of an impact than when used on very large data sets. Also, consider using a TVF(Table-Valued Function) and CROSS APPLY.
This kind of wildcard query pattern will cause a table scan, resulting in poor query performance. Consider using separate IF clauses, separated stored procedures.
I hope now you are equipped with enough pointers to take on performance issues in your database. Discuss with your database Administrator before implementing any of these steps.