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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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; 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 |
5 Comments. Leave new
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.
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?
Abhiram – yes, the SQL Server instance restarts when you fail over, so the plan cache will get cleared out.
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!
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.