The setting 'auto create statistics' causes wildcard TEXT field searches to hang

I have an interesting issue happening in Microsoft SQL when searching a TEXT field. I have a table with two fields, Id (int) and Memo (text), populated with hundreds of thousands of rows of data. Now, imagine a query, such as:

SELECT Id FROM Table WHERE Id=1234

Pretty simple. Let's assume there is a field with Id 1234, so it returns one row. Now, let's add one more condition to the WHERE clause.

SELECT Id FROM Table WHERE Id=1234 AND Memo LIKE '%test%'

The query should pull one record, and then check to see if the word 'test' exists in the Memo field. However, if there is enough data, this statement will hang, as if it were searching the Memo field first, and then cross referencing the results with the Id field.

While this is what it is appearing to do, I just discovered that it is actually trying to create a statistic on the Memo field. If I turn off "auto create statistics", the query runs instantly.

So my quesiton is, how can you disable auto create statistics, but only for one query? Perhaps something like:

SET AUTO_CREATE_STATISTICS OFF

(I know, any normal person would just create a full text index on this field and call it a day. The reason I can't necessarily do this is because our data center is hosting an application for over 4,000 customers using the same database design. Not to mention, this problem happens on a variety of text fields in the database. So it would take tens of thousands of full text indexes if I went that route. Not to mention, add开发者_如何学运维ing a full text index would add storage requirements, backup changes, disaster recovery procedure changes, red tape paperwork, etc...)

I don't think you can turn this off on a per query basis.

Best you can do would be to identify all potentially problematic columns and then CREATE STATISTICS on them yourself with 0 ROWS or 0 PERCENT specified and NORECOMPUTE.

If you have a maintenance window you can run this in it would be best to run without this 0 ROWS qualifier but still leave the NORECOMPUTE in place.

You could also consider enabling AUTO_UPDATE_STATISTICS_ASYNC instead so that they are still rebuilt automatically but this happens in the background rather than holding up compilation of the current query but this is a database wide option.