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).

As requested, this is the query i am testing:

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: enter image description here

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...

  • AFAIK LIKE '%ABC%' makes query non sargable which is bound to do index scan in all conditions. Can you show us the query – Shanky Apr 29 '15 at 11:19
  • Hi, i'll edit the above question now... – Darron Apr 29 '15 at 11:58
  • Is there an index on the Name column. Although the query can't lookup in an index due to non Sargability, this could still speed up the query as the more rows will fit on the index pages thus speeding up the scan? – Steve Apr 30 '15 at 8:19
  • This doesn't make sense to me. The db/table as you describe it isn't massive yet you're talking about the same query taking 5 times longer. Can you confirm that the new table has the same indexes and the statistics are up to date. Consider rebuild/reorg on both so we know that we're comparing apples with apples. Was the old server of a comparable spec? Is there anything else running on the new vm that could be consuming memory /cpu? – Sir Swears-a-lot Apr 30 '15 at 9:37
  • Of course if you only care about whether the column starts or ends with 'ABC' then there are techniques to turn this into an Index seekable operation. – Steve Apr 30 '15 at 10:17

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.

  • Hi Aaron, and thanks for this. To make it easier to diagnose, i am running the query in the management studio directly and that is where i am seeing the 2s vs 10s (as reported in the gui). 300Mb is the database size on disk and i have run this with no other databases being used... the server has 3.5Gb and i have set it to use 3Gb (min and max). I would have expected the entire to be in memory therefore a lot quicker... but it appears not. – Darron Apr 29 '15 at 11:53
  • @Darron Oh, so you are using Management Studio locally to consume results from an Azure SQL Database instance? It's quite possible that the time isn't being spent searching at all, but rather transferring all that data to you over the network, and rendering it in grid results. What happens when you change your query from SELECT * or SELECT a lot of columns to just SELECT PK_column? – Aaron Bertrand Apr 29 '15 at 11:58
  • Sorry no, I have a VM which i build which i installed SQL server onto. For my testing i am using Management Studio locally on that server. All tests are from that Management Studio to the SQL instance on the same instance. – Darron Apr 29 '15 at 12:03
  • oh, and changing the SELECT to any variation as suggested makes no difference in processing time. – Darron Apr 29 '15 at 12:04
  • @Darron sigh so this is, or is not, a problem relating to Azure SQL Database? Maybe you should remove all references to Azure since it is misleading at best. Have you validated that SQL Server inside your VM is actually using enough memory? Have you looked at the waits (outside of Azure, you'll need sys.dm_os_wait_stats, or just use Extended Events directly or generate an actual plan from SQL Sentry Plan Explorer). – Aaron Bertrand Apr 29 '15 at 12:07

Your Answer

