Azure SQL Database: Queries Slow Despite Normal DTU/CPU?
Hi all,
Some of the queries on my Azure SQL Database are taking a lot longer than usual, even though the DTU, CPU, and memory metrics all look normal.
There haven't been any recent changes to the schema or major growth in the size of the data.
It could be because of:
Old statistics or poorly thought-out plans
Problems with parameter sniffing
TempDB contention or maintenance that needs to be done
Changes to auto-tuning
Has anyone else had this problem?
When resource metrics look normal, what is the best way to figure out why queries are taking longer than usual?
0
Sign In to comment.
Comments
Hi @Anderson
You may have more luck posting this to a forum specifically handling Azure and SQL issues. Most of the traffic here will be related to troubleshooting issues that occur when traversing one of WatchGuard's firewalls or other products.
-James Carson
WatchGuard Customer Support
Hey!!! Thanks for the idea!
We know that this forum is mostly about WatchGuard products.
We posted here on purpose to reach a wider audience that may have dealt with performance issues in Azure SQL, even if it's not the usual topic of this forum.
Hey!!
Sounds like a classic case of query plan or stats weirdness.
On Azure SQL, even if the DTU, memory, and CPU look good, performance can drop without warning due to parameter sniffing or old stats.
The first thing I would do is look at the actual execution plans for the slow queries to see if they are using bad indexes or sending data to tempdb.
You can also use the query_hash to compare current and past runtimes with sys.dm_exec_query_stats. This can show you if plan changes happened at the same time as the slowdown.
If you think the stats are wrong, a targeted UPDATE STATISTICS or forcing a recompile can often fix the problem without having to do a full db maintenance. Contention in tempdb is less common, but it's still a good idea to check with sys.dm_db_file_space_usage and keep an eye on it. wait stats for PAGEIOLATCH or PAGELATCH waits.
Auto-tuning is also hard. Make sure it hasn't added or removed an index that changes the plan in a way you didn't expect.