SQL Azure Performance: SOLVED

Question

Hope this will help someone else.

We have a SaaS application that we are moving to Azure. While our table sizes in our multi-tenant database are not small (500,000 or so) the performance on any join was unacceptable.

A simple count statement with joins was taking nearly 1000 ms. Our indexes are solid and the on-premise time was reading 0 ms. The Query Plans were radically different.

ISSUE
The Azure Data Migration tool, helpful as it is, creates NOCHECK CONSTRAINTS on all foreign keys. After dropping the CONSTRAINTS (hundreds) and recreating them without the NOCHECK  option, we were flying again.

The same query was down to 6 ms.

Enjoy,
~Joe