1. 查询使用的参数嗅探完全不合适。也就是说，查询计划对于这次执行是合适的，但是对于下一次执行就可能不合适。
2. 应用程序中存在特定的调用模式，而且与其他大部分调用模式差异很大。通常这种调用是针对初次启动或者新一天的开始时调用。
3. 一个或多个表上的索引结构不能很好地支持查询，但是又有某些次优的索引，导致优化过程中，优化器会无计划选择这些索引。

CREATE PROCEDURE List_orders_12 @custid   nchar(5),
                                @fromdate datetime,
                                @todate   datetime AS
   SELECT *
   FROM   Orders
   WHERE  CustomerID = @custid
     AND  OrderDate BETWEEN @fromdate AND @todate

use Northwind
go
EXEC List_orders_12 ‘SAVEA‘, ‘19970811‘, ‘19970811‘
go
sp_recompile List_orders_12
go
EXEC List_orders_12 ‘CENTC‘, ‘19960101‘, ‘19961231‘

CREATE PROCEDURE List_orders_12 @custid   nchar(5),
                                @fromdate datetime,
                                @todate   datetime AS
SELECT *
FROM   Orders
WHERE  CustomerID = @custid
  AND  OrderDate BETWEEN @fromdate AND @todate
OPTION (RECOMPILE)

CREATE PROCEDURE List_orders_12 @custid   nchar(5),
                                @fromdate datetime,
                                @todate   datetime WITH RECOMPILE AS

1. 存储过程被调用的频率很高，重编译会明显影响系统性能。
2. 查询非常复杂并且编译时间已经明显影响了响应时间。

WHERE (CustomerID = @custid OR @custid IS NULL)
  AND (OrderDate = @orderdate OR @orderdate IS NULL)
  ...

SELECT DISTINCT c.*
FROM   Table_C c
JOIN   Table_B b ON c.Col1 = b.Col2
JOIN   Table_A a ON a.Col4 = b.Col1
WHERE  a.Col1 = @p1
  AND  a.Col2 = @p2
  AND  a.Col3 = @p3  

• 非聚集、不唯一索引Comb0_ix ，覆盖Col1，Col2，Col5，Col4 列。
• 非聚集、不唯一索引Col2_ix，覆盖Col2列。
• 非聚集、不唯一索引Col3_ix ，覆盖Col3列。

DBCC SHOW_STATISTICS (Table_A, Col3_ix)

SELECT Col3, COUNT(*) FROM Table_A GROUP BY Col3 ORDER BY Col3

1. OPTION(RECOMPILE)/ WITH RECOMPILE
2. 添加一个可选的索引，覆盖Col1，Col2，Col3，并且包含Col4。
3. 改写Col3的索引为筛选索引，或者直接删掉。
4. 使用索引提示强制查询使用其他索引。
5. 使用OPTIMIZE FOR查询提示。
6. 把@p3的值复制到本地变量。
7. 修改应用程序行为。

添加新索引：

更改/删除Col3上的索引：

CREATE INDEX col3_ix ON Table_A(col3) WHERE col3 IN (‘FIG‘, ‘RASPBERRY‘, ‘APPLE‘, ‘APRICOT‘)

1. 索引的体积降低了将近99%。
2. 这个索引不再成为问题查询的影响因素，因为SQL Server必须选择一个可以满足所有输入值的查询计划，所以即使嗅探到“APPLE”这个值，SQL Server也不会使用，这个索引，因为对于KIWI这个值而言，查询计划不能覆盖。

SELECT c.*
FROM   Table_C c
JOIN   Table_B b ON c.Col1 = b.Ccol2
JOIN   Table_A a WITH (INDEX = Combo_ix) ON a.Col4 = b.Col1
WHERE  a.Col1 = @p1
  AND  a.Col2 = @p2
  AND  a.Col3 = @p3  

WITH (INDEX (combo_ix, col2_ix))

SELECT c.*
FROM   Table_C c
JOIN   Table_B b ON c.col1 = b.col2
JOIN   Table_A a ON a.col4 = b.col1
WHERE  a.col1 = @p1
  AND  a.col2 = @p2
  AND  a.col3 = @p3
OPTION (OPTIMIZE FOR (@p3 = ‘KIWI‘))  

OPTION (OPTIMIZE FOR (@p3 UNKNOWN))

 CREATE PROCEDURE memdb_get_updated_customers @tstamp timestamp AS
   SELECT CustomerID, CustomerName, Address, ..., tstamp
   FROM   Customers
   WHERE  tstamp > @tstamp

EXEC memdb_get_updated_customers 0x

EXEC memdb_get_updated_customers 0x000000000003E806

EXECUTE memdb_get_updated_customers WITH RECOMPILE

cmd.CommandType = CommandType.Text;
cmd.Text = "EXECUTE memdb_get_updated_customers @tstamp WITH RECOMPILE";

CREATE PROCEDURE memdb_get_updated_customers @tstamp timestamp AS
   IF @tstamp = 0x
      EXECUTE memdb_get_updated_customers_inner @tstamp WITH RECOMPILE
   ELSE
      EXECUTE memdb_get_updated_customers_inner @tstamp

CREATE PROCEDURE memdb_get_updated_customers @tstamp timestamp AS
   IF @tstamp = 0x
   BEGIN
      SELECT CustomerID, CustomerName, Address, ..., tstamp
      FROM   Customers
   END
   ELSE
   BEGIN
      SELECT CustomerID, CustomerName, Address, ..., tstamp
      FROM   Customers WITH (INDEX = timestamp_ix)
      WHERE  tstamp > @tstamp
   END

CREATE PROCEDURE memdb_get_transactions @transid int AS
   IF coalesce(@transid, 0) = 0
      EXECUTE memdb_get_transactions_refresh
   ELSE
   BEGIN
      DECLARE @maxtransid int
      SELECT @maxtransid = MAX(transid) FROM transactions
      EXECUTE memdb_get_transactions_delta @transid, @maxtransid
   END

SELECT ...
FROM   Orders
WHERE  (CustomerID = @custid OR @custid IS NULL)
  AND  (EmployeeID = @empid  OR @empid IS NULL)
  AND  convert(varchar, OrderDate, 101) = convert(varchar, @orderdate, 101)

SELECT ...
FROM   Orders
WHERE  (CustomerID = @custid OR @custid IS NULL)
  AND  (EmployeeID = @empid  OR @empid IS NULL)
  AND  OrderDate >= @orderdate
  AND  OrderDate < dateadd(DAY, 1, @orderdate)

• SQL Server如何编译动态SQL