I have this select:
DECLARE @NoEntity Int
DECLARE @VehicleMake Varchar(30)
DECLARE @VehicleModel Varchar(30)
SET @NoEntity=19
SET @VehicleMake='VOLKSWAGEN%'
SET @VehicleModel='"CABRIOLET*"'
SELECT Client.PrimaryKey
FROM Client (NOLOCK)
WHERE Client.NoEntity=@NoEntity AND Client.VehicleMake LIKE @VehicleMake AND Contains (Client.VehicleModel, @VehicleModel)
This failed to respond within 5 minutes on the production.
The table has 4 million records
We have indexes on all fields and a full text index on VehicleModel.
Usually, the execution plan would tell us, shown in green, an optimized index to build. In this case, no suggestion is shown.
So, we would normally remove the related indexes and then, the execution plan would be able to react and tell us a recommended index.
In this case, I cannot do that as one of the indexes is the primary key and that primary key is also used in the full text.
What can I do to see what would be the recommended index?