SQL Azure premium performance

11
votes

I am currently moving a clients database to SQL Azure and we are seeing some performance issues. We have a premium p2 instance however we are seeing performance times of the SQL azure database about 3 times slower than the current database (sql server 2008 with similar number of cores and ram) . The indexes match, the statements are the same and so is the data.

I understand under the shared Azure SQL databases that performance would be poor, however since we have a p2 instance I would expect the performance to be much closer to our existing sql server 2008 database. Can anyone provide any insight into why a p2 might run slower than sql server 2008 of very similar spec? I realize there is latency to be taken into account, however both servers are remote from my location so this should balance out somewhat or account for only a small difference, not 200 milliseconds v's 600 milliseconds for one simple query.

Given the lack of performance tuning tools at the moment for SQL Azure databases can anyone provide any helpful suggestions on performance tuning the database?

Thanks in advance

P.s. I have also asked this question on the MS azure forum as I'm not sure how much attention that gets. http://social.msdn.microsoft.com/Forums/windowsazure/en-US/cf269a65-7222-4c67-a294-3fa2f67c9583/sql-azure-premium-p2-performance-issues?forum=ssdsgetstarted

1
sqlazureazure-sql-database
As suggested in the response you received on the MS Forum you have to take into consideration the Azure SQL Database has HA while you did not mention it about the remote on premises sql 2008. This may affect at a certain degree (not 3 times) some types of operations so you should take a look at the stats because # of CPU and RAM are just two indicators amnong others: IOPS is the most important factor IMO. msdn.microsoft.com/en-us/library/windowsazure/dn369873.aspx – DavideB
Look at the view sys.resource_stats from master to see resource consumption of the database. You may be hitting the IO bottlenecks though CPU and memory looking fine. Looks for the query wait times using sys.dm_db_wait_stats and other dmvs like sys.dm_exec_Requests to identofy the bottlenecks. – Sirisha Chamarthi
Do you have automatic replication in your on-premises server? – sharptooth

1 Answers

6
votes

As mentioned in the comments Azure SQL, shared or premium, differs significantly from on-premises considering the hardware and network infrastructure. This document compares SQL Server to SQL Databases (aka SQL Azure) and as commodity hardware is used on Azure it might explain the difference you experience: while a CPU stays the same on-prem and on the cloud the choice you made for disks in your on-prem might be different from the disk infrastructure in Azure SQL Databases.

I ignore the type of query you evaluated but in my experience and because of my scenario disk IOPS tend to be as much important (or even more) than the number of CPU cores and RAM, see limits.

Making it short there is no promise of equivalent performance between SQL Server and SQL Database considering CPU and RAMs only, even if these resources are reserved with the Premium option.

We have many Azure SQL Databases in production and few ones are Premium: we had great advantages in implementing telemetry to automatically gather information and being able to use it later as needed for analysis. Here is a link to the CAT blog post: http://blogs.msdn.com/b/windowsazure/archive/2013/06/28/telemetry-basics-and-troubleshooting.aspx

It does not apply specifically to Premium or to Shared but, If it fits in your project scope, you should take the effort to implement telemetry. Otherwise it is a good starting point to look for relevant queries to system views on Azure SQL.

If you want to achieve in Azure SQL the same performances you get on-premises I suggest to investigate and eventually optimize for the different scenario.