Improve SQL Performance with LIKE '%ABC%' QUERY

I know that using a LIKE '%ABC%' query will not use indexes and there is little that can be changed to the query to improve this, however what can do to make execution quicker? At this stage we cannot change the query to use Full-text indexing.

Some background...

We are bringing systems up in Azure VMs (NOTE: Not Azure SQL, but SQL Server running on Windows 2012) as a 2nd location for additional resilience (offline backup) and have 'built' an SQL server using a basic server spec. Performance of a LIKE query on our old platform took 2 seconds to run, whereas on this Azure platform its taking 10 seconds.

This is clearly server spec limitations, however what can i do to improve this?

I can see the CPU spike during the query run, so it would seem a 'faster' azure cpu will help, but know that these figures can be misleading too!

So my question is, do i need to concentrate on improving CPU, or could it be more than that?

The DB in question is only 300mb on disk, and the table being queried has about 160k rows so it isn't large by any means.

Please let me know if i am barking up the wrong tree here, or if i need to check anything else first?

The SQL server is Windows 2012 R2 with SQL Server 2014 Std, and has been built following the Azure SQL performance guidelines (i.e. data on a dedicated striped drive).

EDIT

As requested, this is the query i am testing:

SELECT Name
FROM Users
WHERE Name like '%ABC%'

Thats it. Nothing complex here, just retrieving data from a small database!

Incidentally, This query takes 10s to run, whereas adding the clause 'AND Description like '%ABC%' reduces the time to 6s?

EDIT 2

Ok, some more information following feedback in the comments...

I have followed the information from this page: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

I have run the query shown, and these are the results shown:

To me, it initially looked like it was Disk IO bound, but the Average wait time seems

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.

StackExchange Database Administrators Q#99108, answer score: 3