Find performance issues in Azure SQL database

2
votes

I transitioned a database from a SQL Server 12.0.2548.0 to an Azure SQL database V12. I used SQL Database Migration Wizard, so it should be the same database with regards to indexes, keys, data, tables etc.

However, the performance is generally a lot slower and I have one particular query that takes 7 seconds on the SQL Server database and it takes 20 minutes (!) on the Azure SQL database. On the Azure SQL data - which is an S3 - the CPU and DTU usage is at 100 % the entire 20 minutes.
I already regenerated the indexes on the Azure SQL database with EXEC sp_updatestats.

I am a bit at a loss here, I am a developer, not a DBA.
Where should I start figuring out what causes this extreme difference in performance?

1
sql-serverdatabaseazureazure-sql-database
What is your query, exactly? Have you gotten the Actual (not Estimated) Execution Plans on both your local and Azure databases? Are there any differences between them? – Dai
@Dai: No, I haven't gotten the Actual Execution plan on the Azure database. However, I realized that the estimated execution plan gave me hints about missing indexes. After I added those two indexes, the performance was the same as locally. It is noteworthy that those indexes are missing in the local database as well, so the initial question still is valid: How is such a big difference in performance possible? – Daniel Hilgarth
The query itself is a union of two complex queries that use a CTE (WITH keyword). – Daniel Hilgarth
It's possible your local data was physically arranged such that the index wasn't as-necessary (a coincidental clustering) or just the fact your local machine has a lot less IO contention (SSD too?) so the effect of reading lots of small random rows (as is the case with unclustered unordered data) wouldn't be as noticeable untill you're sharing a database server with potentially hundreds of other simultaneous users. – Dai
@Dai: My "local" machine is actually an Azure VM. So you are saying it is just a co-incidence that it works on the old server and on all other machines where I imported that backup? And you also say that the ordering is different in the Azure SQL database, because it didn't import the backup but got the data via other means (bulk insert, I think)? – Daniel Hilgarth

1 Answers

0
votes

Usually when migrating to Azure I run through a set of DMVs to ensure performance and right plan.

I have written these scripts to help the process.

https://onedrive.live.com/redir?resid=F23F4F5BBCE95F19!150367&authkey=!AF6F4x3mZAyA8qQ&ithint=file%2czi_

Change credentials and connection string in SQLDiagnostics.bat

I generally look for: Worst Query (CPU and IO) Missing indexes Index fragmentation Azure SQL Load Query regressions (using the query store).

Let me know if it helps you troubleshooting?