One of my clients Azure SQL Databases has recently begun consuming unreasonable amounts of DTU, and we are having difficulty pinpointing a root cause. This database is fairly simple in architecture; the design was reviewed by a group of DBAs who found no concrete inherent design issues. The database ran without issue for over 2 years, consistently using below 25 DTU, before “falling off a cliff”. Inexplicably, it does not matter how much DTU we allocate, whether 100 DTU or 800 DTU, it will consume 100% of the allocation.
The end client would like to bring on a consulting DBA with a specific knowledge of Azure SQL Database and SQL Server, as this issue involves the SQL Server engine and Azure-specific concepts like DTU. We want to have a full understanding of the problem, as this design pattern exists across our systems. Here are a few specific questions that have arisen from research:
• Are there known situations where DTU usage can swing severely without a correlated uptick in usage?
• Are there inherent weaknesses in the Azure SQL Database platform?
• Are there known issues in SQL Server with using GUID data types for non-clustered indexes or foreign key relationships?
• We are seeing notable index fragmentation for the GUID-based indexes (expected); is this possibly the culprit even though it is not a new condition?
• Is there a recommended fill factor for indexes built on GUID columns?”