I have an application which will be deployed on Azure.
If we don't care about the cost of the hosting (which will be more dependent on CPU and IO than response time), what would be the best strategy to optimize queries for high frequency utilization (queries which will be executed many times per second ) ?:
Of course all those optimization scenarios have one goal = execute the maximum queries per second.
What would be the most important strategy and why ?
Reduce IO. Azure has shockingly low IOPS even with Premium Storage. I guess that's the price of geo-mirroring. Keep all data for high-frequency queries in tempdb on SSD Temporary Storage on SQL Server VM. Alternatively try utilizing Memory-Optimized Tables.
Extra CPU can be expensive, but it is available.
Response time across VMs can be reduced if they are on the same Affinity Group. For the client response time pick the nearest Region as the ping flies.