How can I search for all points with a specific range (radius) of another point?
Can the queries themselves be improved?

...
dbo.myPOI2 ON myTable.geog.STDistance(myPOI2.geog) &lt;= POI2.Point_Radius



Yes. That's going to be slow. You could massively improve that query by buffering the point and finding the intersects:

ON myTable.geog.STIntersects(myPOI.geog.STBuffer(PP.Point_Radius)) = 1


You can make this even better in your case because the radius is provided on the row, rather than in the query. You can store the geometery returned by STBuffer on the table instead of the point. If you ever want to to find the center point (which you'll probably never need), you can use the GIS function STCentroid.

Other implementations

As a side note, this type of query is done with ST_DWithin in PostGIS and will run on the index.