CODEWITHSUNDEEP
sqlazureazure-sql-database
TheCiscoKid
TheCiscoKid

Reputation: 156

SQL Azure premium performance

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

Upvotes: 11

Views: 3656

Answers (1)

DavideB
DavideB

Reputation: 609

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.

Upvotes: 6

Related Questions