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.
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).
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?
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 low? I am no expert in this area so please advise...