title: Why my execution plan is the same with index and without and sometimes has missing index?
body: <p>I tested my procedure with and without index to compare the result. My problem is I dont know why my spatial index in noy in the execution plan when I test but sometimes in the execution plan there is a misssing index; I dont know why I have this, can someone help with this? My procedure looks like:</p>
  <pre><code>create procedure    [dbo].[p_search_vehicle]
  @IdCustomer int,
   @idGroupVehicle int = null,
   @ResultCount int= null,
   @Radiant int= null 
   as
   begin
   if @IdCustomer is null
      begin
          print 'The argument cannot be null'
          return 
      end
   declare @start geography
   SET @start = (select location from Customer where idCustomer=@idCustomer )
   ---@Result null group null radiant null
      if @ResultCount is null and @idGroupVehicle is null and @Radiant is null
      select top 10 idVehicle,idGroupVehicle,brand,model,maxRange,weight,maxSpeed, nameLocation , @start.STDistance(locationVehicle)/1000 as distanceInKm
          from Vehicle 
              where (@start.STDistance(locationVehicle)/1000 is not null)
              order by @start.STDistance(locationVehicle)/1000 asc
               ---@Result null  radiant null
              else if @ResultCount is null and @Radiant is null
      select  top 10 idVehicle,idGroupVehicle,brand,model,maxRange,weight,maxSpeed, nameLocation , @start.STDistance(locationVehicle)/1000 as distanceInKm
          from Vehicle 
              where  idGroupVehicle= @idGroupVehicle and (@start.STDistance(locationVehicle)/1000  is not null)
              order by @start.STDistance(locationVehicle)/1000 asc
               ---@Radiant null  
              else if @Radiant is null
      select TOP(@ResultCount) idVehicle,idGroupVehicle,brand,model,maxRange,weight,maxSpeed, nameLocation , @start.STDistance(locationVehicle)/1000 as distanceInKm
          from Vehicle 
              where idGroupVehicle= @idGroupVehicle  and  (@start.STDistance(locationVehicle)/1000  is not null)
              order by @start.STDistance(locationVehicle)/1000 asc
               ---@@idGroupVehicle  null @Radiant is null
              else if  @idGroupVehicle is null and @Radiant is null
      select TOP(@ResultCount) idVehicle,idGroupVehicle,brand,model,maxRange,weight,maxSpeed, nameLocation , @start.STDistance(locationVehicle)/1000 as distanceInKm
          from Vehicle 
              where  (@start.STDistance(locationVehicle)/1000  is not null)
              order by @start.STDistance(locationVehicle)/1000 asc
              ---@idGroupVehicle is null and @ResultCount is null
              else if  @idGroupVehicle is null and @ResultCount is null
      select top 10 idVehicle,idGroupVehicle,brand,model,maxRange,weight,maxSpeed, nameLocation , @start.STDistance(locationVehicle)/1000 as distanceInKm
          from Vehicle 
              where  (@start.STDistance(locationVehicle)/1000   &lt;= @Radiant)
              order by @start.STDistance(locationVehicle)/1000 asc
          --- @idGroupVehicle is null 
              else if  @idGroupVehicle is null 
      select TOP(@ResultCount) idVehicle,idGroupVehicle,brand,model,maxRange,weight,maxSpeed, nameLocation , @start.STDistance(locationVehicle)/1000 as distanceInKm
          from Vehicle 
              where  (@start.STDistance(locationVehicle)/1000   &lt;= @Radiant)
              order by @start.STDistance(locationVehicle)/1000 asc
              --- @Result is null 
              else if  @ResultCount is null 
      select TOP(10) idVehicle,idGroupVehicle,brand,model,maxRange,weight,maxSpeed, nameLocation , @start.STDistance(locationVehicle)/1000 as distanceInKm
          from Vehicle 
              where  idGroupVehicle= @idGroupVehicle and  (@start.STDistance(locationVehicle)/1000   &lt;= @Radiant)
              order by @start.STDistance(locationVehicle)/1000 asc
              --- all options
      else
      select TOP(@ResultCount) idVehicle,idGroupVehicle,brand,model,maxRange,weight,maxSpeed, nameLocation , @start.STDistance(locationVehicle)/1000 as distanceInKm
          from Vehicle 
              where idGroupVehicle= @idGroupVehicle  and  (@start.STDistance(locationVehicle)/1000  &lt;= @Radiant)
              order by @start.STDistance(locationVehicle)/1000 asc
   end
  GO
  </code></pre>
  <p>this procedure retunr list of vehicle which is the nearest to user
  i have a spatial index for table vehicle is that</p>
  <pre><code>CREATE SPATIAL INDEX [SIndx_Vehicle_locationVehicle] ON [dbo].[Vehicle]
  (
      [locationVehicle]
  )USING  GEOGRAPHY_AUTO_GRID 
  WITH (
  CELLS_PER_OBJECT = 12, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  GO
  </code></pre>
  <p>When I test this procedure with <code>set statistics io on</code> I don't see my spatial index in the execution plan. I dont know why but I have a green message with missing index.</p>
  <p>Can someone explain why I have the same execution plan with index and without?</p>
  <p><a href="https://i.stack.imgur.com/SuT3H.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/SuT3H.png" alt="enter image description here" /></a></p>