By not providing a way to use table hints for spatial indexes in ArcSDE, it seems like ESRI is encouraging developers to write code that directly queries SqlServer tables, bypassing ArcSDE.
More on table hints here.
I wonder if having multiple spatial indexes on a column would make sense. For example, if I find the best index for a point in polygon spatial join differs from the best index for a polygon overlay, I could just have two indexes and specify the appropriate one using a hint, depending on the type of join.
Table hints are usually frowned upon for non-spatial tables, but I think it will be a long time before query optimizers are smart enough to pick the best spatial index.