I found the solution, but I’m not sure I understand why. Apparently the SQL server on my development machine uses the spacial index by default, but the production machine does not. I added “WITH (INDEX (geog_sidx))” to the query and now it runs just as fast on the production machine and the execution plan looks identical to the one on my development computer.
I just somehow stumbled onto this solution by accident while trying to research spatial query optimization techniques. I didn’t even know that manually specifying an index was possible…
Why would an index not be used if it were available?