Why is the built-in distance function so much slower than my custom one?
SQL Server 2016 improved the perf somewhat, but the basic reason is the STDistance is accurate, but simpler formulas can be faster, if you understand the errors they produce.
See eg Geographic distance can be simple and fast, and a description of the algorithm used in STDistance:

This paper outlines some geometric algorithms that are used in
Microsoft SQL Server for processing geospatial data in a way that is
independent of any cartographic projection.

Geometric algorithms on an ellipsoid earth model
eg
declare @lat1 real = 33.1 
declare @lon1 real =-117.1
declare @lat2 real = 39.6
declare @lon2 real =-98.7

declare @source geography = geography::Point(@lat1, @lon1, 4326);
declare @target geography = geography::Point(@lat2, @lon2, 4326);

declare @distance float 
declare @i int = 0
declare @iter int = 1000 * 1000
declare @start datetime2 

set @start = sysdatetime()
set @i = 0
while @i &lt; @iter
begin
  set @distance =  @source.STDistance(@target)   
  set @i += 1
end
select concat('STDistance: ',  datediff(ms,@start,sysdatetime()), 'ms' )


set @start = sysdatetime()
set @i = 0
while @i &lt; @iter
begin
 set @distance =  0.621371 * 6378.137 * ACOS(ROUND(
    (SIN(PI() *  @lat2 /180) * SIN(PI() * @lat1/180))
    + (COS(PI() *  @lat2 /180) * COS(PI() * @lat1/180) * COS(PI() * @lon1/180 - PI() * @lon2 /180)), 12))  
  set @i += 1
end
select concat('calculation: ',  datediff(ms,@start,sysdatetime()), 'ms' )

set @start = sysdatetime()
set @i = 0
while @i &lt; @iter
begin
  set @distance =  @source.STDistance(@target)   
  set @i += 1
end
select concat('STDistance: ',  datediff(ms,@start,sysdatetime()), 'ms' )

set @start = sysdatetime()
set @i = 0
while @i &lt; @iter
begin
 set @distance =  0.621371 * 6378.137 * ACOS(ROUND(
    (SIN(PI() *  @lat2 /180) * SIN(PI() * @lat1/180))
    + (COS(PI() *  @lat2 /180) * COS(PI() * @lat1/180) * COS(PI() * @lon1/180 - PI() * @lon2 /180)), 12))  
  set @i += 1
end
select concat('calculation: ',  datediff(ms,@start,sysdatetime()), 'ms' )

outputs
--------------------------
STDistance: 2367ms


---------------------------
calculation: 1098ms


--------------------------
STDistance: 2347ms


---------------------------
calculation: 1105ms