How long has this system been running now as an Azure SQL Server Database? Presumably if it's more than a few hours old (i.e. some "production" queries have hit it) and it's generated some useful statistics.
Analyzing this and determining the source of your problem will be a multi-pronged strategy.
Service Tier Check
Try the following queries, which determine whether you are at the correct service level:
-----------------------
---- SERVICE TIER CHECK
-----------------------
-- The following query outputs the fit percentage per resource dimension, based on a threshold of 20%.
-- IF the query below returns values greater than 99.9 for all three resource dimensions, your workload is very likely to fit into the lower performance level.
SELECT
(COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent >= 20 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'
,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent >= 20 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'
,(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent >= 20 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'
FROM sys.dm_db_resource_stats
-- Look at how many times your workload reaches 100% and compare it to your database workload SLO.
-- IF the query below returns a value less than 99.9 for any of the three resource dimensions, you should consider either moving to the next higher performance level or use application tuning techniques to reduce the load on the Azure SQL Database.
SELECT
(COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'
,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'
,(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent >= 100 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'
FROM sys.dm_db_resource_stats
Resource Consumption Levels
It would also be useful to check the resource consumption, which you can do using the following query. This will report things like DTU consumption and IO.
-----------------
-- Resource Usage
-----------------
select *
from sys.dm_db_resource_stats
order by end_time desc
Indexes
It's also worth a quick check whether you have missing indexes or whether some of your existing indexes are getting in the way.
The missing index query is a doozy, but should be taken with a grain of salt. I generally see it as an advisement on how the db is being used and I make my own judgement on which indexes to add, and how. For example, as a general rule of thumb, all foreign keys should have non-clustered indexes to facilitate the inevitable JOIN's they're involved in.
--------------------
-- Find poor indexes
--------------------
DECLARE @dbid int
SELECT @dbid = db_id()
SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,
'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,
'Difference' = user_updates - (user_seeks + user_scans + user_lookups)
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i
ON s.object_id = i.object_id
AND i.index_id = s.index_id
WHERE objectproperty(s.object_id,'IsUserTable') = 1
AND s.database_id = @dbid
AND user_updates > (user_seeks + user_scans + user_lookups)
ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC;
------------------
-- Missing Indexes
------------------
declare @improvementMeasure int = 100
SELECT
CONVERT (decimal (28,1),
migs.avg_total_user_cost *
migs.avg_user_impact *
(migs.user_seeks + migs.user_scans))
AS improvement_measure,
OBJECT_NAME(mid.object_id, mid.database_id) as table_name,
mid.equality_columns as index_column,
mid.inequality_columns,
mid.included_columns as include_columns,
'CREATE INDEX IX_' +
OBJECT_NAME(mid.object_id, mid.database_id) +
'_' +
REPLACE(REPLACE(mid.equality_columns, '[', ''), ']', '') +
' ON ' +
mid.statement +
' (' + ISNULL (mid.equality_columns,'') +
CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL
THEN ','
ELSE ''
END + ISNULL (mid.inequality_columns, '') +
')' +
ISNULL (' INCLUDE (' + mid.included_columns + ')',
'') AS create_index_statement,
migs.user_seeks,
migs.unique_compiles,
migs.avg_user_impact,
migs.avg_total_user_cost
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1),
migs.avg_total_user_cost *
migs.avg_user_impact *
(migs.user_seeks + migs.user_scans)) > @improvementMeasure
ORDER BY migs.avg_total_user_cost *
migs.avg_user_impact *
(migs.user_seeks + migs.user_scans) DESC
Maintenance
A maintenance plan should also be setup, whereby you are rebuilding indexes and statistics on a somewhat regular basis. Unfortunately there is no SQL Agent in an Azure SQL environment. But Powershell and either an Azure function or Azure WebJob can help you schedule and execute this. For our on-prem and azure servers, we do this weekly.
Note that WebJob's would only help if you have a pre-existing App Service to run it within.
For scripts on helping you with index and statistics maintenance, checkout Ola Hallengren's script offering.
