Azure SQL - Spatial Peformance Issues

Lachlan Moerenhout 0 Reputation points
2023-01-23T00:34:40.2433333+00:00

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!

Azure SQL Database
{count} votes

2 answers

Sort by: Most helpful
  1. Bas Pruijn 946 Reputation points
    2023-01-23T09:13:09.4733333+00:00

    Your question is puzzling me. You state that an S3 database is not financially feasible. An S3 will cost you $148 per month. A vcore database will cost you $0.5740 per vcore hour. This will result in 80 vcores * 730 hours per month * $0.5740 = $33k per month. I might be missing something here😊

    Azure serverless vcore databases are using remote storage. This will usually result in a lower throughput. If you need faster throughput, you might want to look into locally provisioned storage, like a P1 DTU database, or business critical vcore. These will cost you probably more than a S3, but far less than a 80 vCore serverless.


  2. Alberto Morillo 32,716 Reputation points MVP
    2023-01-23T13:45:37.5+00:00

    With S3 you only have two vCores available and max degree of parallelism is one (1), all your query plans are serialized. I guess your local SQL Server has more computing power.

    If you already think S3 is expensive and you cannot afford to scale up and try what service tier better adjust to the performance you are expecting, then you can try saving Lat and Long Points as varchar and convert them to geography when needed.

    SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Longitude] 
    AS VARCHAR(20)) + ' ' + 
                    CAST([Latitude] AS VARCHAR(20)) + ')', 4326)