system_health session incidentally dropped

The session can be recreated using the file u_tables.sql (the script is at the bottom of the file) that is located in :

C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Install\

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.