Without full-text search, no, there's no magic to making string parsing faster within SQL Server, other than pre-calculating results or throwing more resources at the problem.
If you have a narrow set of search patterns that are repeated over and over again, it's possible you could maintain a skinnier materialized portion of the table that meet those criteria (e.g. a table of just the PK columns representing the rows in the main table that match '%ABC%' - you could maintain these through triggers). This will reduce the amount of reads required, but may not have a serious impact on duration.
If people are entering arbitrary search strings in a non-repeatable and unpredictable way, that may not help anyway.
10 seconds seems like a long time for a table with 160K rows. If you are on V12 (and can run this query in relative isolation), you should be able to determine the waits that changed during that query, using sys.dm_db_wait_stats - it may be that you can't keep 300MB of data in memory and the wait time is all disk churn. In this case it may just be that you are sharing an overwhelmed server, so one consideration would be to move up to a better tier that provides for better performance.
Another option you can consider is application-side caching (think memcached, redis, etc.), where you have a copy of the data in your application's memory, and perform the searching there instead of within SQL Server.
In both cases for the attempted queries in the question, the problems are simply syntactical.
In the first case: You have an IF condition, but do not group the statements after it together, so only the statement immediately following the IF is conditional. The second ALTER DATABASE statement always executes. To fix, do this:
EXECUTE master.sys.sp_MSforeachdb N'USE [?];
IF DB_ID() >= 5
BEGIN
ALTER DATABASE [?] SET QUERY_STORE = ON;
ALTER DATABASE [?] SET QUERY_STORE
(OPERATION_MODE = READ_WRITE,
INTERVAL_LENGTH_MINUTES = 30,
MAX_STORAGE_SIZE_MB = 1000,
QUERY_CAPTURE_MODE = AUTO);
END;
';
You probably also no not need the USE statement. It could be removed and then the DB_ID() could be changed into DB_ID(N''?'').
In the second case: You did not escape the embedded single-quotes. You also had an extraneous EXEC, and you did not include the msdb system database. To fix all of that, do the following:
EXECUTE master.sys.sp_MSforeachdb N'
IF (N''?'' NOT IN (N''tempdb'', N''master'', N''model'', N''msdb''))
BEGIN
ALTER DATABASE [?] SET QUERY_STORE = ON;
ALTER DATABASE [?] SET QUERY_STORE
(OPERATION_MODE = READ_WRITE,
INTERVAL_LENGTH_MINUTES = 30,
MAX_STORAGE_SIZE_MB = 1000,
QUERY_CAPTURE_MODE = AUTO);
END;
';
PLEASE NOTE: @Kris is not incorrect in saying that "sp_MSForeachDB is undocumented and unsupported", and the advice to use another mechanism to cycle through DBs is good advice. It might not be worth the trouble if this is a one-time task, but if this code is to be used repeatedly, then yes, you should probably heed that warning.
Best Answer
This fixed my problem:
Disabling the query store by command:
Then enabling it again fixed the problem.