Blog

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

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.1 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.1 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.1 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
↑ Back to top
  1. 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.

  2. Pingback: Something for the Weekend - SQL Server Links 29/06/12

  3. Pingback: Recompile Option – Yes, No, Maybe? « The SQL Corner

  4. Pingback: Things You Can Find In The Plan Cache | DB NewsFeed

  5. Pingback: Things You Can Find In The Plan Cache - SQL Server Blog - SQL Server - Telligent

  6. 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?

  7. Pingback: Prevent creating stored procedures with RECOMPILE hint – Policy Based Management is your friend ! | SQLSailor

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php