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