Hi all, I've recently set up an Azure SQL Database with intentions to build high-performance spatial applications.
Server Config:
- On-Prem -> SQL Server 2022 (Xeon E5-1630 CPU // 64GB 2133mhz DDR4 RAM // Samsung 870 EVO SSD)
- Azure SQL -> General Purpose - Serverless: Standard-series (Gen5) 80 vCore max, 80 vCore min
Note that I also tested the S3 100 DTU model. This not only didn't perform, but isn't feasible financially.
Dataset, replicated across systems (including clustered PK + spatial indexes with bounding boxes and auto-grid):
- [dbo].[AddressGeocodes] -> Lat, Long Points, stored as geometry.
- [dbo].[SA1_GDA2020] -> Multi-polygon geo-spatial boundaries, also in geometry
Query:
SELECT *
FROM [dbo].[AddressGeocodes] GEO
INNER JOIN [dbo].[SA1_GDA2020] SA1 ON GEO.[geom].STIntersects(SA1.[geom]) = 1
Results (after 60 seconds):
- On-prem -> 68,000 Records
- Azure 80 vCores -> 17,000 Records
The estimated and actual execution plans within SSMS are identical, recognising the two clustered PKs and the spatial index.
What I don't understand is within azure portal, the CPU usage is only 2% for the query.
Could anyone please help me understand how there is such a dramatic difference?
There are very limited resources for spatial performance in Azure.
Thanks heaps!