You are right... queries with a leading wildcard are awful for performance. To get around this, Sql Server has something called full text search . You create a special FULL TEXT Index for each of the columns you want to search, and then update your code to use the CONTAINS keyword :

SELECT 
  p.CrmId,
  park.Name
        from Property p
        inner join Som som on som.CrmId = p.SystemOfMeasurementId 
        left join Park park on park.CrmId = p.ParkId
  WHERE 
  (
       Contains(p.City, @search)
    or Contains(p.Address1, @search)
    or Contains(p.Address2, @search)
    or Contains(p.State, @search) 
    or Contains(park.Name, @search) 
    or Contains(p.ZipCode, @search)
 )
 AND (@usOnly = 0 or (p.CrmCountryId = @USA_COUNTRY_ID))

Unfortunately, all those OR conditions are still likely to make this pretty slow, and FULL TEXT wasn't intended as much for shorter strings like City or State, or for casting wide nets like this. You may find you'll do much better for this kind of search by integrating with a tool like Solr or ElasticSearch . In addition to writing a better and faster search, these tools will help you create sane rankings for returning results in an order that makes sense and is relevant to the input.

Another strategy it to create a computed column that concatenates your address and name text into a single column, and then do a single FULL TEXT index on that one field, with a single CONTAINS() call.

This is old post, but in case someone has this issue, there is a good article on https://www.mssqltips.com/ on how to "cheat" using the REVERSE functionality. I think it's really clever and useful and it doesn't require a fulltext search: https://www.mssqltips.com/sqlservertip/5681/how-to-make-sql-server-wildcard-searches-faster/