The Use and Abuse of RECOMPILE in SQL Server (Video)

Last Updated 12 years ago

What are the costs when you ask SQL Server to recompile a statement or a stored procedure each time it runs? In this 30 minute talk Kendra Little explains the impact on query execution– and on your SQL Server instance– of forcing recompilation. This talk is appropriate for DBAs and Developers who have a working knowledge of execution plans in SQL Server.

Want to use the scripts from the video? Scroll on down to the bottom of the page.

Learn more about Recompilation in SQL Server

Download the demo database used today: Contoso Retail DW
The DMV Script used in the demo was adapted from the SQL Server Books Online Entry for sys.dm_exec_sql_text

Scripts used today:

USE ContosoRetailDW;
GO
SET NOCOUNT ON;
GO

-- Demo 1:
-- Recompile Hogs Shared Resources.

-- Run this batch. How long does it take?

USE ContosoRetailDW;
SET STATISTICS TIME, IO OFF;
SET NOCOUNT ON;
GO

DECLARE @p NUMERIC(10,2);
SELECT  @p=SUM(TotalCost)
FROM    dbo.FactOnlineSales os
        JOIN dbo.dimProduct p ON os.ProductKey = p.ProductKey
WHERE   StoreKey = 1
GROUP BY ProductName OPTION (RECOMPILE);
GO 5000

-- Now run this batch.
-- The only thing different is the lack of RECOMPILE hint.
USE ContosoRetailDW;
SET STATISTICS TIME, IO OFF;
SET NOCOUNT ON;
GO

DECLARE @p NUMERIC(10,2);
SELECT  @p=SUM(TotalCost)
FROM    dbo.FactOnlineSales os
        JOIN dbo.dimProduct p ON os.ProductKey = p.ProductKey
WHERE   StoreKey = 1
GROUP BY ProductName;
GO 5000

-- DEMO 2:
-- Recompile Doesn't Tell You It Used Your Stuff

USE ContosoRetailDW;
GO
DBCC FREEPROCCACHE;
GO

-- First, let's create a simple stored procedure. No hints here!

IF OBJECT_ID('dbo.rptStoreSales') IS NULL
	EXEC sp_executesql N'CREATE PROC dbo.rptStoreSales AS RETURN 0;';
GO
ALTER PROC dbo.rptStoreSales
	@StoreKey INTEGER
AS
	SELECT ProductName, SUM(TotalCost) AS TotalSales
	FROM dbo.FactOnlineSales os
	JOIN dbo.dimProduct p ON os.ProductKey=p.ProductKey
	WHERE StoreKey=@StoreKey
	GROUP BY ProductName
GO

-- Give it a run.

EXEC dbo.rptStoreSales @StoreKey=199;

-- Now check out the details.

SELECT  ( SELECT TOP 1 SUBSTRING(st.text, statement_start_offset / 2 + 1,
             ( ( CASE WHEN statement_end_offset = -1
				THEN ( LEN(CONVERT(NVARCHAR(MAX), st.text))* 2 )
                ELSE statement_end_offset
                END ) - statement_start_offset ) / 2 + 1) )AS sql_statement,
	st.[text] AS [sql_batch], ps.execution_count, ps.creation_time, ps.last_execution_time,
	ps.total_elapsed_time, ps.total_worker_time, ps.total_logical_reads,
	ps.total_physical_reads, ps.total_logical_writes, ps.plan_generation_num,
	qp.query_plan
FROM sys.dm_exec_query_stats ps
CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) AS st
OUTER APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp
WHERE (st.text LIKE '%SELECT ProductName, SUM(TotalCost) AS TotalSales%' OR st.text LIKE '%dbo.rptStoreSales%')
AND st.text NOT LIKE '%sys.dm_exec_query_stats%';
GO

-- Now, alter the procedure to use RECOMPILE

ALTER PROC dbo.rptStoreSales
	@StoreKey INTEGER
WITH RECOMPILE
AS
	SELECT ProductName, SUM(TotalCost) AS TotalSales
	FROM dbo.FactOnlineSales os
	JOIN dbo.dimProduct p ON os.ProductKey=p.ProductKey
	WHERE StoreKey=@StoreKey
	GROUP BY ProductName
GO

-- Give it a run.

EXEC dbo.rptStoreSales @StoreKey=199;

-- Now check out the details.

SELECT  ( SELECT TOP 1 SUBSTRING(st.text, statement_start_offset / 2 + 1,
             ( ( CASE WHEN statement_end_offset = -1
				THEN ( LEN(CONVERT(NVARCHAR(MAX), st.text))* 2 )
                ELSE statement_end_offset
                END ) - statement_start_offset ) / 2 + 1) )AS sql_statement,
	st.[text] AS [sql_batch], ps.execution_count, ps.creation_time, ps.last_execution_time,
	ps.total_elapsed_time, ps.total_worker_time, ps.total_logical_reads,
	ps.total_physical_reads, ps.total_logical_writes, ps.plan_generation_num,
	qp.query_plan
FROM sys.dm_exec_query_stats ps
CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) AS st
OUTER APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp
WHERE (st.text LIKE '%SELECT ProductName, SUM(TotalCost) AS TotalSales%' OR st.text LIKE '%dbo.rptStoreSales%')
AND st.text NOT LIKE '%sys.dm_exec_query_stats%';
GO

-- What if we move the RECOMPILE hint?
ALTER PROC dbo.rptStoreSales
	@StoreKey INTEGER
AS
	SELECT ProductName, SUM(TotalCost) AS TotalSales
	FROM dbo.FactOnlineSales os
	JOIN dbo.dimProduct p ON os.ProductKey=p.ProductKey
	WHERE StoreKey=@StoreKey
	GROUP BY ProductName OPTION (RECOMPILE);
GO

--Give it a run. Three times.

EXEC dbo.rptStoreSales @StoreKey=199;
EXEC dbo.rptStoreSales @StoreKey=199;
EXEC dbo.rptStoreSales @StoreKey=199;

--Now check out the details in the cache.
SELECT  ( SELECT TOP 1 SUBSTRING(st.text, statement_start_offset / 2 + 1,
             ( ( CASE WHEN statement_end_offset = -1
				THEN ( LEN(CONVERT(NVARCHAR(MAX), st.text))* 2 )
                ELSE statement_end_offset
                END ) - statement_start_offset ) / 2 + 1) )AS sql_statement,
	st.[text] AS [sql_batch], ps.execution_count, ps.creation_time, ps.last_execution_time,
	ps.total_elapsed_time, ps.total_worker_time, ps.total_logical_reads,
	ps.total_physical_reads, ps.total_logical_writes, ps.plan_generation_num,
	qp.query_plan
FROM sys.dm_exec_query_stats ps
CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) AS st
OUTER APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp
WHERE (st.text LIKE '%SELECT ProductName, SUM(TotalCost) AS TotalSales%' OR st.text LIKE '%dbo.rptStoreSales%')
AND st.text NOT LIKE '%sys.dm_exec_query_stats%';
GO

-- Compare Execution Count and Plan Generation Number

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

USE ContosoRetailDW;

SET NOCOUNT ON;

-- Demo 1:

-- Recompile Hogs Shared Resources.

-- Run this batch. How long does it take?

USE ContosoRetailDW;

SET STATISTICS TIME, IO OFF;

SET NOCOUNT ON;

DECLARE @p NUMERIC(10,2);

SELECT @p=SUM(TotalCost)

FROM dbo.FactOnlineSales os

JOIN dbo.dimProduct p ON os.ProductKey = p.ProductKey

WHERE StoreKey = 1

GROUP BY ProductName OPTION (RECOMPILE);

GO 5000

-- Now run this batch.

-- The only thing different is the lack of RECOMPILE hint.

USE ContosoRetailDW;

SET STATISTICS TIME, IO OFF;

SET NOCOUNT ON;

DECLARE @p NUMERIC(10,2);

SELECT @p=SUM(TotalCost)

FROM dbo.FactOnlineSales os

JOIN dbo.dimProduct p ON os.ProductKey = p.ProductKey

WHERE StoreKey = 1

GROUP BY ProductName;

GO 5000

-- DEMO 2:

-- Recompile Doesn't Tell You It Used Your Stuff

USE ContosoRetailDW;

DBCC FREEPROCCACHE;

-- First, let's create a simple stored procedure. No hints here!

IF OBJECT_ID('dbo.rptStoreSales') IS NULL

EXEC sp_executesql N'CREATE PROC dbo.rptStoreSales AS RETURN 0;';

ALTER PROC dbo.rptStoreSales

@StoreKey INTEGER

SELECT ProductName, SUM(TotalCost) AS TotalSales

FROM dbo.FactOnlineSales os

JOIN dbo.dimProduct p ON os.ProductKey=p.ProductKey

WHERE StoreKey=@StoreKey

GROUP BY ProductName

-- Give it a run.

EXEC dbo.rptStoreSales @StoreKey=199;

-- Now check out the details.

SELECT ( SELECT TOP 1 SUBSTRING(st.text, statement_start_offset / 2 + 1,

( ( CASE WHEN statement_end_offset = -1

THEN ( LEN(CONVERT(NVARCHAR(MAX), st.text))* 2 )

ELSE statement_end_offset

END ) - statement_start_offset ) / 2 + 1) )AS sql_statement,

st.[text] AS [sql_batch], ps.execution_count, ps.creation_time, ps.last_execution_time,

ps.total_elapsed_time, ps.total_worker_time, ps.total_logical_reads,

ps.total_physical_reads, ps.total_logical_writes, ps.plan_generation_num,

qp.query_plan

FROM sys.dm_exec_query_stats ps

CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) AS st

OUTER APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp

WHERE (st.text LIKE '%SELECT ProductName, SUM(TotalCost) AS TotalSales%' OR st.text LIKE '%dbo.rptStoreSales%')

AND st.text NOT LIKE '%sys.dm_exec_query_stats%';

-- Now, alter the procedure to use RECOMPILE

ALTER PROC dbo.rptStoreSales

@StoreKey INTEGER

WITH RECOMPILE

SELECT ProductName, SUM(TotalCost) AS TotalSales

FROM dbo.FactOnlineSales os

JOIN dbo.dimProduct p ON os.ProductKey=p.ProductKey

WHERE StoreKey=@StoreKey

GROUP BY ProductName

-- Give it a run.

EXEC dbo.rptStoreSales @StoreKey=199;

-- Now check out the details.

SELECT ( SELECT TOP 1 SUBSTRING(st.text, statement_start_offset / 2 + 1,

( ( CASE WHEN statement_end_offset = -1

THEN ( LEN(CONVERT(NVARCHAR(MAX), st.text))* 2 )

ELSE statement_end_offset

END ) - statement_start_offset ) / 2 + 1) )AS sql_statement,

st.[text] AS [sql_batch], ps.execution_count, ps.creation_time, ps.last_execution_time,

ps.total_elapsed_time, ps.total_worker_time, ps.total_logical_reads,

ps.total_physical_reads, ps.total_logical_writes, ps.plan_generation_num,

qp.query_plan

FROM sys.dm_exec_query_stats ps

CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) AS st

OUTER APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp

WHERE (st.text LIKE '%SELECT ProductName, SUM(TotalCost) AS TotalSales%' OR st.text LIKE '%dbo.rptStoreSales%')

AND st.text NOT LIKE '%sys.dm_exec_query_stats%';

-- What if we move the RECOMPILE hint?

ALTER PROC dbo.rptStoreSales

@StoreKey INTEGER

SELECT ProductName, SUM(TotalCost) AS TotalSales

FROM dbo.FactOnlineSales os

JOIN dbo.dimProduct p ON os.ProductKey=p.ProductKey

WHERE StoreKey=@StoreKey

GROUP BY ProductName OPTION (RECOMPILE);

--Give it a run. Three times.

EXEC dbo.rptStoreSales @StoreKey=199;

--Now check out the details in the cache.

SELECT ( SELECT TOP 1 SUBSTRING(st.text, statement_start_offset / 2 + 1,

( ( CASE WHEN statement_end_offset = -1

THEN ( LEN(CONVERT(NVARCHAR(MAX), st.text))* 2 )

ELSE statement_end_offset

END ) - statement_start_offset ) / 2 + 1) )AS sql_statement,

st.[text] AS [sql_batch], ps.execution_count, ps.creation_time, ps.last_execution_time,

ps.total_elapsed_time, ps.total_worker_time, ps.total_logical_reads,

ps.total_physical_reads, ps.total_logical_writes, ps.plan_generation_num,

qp.query_plan

FROM sys.dm_exec_query_stats ps

CROSS APPLY sys.dm_exec_sql_text(ps.sql_handle) AS st

OUTER APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp

WHERE (st.text LIKE '%SELECT ProductName, SUM(TotalCost) AS TotalSales%' OR st.text LIKE '%dbo.rptStoreSales%')

AND st.text NOT LIKE '%sys.dm_exec_query_stats%';

-- Compare Execution Count and Plan Generation Number

Why Your SQL Server’s Network Connection Matters

Five Things That Fix Bad SQL Server Performance

7 Comments. Leave new

GulliMeel
June 27, 2012 12:36 pm

Nice.I used a proc in Sybase. This proc was fired almost every second and it was running in around 2 seconds but this was also high and thus I looked what was going on and there was a statement which was cauisng the proc to recompile (in sybase there is no statement level recompile but if populate temp table and then create the index on table then all the code after that create index statement is recompiled) and the proc was very big and referencing lots of tables. After putting the create index statement before populating the data it reduced the run time from 2 s to 350ms on average.

Reply
Abhiram. Chitipirlla (Raam )
July 27, 2013 8:07 pm

Hi Kendra

First thing , I really appreciate the way your team bringing SQL awareness.

In this video, you mentioned ; Plan cache will get truncated each time we restart SQL Server. What is the case in clustering environment where maintenance(SAN , Windows patching, SQL Patching) takes place every 10 -15 days ? At this point, SQL Server services will move from one node to another each time we do a fail over.

Does SQL plan cache get truncate each time we fail-over?

Reply
- Brent Ozar
  July 28, 2013 4:33 am
  
  Abhiram – yes, the SQL Server instance restarts when you fail over, so the plan cache will get cleared out.
  
  Reply
Stefan
March 17, 2015 10:00 am

You have a typo in the youtube embed link, so the video does not show up correctly.
Also the audio quality of the video is a bit low, hardly understandable.
However, keep up the good work!

Reply
- Kendra Little
  March 17, 2015 12:27 pm
  
  Thanks Stefan– the link looks OK to me, so I think someone may have fixed this already.
  
  Thanks for the note on the sound. This is back from when we recorded live webcasts that we simultaneously broadcasted, and the sound quality was pretty poor for the recordings with that system. I’ll put this on my list of topics that would be good to re-record with better quality.
  
  Reply
iahmed
November 21, 2023 6:08 pm

The is not playing both chrome and on edge.

Reply
iahmed
November 21, 2023 6:08 pm

I mean the Video* is not able to play

Reply