Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 376

2. The query result returns 17 employees and the statistics as follows:

Table 'Worktable'. Scan count 1, logical reads 16425, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical
reads 0, lob read-ahead reads 0.
Table 'Product'. Scan count 1, logical reads 4, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 3806, logical reads 11499,
physical reads 0, read-ahead reads 0, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 290, logical reads 623,
physical reads 0, read-ahead reads 0, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0.
Table 'Person'. Scan count 0, logical reads 888, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Employee'. Scan count 1, logical reads 9, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

3. Further to the preceding selection of product sales, let us instruct the query optimizer

to use statistical data instead of initial data values. Use the following TSQL:
--Use the statistical data
DECLARE @LoginID NVARCHAR(250)
SET @LoginID='adventure-works\ranjit0'

SELECT TOP 5 * FROM HumanResources.Employee WHERE LoginID=@LoginID
OPTION (OPTIMIZE FOR UNKNOWN)

4. In addition to the preceding query hint OPTIMIZE FOR UNKNOWN , now let us
instruct the query optimizer to use a particular value for the local variable by
using the following TSQL:
--Use a particular data value
DECLARE @LoginID NVARCHAR(250)
SET @LoginID='adventure-works\ranjit0'

SELECT TOP 5 * FROM HumanResources.Employee WHERE LoginID=@LoginID
OPTION (OPTIMIZE FOR (@LoginId='adventure-works\ranjit0'))

SET STATISTICS IO OFF
GO

This completes the process for the OPTIMIZE FOR UNKNOWN hint. Let us work out the
RECOMPILE hint.