Search Arguments : impact on query plans

For those who may be starting out with query execution plans, it may be interesting to consider the use of Search arguments (SARG) in queries. The use of SARG has 2 objectives :

1. Limit the number of rows returned by the query
2. Use an index seek operation to improve the performance of the query

If the filter is not a SARG then typically an index scan or table scan is performed on the entire table or index.
2 points to remember :

1. A filter expression is not a SARG if the column is used in an expression  such as YEAR(OrderDate) or LEFT(Employee.Name,1)=’B’
2. The use of the COLLATE operator on a column invalidates the use of  and index on that column.

A simple Example using AdventureWorks2008:
CREATE NONCLUSTERED INDEX OrderDateIndex ON Sales.SalesOrderHeader ( Orderdate  )
GO

SELECT COUNT(*) FROM Sales.SalesOrderHeader soh      
WHERE YEAR(soh.OrderDate )      
=2004 

SELECT COUNT(*) FROM Sales.SalesOrderHeader soh      
WHERE soh.OrderDate >= '20040101' AND soh.OrderDate < '20050101'

These 2 queries will generate the following Query plans:

            The first query uses and index scan with the following cost :

as you can see the second query plan cost is reduced by using a SARG in the query resulting in the use of the index seek operator, that allows SQL server to use a balanced tree to search the records.

Join tip – Inner Joins when using ON and WHERE Clause

 

When tuning queries, one the first basic strategies is to minimize the use of joins. Additionally outer joins incur more cost than inner joins as they require extra work to retrieve unmatched rows.

TIP : If only inner joins are used , the ON and WHERE Clause behave the same. Consider the following queries:

you can see that the execution plans generated for both queries are the same:

If the queries would have been written with outer join, they would have different query plans.

List All plans in the cache SQL Server 2008

SELECT 
[cp].[refcounts] , 
[cp].[usecounts] , 
[cp].[objtype] , 
[st].[dbid] , 
[st].[objectid] , 
[st].[text] , 
[qp].[query_plan] 
FROM sys.dm_exec_cached_plans cp 
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st 
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp ;

Full-Text Thesaurus Files overview

 

These files let you define synonyms for full text queries. There is one file for every language supported by fulltext search. To list them run the following Query:

1. List Full-text languages and their LCIDs:

SELECT * FROM sys.fulltext_languages ORDER  BY lcid

2. Thesaurus File location  :     $SQL_Server_Install_Path\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\FTData.                                                            

3. Basic File Structure

• The first element in red <diacritic_sensitive> determines if the thesaurus file is accent-sensitive or note (defaults to 0).
• <expansion> : determines a set of terms that can be substituted. A full-text query can return results using any of the three terms “Internet Explorer” or “IE” or “IE5”.
• <replacement> : one term is substituted for another, the terms in the <pad> elements are replaced by the term in the <sub> element, here “NT5” and “W2K” are replaced by “Windows 2000”, i.e if a full-text query includes either “NT5” or “W2K” the results include only content that contains “Windows 2000”.

in SQL server 2008 , you need to run sys.sp_fulltext_load_thesaurus_file as shown below :

EXEC sys.sp_fulltext_load_thesaurus_file 1033 (see above in this to find the LCID for the language).

List Tables associated with a fulltext catalog

SELECT t.name AS TableName, c.name AS [CatalogName]

FROM sys.tables t JOIN sys.fulltext_indexes i

ON t.object_id = i.object_id JOIN sys.fulltext_catalogs c

ON i.fulltext_catalog_id = c.fulltext_catalog_id