Sql Azure - maxing DTU Percentage querying "empty table"
I've had database problems for the last month or so ... (November was good). (S0 Standard level - not even the lowest level.) - Fixed update 5
Select statements are causing my database to throttle (time out uniformly). To make sure this wasn't just a problem with my database, I've:
- Copied the database ... same issue for both (unless increasing the level size).
- Deleted database and re-created database (empty database) from root code structure
The second turned out to be more interesting. Now my database has no data and it is still diving in DTU and making things unresponsive.
First ... is it ok?
I have more complex databases at work that use roughly 10% max for dtu at one level (s0). Therefore, I am perplexed. It's just one user, one database and is currently empty and I can make it unresponsive.
Update 2: From copy ("with data 10000 ~ records"). I've updated it to standard S2 (maybe 5x more powerful than s0). Downgrade it to S0 again and
SET UP STATS IO ON SET STATISTICS ON TIME select * from Competitions - 6 entries here ...
SQL Server compile time and time: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Runtime: CPU Time = 0ms, Elapsed Time = 0ms.
SQL Server Runtime: CPU Time = 0ms, Elapsed Time = 0ms.
(6 row(s) affected)
Table "Competitions". Scan count 1, boolean read 3, physical read 1, read forward 0, boolean read 0, physical read lob 0, read / read lob 0.
SQL Server Runtime: CPU Time = 407 ms, Elapsed Time = 21291 ms.
Am I missing an understanding of azure databases to keep them warming up? If I run the same query again, it is immediate. If I close the connection and make it back again before ~ 20 seconds.
Update 3: s1 and it makes the same request above for the first time for ~ 1 second
Update 4: Level s0 again ... first request ...
(6 row(s) affected)
Table "Competitions". Scan count 1, boolean read 3, physical read 0, read forward 0, boolean read 0, physical read lob 0, read-read lob 0.
SQL Server Runtime: CPU Time = 16ms, Elapsed Time = 35ms.
Nothing changes in these databases except the level. After roaming on one of my live sites (different databases, schemas and data) on s0 ... it peaked at 14.58% (his stats site)
This is not my best investigation. But fatigue: D I can give more updates if anyone is interested.
** Update: 5 - fixed view **
The first few 100% of the peaks were the same. After updating the schema and removing the geography field (the data was zero in this column), it moved to later peaks ~ 1-4%, and the result time to a very low ms value.
Thanks for the help, Matt
source to share
The cause of the problem for a mangling 100% DTO was the GEOGRAPHY field: http://msdn.microsoft.com/en-gb/library/cc280766.aspx
Removing this from my requests fixed the issue. Extracting it from my EF models will hopefully come back for sure.
I want to use a geography field in Azure (ultimately maybe not for a few months), so if anyone knows why this caused an unexpected amount of DTU to be spent on the (always always null) column which is very useful for future knowledge.
source to share