sql server 2008 LIKE performance

Question

I've noticed that the first sql statement with LIKE on a large table runs very slowly (around 20 minutes) but every subsequent one very very fast (a few seconds), even if the strings searched for are completely different from the initial one (the first string 'ability%', the second 'su_mit%')

Does sql server store the results of table scan for the "like statement" in a cache? Is the possible cache accessible to all clients?
Does the "cache" somehow expire?
if a full text index is available for the same column where the LIKE was applied and populated, does the "cache" influences full text speed?
Does anybody know a document explaining the issue?
Cheers
Greg

Answer 1

What you're experiencing is most likely the result of part (or all) of the table being in the buffer cache (data pages cached in memory) after the first scan. Depending on how frequently you hit the same table with scans, available memory, and other tables being cached SQL Server may keep your table's pages in the buffer cache, or may bump it for the benefit of someone else.

For queries with no leading wild card (as in your examples above) you could reduce it to an index scan if the column you're querying is indexed with a normal index...

Full text indexes are not used by the LIKE operator, if you have a fulltext index on the column it is probably a good idea to change from a LIKE query to a fulltext query... See http://msdn.microsoft.com/en-us/library/ms142559.aspx

Answer 2

SQL Server basically works like a virtual memory system, so the performance increase you see is simply more pages being in memory when it does the second table scan.

I am not a SQL server expert so perhaps someone else can add further details to this, but when you use LIKE you are basically removing the databases ability to use any indexes. If you want to use the full text index you need to use one of the specific full text index query commands FREETEXT, FREETEXTTABLE, CONTAINS and CONTAINSTABLE.