-- I started out talking about how to compare plans between what's in cache
-- and getting a plan "just for that execution" to see if the problem is 
-- parameter sensitivity

USE [credit];
GO

CREATE INDEX [MemberName]
ON [dbo].[Member]([Lastname], [FirstName], [MiddleInitial]);
GO

UPDATE [dbo].[Member]
	SET [lastname] = 'Tripp'
	WHERE [member_no] = 1234;
GO

-- Create a procedure
CREATE PROCEDURE [GetMember]
	(@ln	varchar(15))
AS
SELECT [m].* 
FROM [dbo].[member] AS [m]
WHERE [m].[lastname] = @ln;
GO

SET STATISTICS IO ON;
GO

-- First, let's see how parameter sensitivity might affect us?!
EXEC [GetMember] 'Tripp';		-- this says to use the index
EXEC [GetMember] 'Anderson';	-- this will use the plan determined by the first execution...
GO

-- So, if you suspect that it's not good... compare the 
-- plan in cache vs. a plan just for THAT execution:

EXEC [GetMember] 'Anderson';				-- uses the plan in cache
EXEC [GetMember] 'Anderson' WITH RECOMPILE;	-- gets a plan JUST for this execution
GO

-- Are they different? YES! SO... the problem is not STATISTICS
-- it's parameter sensitivity!!

-- What about with a statement executed with sp_executesql (forced statement caching)

DECLARE @execstr	nvarchar(100)
SELECT @execstr = N'SELECT [m].* FROM [dbo].[member] AS [m] WHERE [m].[lastname] = @ln'
EXEC sp_executesql @execstr, N'@ln varchar(15)', 'Tripp'			-- says, use an index
go

DECLARE @execstr	nvarchar(100)
SELECT @execstr = N'SELECT [m].* FROM [dbo].[member] AS [m] WHERE [m].[lastname] = @ln'
EXEC sp_executesql @execstr, N'@ln varchar(15)', 'Anderson'			-- uses the plan in cache for the STATEMENT
go

DECLARE @execstr	nvarchar(100)
SELECT @execstr = N'SELECT [m].* FROM [dbo].[member] AS [m] WHERE [m].[lastname] = @ln OPTION (RECOMPILE)'
EXEC sp_executesql @execstr, N'@ln varchar(15)', 'Anderson'			-- gets a plan JUST for this execution
go

-- Again, parameter sensitivity


-- FINALLY, what statements CAN SQL Server cache (not many!!!) but, it can if the statement
-- is super simple - specifically, only TRIVIAL statements

SELECT [m].* FROM [dbo].[member] AS [m] WHERE [m].[member_no] = 1234
SELECT [m].* FROM [dbo].[member] AS [m] WHERE [m].[member_no] = 3456

-- How can you see this in the cache?

SELECT [st].[text], [qs].[execution_count], [qs].*, [p].* 
FROM sys.dm_exec_query_stats AS [qs] 
	CROSS APPLY sys.dm_exec_sql_text ([sql_handle]) [st]
	CROSS APPLY sys.dm_exec_query_plan ([plan_handle]) [p]
WHERE [st].[text] LIKE '%member_no%'
ORDER BY 1, [qs].[execution_count] DESC;

-- See how the execution count is 2 for the parameterized version of the statement?
-- (@1 smallint)SELECT [m].* FROM [dbo].[member] [m] WHERE [m].[member_no]=@1


-- Now, if you update statistics (and, because I updated the row to Tripp AFTER I created
-- the index), SQL Server will invalidate the plan:

UPDATE STATISTICS [dbo].[member];
go

EXEC [GetMember] 'Anderson'; -- now, this does a table scan!
go

-- A better way to get a plan out of cache quickly (instead of updating stats) would be
-- to do a recompile (which really doesn't recompile anything, it just invalidates the
-- plan such that the next execution can't use it...and therefore must recompile)

EXEC sp_recompile Getmember;
go


------------------------------------



