8

We recently observed a serious performance degradation in a SQL Azure database running in Standard3 performance tier - CPU utilization went from ten percent to fifty percent to almost one hundred percent in just one hour. So we changed the performance tier to Premium2 and CPU utilization immediately dropped to about eight percent.

Standard3 is claimed to offer 100 DTUs and Premium2 is claimed to offer 250 DTUs. Which means that eight percent of P2 is just twenty DTUs which is very far from using all of 100 DTUs in Standard3.

Are those DTUs different? Otherwise how is this sudden drop of utilization possible when switching from a 100 DTUs performance tier to a 250 DTUs performance tier?

  • I'm interested in your experience with that issue. I would say that those DTU's are in effect different, but it really depends on what you doing with your DB. For example, in my environment, we have a DB in standard that's struggling a bit because it's doing a lot of CPU intensive work and I'm pretty sure we could benefit a lot from going Premium even if the overall number of DTUs is smaller than we have now in Standard tier. – Etienne Oct 17 '17 at 5:59
  • 1
    @Etienne TL;DR Premium is much, much faster than Standard, especially if you write into the database. For example, a very large SELECT causing a full index scan can easily halt most of the writes on Standard but those writes will just slow down a bit on Premium. YMMV and you should monitor what's going on in your database to see if you like what you see and if you want to pay more. – sharptooth Oct 17 '17 at 8:01
6

I was similarly confused when looking at the pricing for those two tiers. 100 DTUs on standard costs $150/mo and 125 DTUs on Premium costs $465/mo, I figured something else must explain that disparity. I think this line from https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu#choosing-a-service-tier-in-the-dtu-based-purchasing-model must explain the difference:

                            | Standard                   | Premium
IO throughput (approximate) | 2.5 IOPS per DTU           | 48 IOPS per DTU
IO latency (approximate)    | 5 ms (read), 10 ms (write) | 2 ms (read/write)

So it looks like a Premium DTU is actually worth 19x more than a Standard DTU

| improve this answer | |
5

Peformance of Azure databases is expressed in terms of DTUS which means the number of transactions that can be completed per second.Further it also limits the max amount of memory,cpu,IO your database will get..please see below table for more details and pay attention to session requests section..

enter image description here

I hope the above image clarifies on differences between different database tiers.further below is what Azure Documentation has to say when to use different database tiers..

enter image description here

Whenever you want to estimate performance of Azure database,you will want to check below DMVS which gives more details on DTU usage expressed in terms of IO,log,Memory,CPU..

--This DMV contains data for only hour,but captured every 15 seconds

SELECT  
    AVG(avg_cpu_percent) AS 'Average CPU Utilization In Percent', 
    MAX(avg_cpu_percent) AS 'Maximum CPU Utilization In Percent', 
    AVG(avg_data_io_percent) AS 'Average Data IO In Percent', 
    MAX(avg_data_io_percent) AS 'Maximum Data IO In Percent', 
    AVG(avg_log_write_percent) AS 'Average Log Write Utilization In Percent', 
    MAX(avg_log_write_percent) AS 'Maximum Log Write Utilization In Percent', 
    AVG(avg_memory_usage_percent) AS 'Average Memory Usage In Percent', 
    MAX(avg_memory_usage_percent) AS 'Maximum Memory Usage In Percent' 
FROM sys.dm_db_resource_stats; 

--This DMV contains data for 14 days with capturing interval of 5 minutes

SELECT start_time, end_time,    
  (SELECT Max(v)    
   FROM (VALUES (avg_cpu_percent), (avg_physical_data_read_percent), (avg_log_write_percent)) AS value(v)) AS [avg_DTU_percent]  
FROM sys.resource_stats 
WHERE database_name = '<your db name>' 
ORDER BY end_time DESC; 

Whenever you see a DTU metric consistently at 90%,it is an indicator of bottle neck and it can be troubleshooted the same way,we troubleshoot our on prem servers..

Say for example,you are seeing CPU consistently at 90% for a period of time from the data captured through DMV,you can start with gathering queries which are causing high CPU,see if they can be tuned to consume less cpu..When all your tuning efforts are exhausted,then you may need to definitely upgrade to higher level Tier

References: https://azure.microsoft.com/en-in/documentation/articles/sql-database-performance-guidance/#monitoring-resource-use-with-sysresourcestats

| improve this answer | |
  • I've seen this table. It doesn't explain how I got avg_cpu_percent lowered from 100 percent to 8 percent after switching from S3 to P2. – sharptooth Mar 29 '16 at 7:46
  • Imagine in terms like on prem sql server having 2GB ram and when you run memory intensive queries,some may wait for memory grant..so you will see memory metric high all time.When you increase memory,you will see this getting lower – TheGameiswar Mar 29 '16 at 8:35
  • I'm okay seeing it lower, but it went from 100 percent to 8 percent - more than ten times lower. – sharptooth Mar 29 '16 at 8:54
  • Up-to-date details of each tier resources see docs.microsoft.com/en-us/azure/sql-database/… – Michael Freidgeim Feb 14 '18 at 12:44
3

This time the reason was SQL Azure changing its mind on which index to use for a query running often. SQL Azure sometimes decides that changing an index used is maybe a good idea (based on gathered metrics). At the time the problem was observed this mechanism lacked validation - once query was switched to another index the database engine would not validate there was actual improvement. No idea if this changed since that time. The way to work around this situation is using WITH INDEX hint.

Performance improvement we observed was not because of changing the performance tier by itself, just when Standard changed to Premium there was presumably a hardware change and so database statistics got cleared and the database engine reconsidered the query plans once again and so when we switched to Premium the engine just reset to the old plan, that's why we got the performance improvement that time.

| improve this answer | |
  • 1
    To perform a move to a different tier while minimising downtime, Azure basically replicates the database to a host that is serving that sort of load and switches over once complete (the small amount of downtime at the end of the process is where it is closing active transactions replicating the final updates, and switching over). This is likely to result in stats updates which in your case helped the query planner make a better descision. – David Spillett Oct 17 '17 at 12:42
0

If you are looking for the new Azure SQL vCore based comparison, these articles from 3rd Party websites and official Microsoft sites should give you more information.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.