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.
Database Advisor Performance Recommendations
Azure SQL Database analyzes historic data and workload patterns and comes up with recommendations to improve performance. The recommendations can be
Create Index
Based on query execution patterns, it recommends the creation of indexes that may improve the performance of your workload.
Drop index
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.
Parameterize queries (preview)
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.
Automated Tuning
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.
Manual Tuning
Update Statistics
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
UPDATE STATISTICS
Create Missing Index
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])
Rebuild or Reorganize Index
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 );
Queries and Execution Plans
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 ;
Long Running Queries
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;
Number of Executions for Query
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;
Highest Average Physical I/O Reads
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;
Highest Waiting Queries
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
T-SQL Recommendations
Intrinsic function usage on columns in predicate or JOIN
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.
LIKE predicate with leading wildcard
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.
UDF (User Defined Functions) without table access should specify SCHEMABINDING
There is a small but distinct performance hit if you do not mark a UDF (which does not access any table data) as SCHEMABINDING.
UDF (User Defined Functions) usage in the output list or WHERE Clause
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.
Wildcard pattern usage
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.
Categories: Azure, Azure SQL Database, Performance Tuning, SQL, T-SQL
Leave a Reply