/*============================================================================
  File:     RecompilationParameterSniffing&Unknown.sql

  Summary:  Understanding how PSP problems occur and how to reduce them
			by using recompilation strategies
            
  Date:     October 2012

  SQL Server Version: SQL Server 200x
------------------------------------------------------------------------------
  Written by Kimberly L. Tripp, SYSolutions, Inc.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by Kimberly L. Tripp.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE CREDIT
go

UPDATE member 
    SET lastname = 'TRIPP' 
    WHERE member_no = 123
go

CREATE INDEX MemberName ON Member(Lastname)
go

-- This proc is a typical proc...
CREATE PROC proc_w_Parameter
    @lname  varchar(15)
AS
SELECT * 
FROM dbo.member AS m
WHERE m.lastname = @lname
go

SET STATISTICS IO ON
SET STATISTICS TIME ON
go -- (also turn on the graphical showplan)

-- On first execution, SQL Server uses parameter sniffing...
-- This is GOOD for the first execution as the 
-- estimate is correct and SQL Server chooses 
-- the right plan for this value.
EXEC proc_w_Parameter 'Tripp'
go

DBCC SHOW_STATISTICS (member, membername)
WITH HISTOGRAM
go

-- However, what if you supply a different value?
-- Since the plan was already determined by the first execution
-- then we're going to get THAT plan. Is that good for this
-- value? Maybe... but, maybe not? This is where parameter
-- sniffing becomes a problem!
EXEC proc_w_Parameter 'Anderson'
go

sp_recompile proc_w_Parameter 

-- One solution is to ALWAYS recompile:
ALTER PROC proc_w_Parameter
    @lname  varchar(15)
AS
SELECT * 
FROM dbo.member AS m
WHERE m.lastname = @lname
OPTION (RECOMPILE)
go

EXEC proc_w_Parameter 'Tripp'
go

EXEC proc_w_Parameter 'Anderson'
go

-- Could end up recompiling too frequently
-- Could optimize for a specific value

ALTER PROC proc_w_Parameter
    @lname  varchar(15)
AS
SELECT * 
FROM dbo.member AS m
WHERE m.lastname = @lname
OPTION(OPTIMIZE FOR (@Lastname = 'Anderson'))
go

EXEC proc_w_Parameter 'Tripp'
go

EXEC proc_w_Parameter 'Anderson'
go

-- But, what if you don't know what the NORMAL value is?

-- Here we get an average plan for the distribution of this data...

-- What SQL Server is doing is getting the "average" plan...
-- If we actually look at the data, you'll find that 'Tripp'
-- is the anomalie. So, it might actually be preferred to
-- use the "average" plan. To simplify this process, they
-- added a new statement hint in SQL Server 2008.

-- In SQL Server 2008+
CREATE PROC proc_w_OptUnknown
    @lname  varchar(15)
AS
SELECT * 
FROM dbo.member AS m
WHERE m.lastname = @lname
OPTION (OPTIMIZE FOR UNKNOWN)
go

EXEC proc_w_OptUnknown 'Tripp'
go

EXEC proc_w_OptUnknown 'Anderson'
go

-- There are cases where the average plan is good enough. 
-- But, you need to be careful because those that would
-- not have been optimal with the average plan might be
-- very slow.
-- 
-- And, don't forget the other options...

-- OPTION (RECOMPILE)
-- OPTION (OPTIMIZE FOR ( ))

-- Finally, there is a way to disable parameter sniffing 
-- SERVER-WIDE (not recommended) using Trace Flag 4136

-- http://support.microsoft.com/kb/980653 

-- If this trace flag is on then parameter sniffing will be
-- turned off and all code will use the density vector
-- for optimization. It's as if you added OPTIMIZE FOR UNKNOWN
-- to ALL of your code.

-- NOTE: You can simulate UNKNOWN by "hiding" the
-- parameter coming in...

-- This procedure uses a variable inside the procedure 
-- to "trick" SQL Server at optimization/compilation time.
-- This turns the parameter into an "unknown" value and
-- as a result - SQL Server cannot "sniff" the incoming
-- value.
CREATE PROC proc_w_Variable
    @lname  varchar(15)
AS
DECLARE @lastname   varchar(15)
SELECT @lastname = @lname
SELECT * 
FROM dbo.member AS m
WHERE m.lastname = @lastname
go

EXEC proc_w_Variable 'Tripp'
go

-- Estimate comes from the density vector (the all density)
-- which is the "AVERAGE"

DBCC SHOW_STATISTICS (member, membername)
--WITH HISTOGRAM
go

EXEC proc_w_Variable 'anderson'
go
