SQL Server Performance Tips

FastSqlServer.com - Chris Dickey - SQL Server Performance Consultant

SQL Server Performance Tuning Tips

Use CONVERT(Date, column) = '2012-08-01' to compare a datetime column to a date constant

The important point is that an index seek will be used on the column that is being converted using the Date data type. This option became available in SQL Server 2008 when the Date datatype was first introduced.
Using a function on a table column usually prevents an index seek. But not in this special case. It is much easier to use this feature to solve SQL Server performance problems instead of doing a more complex query rewrite with an explicit date range comparison.

use AdventureWorks
add this index:

CREATE NONCLUSTERED INDEX [ix_OrderDate] ON [Sales].[SalesOrderHeader]([OrderDate])

turn on "include actual execution plan"

--this query does an index scan - a performance problem that needs to be fixed

select COUNT(*) from sales.SalesOrderHeader 
where convert(varchar(10),OrderDate,120) = '2012-08-01'

--this simple change causes the query plan to use an index seek - problem solved

select COUNT(*) from sales.SalesOrderHeader 
where CONVERT(Date,OrderDate) = '2012-08-01'

--this is an oftn recommended solution that is more complex

select COUNT(*) from sales.SalesOrderHeader 
where OrderDate >= '2012-08-01' and OrderDate < CAST('2012-08-01' as datetime) + 1

Option(Recompile) - lots of query plan magic

Yes, Option(Recompile) on a query does force a compile. But it does much more! If the query has table variables, the optimizer knows the exact number of rows instead of the estimate of 1 row. If there are local variables, the optimizer knows the exact value so it has better selectivity estimates.
Since SQL Server 2008 SP2 and 2008 R1 SP1, the optimizer knows how to completely eliminate the branches in WHERE clauses that don't apply because of the value of a variable.
The following WHERE clause will have the 1st AND totally optimized out if @name IS NULL. Without Option(Recompile), the value in t.Name will have to be checked even if the value of @name used for the plan is NULL. This is a T-SQL pattern that is often used in report queries with optional parameters. That is one reason why option(recompile) is a big deal!

SELECT * from table t
AND (@name is NULL or @name = t.Name)
AND (@id is NULL or @id = t.id)
WITH RECOMPILE at the stored proc level only causes the queries in the proc to be recompiled. It does not have these other properties. The reason is that since Option(Recompile) is an attribute of the query, the optimizer knows that the query plan will only be used 1 time.

A trivia note: This feature of Option(Recompile) was first added in the release version of SQL Server 2008. It was disabled in SP1 because of a bug causing incorrect results in certain cases. It was fixed and back in action as of SP1 CU5. The fix is in SQL Server 2008 SP2 and SQL Server 2008 R2 SP1.
News: SQL Server 2008 SP1 CU5 re-instated parameter embedding optimization using option(recompile).


Chris Dickey