January 17, 2014 at 12:31 pm
We have run into this scenario. We have a query that refers to a table with some 250K rows with 10+ joins to other tables. Indexes have been created to the "join" columns and mostly are identity columns even though a few are datetime. When we execute the query it takes about 4 minutes to produce 350 rows. Too slow. Tried adding OPTION (RECOMPILE) and execution time was 7 seconds.
Can somebody explain the behavior and point in the right direction to avoid having to use RECOMPILE since obviously degrades the performance of the server?
Thanks in advance.
Raul
January 17, 2014 at 2:16 pm
Without seeing the query, no.
Could be parameter sniffing, could be lack of parameter sniffing, could be better row estimates on table variables, could be related to catch-all queries, could be a pile of other things
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2014 at 4:05 am
I'm right there with Gail. Not seeing the code, there's just not enough to go on. Heck, you may have just had contention the first time you ran the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 19, 2014 at 11:11 am
I'm with the other two (as I am 99.x% of the time).
I will add that you can find out a LOT by yourself by doing 2 things:
1) running sp_whoisactive while the long-running version is running and see what it exposes.
2) show the ACTUAL execution plan for both runs and pay close attention to the various ESTIMATED and ACTUAL row counts in the different parts of the execution plan. My money is on this being the mechanism by which OPTION (RECOMPILE) makes the difference.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 19, 2014 at 5:03 pm
Raul Undreiner (1/17/2014)
We have run into this scenario. We have a query that refers to a table with some 250K rows with 10+ joins to other tables. Indexes have been created to the "join" columns and mostly are identity columns even though a few are datetime. When we execute the query it takes about 4 minutes to produce 350 rows. Too slow. Tried adding OPTION (RECOMPILE) and execution time was 7 seconds.Can somebody explain the behavior and point in the right direction to avoid having to use RECOMPILE since obviously degrades the performance of the server?
Thanks in advance.
Raul
BWAAA-HAAAA!!!!... let's see now... RECOMPILE made some monster all-in-one code that used to run in 4 minutes only take 7 seconds and you say you want to avoid RECOMPILE "since obviously degrades the performance of the server"?
I'd recommend that you take the "hit" and move on.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 20, 2014 at 6:36 am
Hi.
I've run into situations where a non-optimal execution plan is created when the SProc is first compiled. The plans all look wonderful.
This is probably because I put the SProc out in production, ran a quick test on it, then release it for use which might require millions of rows. I'm still researching that.
Consecutive runs use that plan and perform very poorly. A recompile might take the SProc from 6 minutes down to 5 seconds. But the Plan is NOT replaced by the recompile. The non-optimal continues to exist. If you can flush your plan cache with DBCC FreeProcCache or FlushProcInDB or reboot, you should find the proper Plan is created next full use. Obviously FreeProcCache will hurt a production system while all the plans are rebuilt.
January 20, 2014 at 6:45 am
It's called parameter sniffing. Plans ARE replaced when you recompile.
Recompile either removes the plan from cache on the spot forcing a new compile the next time, or marks the underlying objects as changed meaning plans get discarded and recreated the next time the procedure is executed. Which one it is depends whether you recompile the procedure (gives you the first) or a table which the procedure uses (results in the second)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2014 at 8:01 am
I expected the recompile to replace the plan or mark it obsolete, but that's not what I saw.
Running select...from sys.dm_exec_query_stats..Xapply sys.dm_exec_sql_text where object_name()='xxxx' and dbname='yyyy' showed me the plans before running, running, after recompile, and flush and what I saw was the original plan stuck around and unless "RECOMPILE" was run each time, the SProc would default to picking up the old plan (indicated by use count & poor performance)
January 20, 2014 at 8:07 am
You'd need to explain more what you did for me to be able to say what happened there.
exec sp_recompile 'procedure name' removes that plan from cache completely. I have a blog post that shows it's behaviour if you want me to find it.
exec sp_recompile 'table name' marks the table as having changed so all plans using it recompile on their next execution. The recompilation doesn't set usecount to 0.
EXEC procedure WITH RECOMPILE just creates a new plan for that execution, a plan which is never cached, it doesn't remove any existing plan (as per Books Online)
CREATE PROCEDURE ProcedureName WITH RECOMPILE means that the procedure's plan is never cached at all, so recompiled on every execution
SELECT .... OPTION(RECOMPILE) means that the individual query's plan is never cached and so is recompiled on every execution
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 20, 2014 at 8:46 am
My apologies to the OP for the thread hijack.
Should I start a new one?
"... ProcedureName WITH RECOMPILE means that the procedure's plan is never cached at all"
That's documented in BOL? But it explains what I saw.
What I expected:
Create the SP, no plan yet exists
Run it once, the plan is created
Run the SP again, cached plan is used.
run it with the OPTION RECOMPILE I expected the old plan to be deactivated and the recompiled one to takes its place.
Something else I saw: I have a twin processor 6 core box so 24 virtual processors.
I frequently see 6 plans created every time the system decides to create new plans, but not always.
I presume this is tied to parallel processing on one of the processors?
January 20, 2014 at 8:48 am
GilaMonster (1/20/2014)
You'd need to explain more what you did for me to be able to say what happened there.exec sp_recompile 'procedure name' removes that plan from cache completely. I have a blog post that shows it's behaviour if you want me to find it.
exec sp_recompile 'table name' marks the table as having changed so all plans using it recompile on their next execution. The recompilation doesn't set usecount to 0.
EXEC procedure WITH RECOMPILE just creates a new plan for that execution, a plan which is never cached, it doesn't remove any existing plan (as per Books Online)
CREATE PROCEDURE ProcedureName WITH RECOMPILE means that the procedure's plan is never cached at all, so recompiled on every execution
SELECT .... OPTION(RECOMPILE) means that the individual query's plan is never cached and so is recompiled on every execution
Awesome list, Gail! That's going into my reference library.
I would note that as far as seeing a plan in the plan cache, there are a lot of factors that affect plan reuse, including a wide array of SET options (one older 2008R2 reference is http://technet.microsoft.com/en-us/library/ms188722%28v=sql.105%29.aspx Query tuning recommendations - this is a common issue when two different [sources of] sessions run the same code and get different plans/execution speeds.
All of the SET options that need to be identical for query plan reuse are listed in the below SQL as "(PLAN REUSE)":
-- Originally from http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/
-- Verified against http://technet.microsoft.com/en-us/library/ms190763.aspx for SQL 2012
-- (PLAN REUSE) per http://technet.microsoft.com/en-us/library/ms188722%28v=sql.105%29.aspx which applies to SQL 2005 thorugh 2008R2 (there is no SQL 2012 version)
DECLARE @options INT
DECLARE @msg VARCHAR(8000)
SET @options = @@OPTIONS
PRINT '@@OPTIONS: ' + CONVERT(VARCHAR(11),@options)
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK: ON' ELSE PRINT 'DISABLE_DEF_CNST_CHK: OFF'
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS: ON' ELSE PRINT 'IMPLICIT_TRANSACTIONS: OFF'
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT: ON' ELSE PRINT 'CURSOR_CLOSE_ON_COMMIT: OFF'
IF ( (8 & @options) = 8 ) PRINT '(PLAN REUSE) ANSI_WARNINGS: ON' ELSE PRINT '(PLAN REUSE) ANSI_WARNINGS: OFF'
IF ( (16 & @options) = 16 ) PRINT '(PLAN REUSE) ANSI_PADDING: ON' ELSE PRINT '(PLAN REUSE) ANSI_PADDING: OFF'
IF ( (32 & @options) = 32 ) PRINT '(PLAN REUSE) ANSI_NULLS: ON' ELSE PRINT '(PLAN REUSE) ANSI_NULLS: OFF'
IF ( (64 & @options) = 64 ) PRINT '(PLAN REUSE) ARITHABORT: ON' ELSE PRINT '(PLAN REUSE) ARITHABORT: OFF'
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE: ON' ELSE PRINT 'ARITHIGNORE: OFF'
IF ( (256 & @options) = 256 ) PRINT '(PLAN REUSE) QUOTED_IDENTIFIER: ON' ELSE PRINT 'QUOTED_IDENTIFIER: OFF'
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT: ON' ELSE PRINT 'NOCOUNT: OFF'
IF ( (1024 & @options) = 1024 ) PRINT '(PLAN REUSE) ANSI_NULL_DFLT_ON: YES' ELSE PRINT '(PLAN REUSE) ANSI_NULL_DFLT_ON: NO'
IF ( (2048 & @options) = 2048 ) PRINT '(PLAN REUSE) ANSI_NULL_DFLT_OFF: YES' ELSE PRINT '(PLAN REUSE) ANSI_NULL_DFLT_OFF: NO'
IF ( (4096 & @options) = 4096 ) PRINT '(PLAN REUSE) CONCAT_NULL_YIELDS_NULL: ON' ELSE PRINT '(PLAN REUSE) CONCAT_NULL_YIELDS_NULL: OFF'
IF ( (8192 & @options) = 8192 ) PRINT '(PLAN REUSE) NUMERIC_ROUNDABORT: ON' ELSE PRINT '(PLAN REUSE) NUMERIC_ROUNDABORT: OFF'
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT: ON' ELSE PRINT 'XACT_ABORT: OFF'
PRINT 'DATEFIRST: ' + CONVERT(CHAR(1),@@DATEFIRST)
/*
--Alternate to sys.dm_exec_sessions
IF EXISTS (SELECT * FROM tempdb.sys.objects so WHERE so.object_id = object_id(N'[tempdb].[dbo].[#DBCCUseroptions]') AND so.type = 'U' AND so.is_ms_shipped = 0)
DROP TABLE [dbo].[#DBCCUseroptions]
CREATE TABLE #DBCCUseroptions
( [Set option] SYSNAME
,Value SYSNAME
)
INSERT INTO #DBCCUserOptions
EXEC('DBCC USEROPTIONS')
SELECT '(PLAN REUSE) ' + [Set option] + ': ' + Value FROM #DBCCUserOptions WHERE [Set Option] = 'dateformat'
select * from #DBCCUserOptions
*/
SELECT @msg = '(PLAN REUSE) DATEFORMAT: ' + date_format FROM sys.dm_exec_sessions WHERE session_id = @@spid
PRINT @MSG
-- Forceplan comes later with some truly nasty code
SELECT @msg = '(PLAN REUSE) LANGUAGE: ' + language FROM sys.dm_exec_sessions WHERE session_id = @@spid
PRINT @MSG
-- Forceplan comes later with some truly nasty code
SELECT @msg = '(PLAN REUSE) ANSI_DEFAULTS: ' + CASE WHEN ansi_defaults = 1 THEN 'ON' ELSE 'OFF' END FROM sys.dm_exec_sessions WHERE session_id = @@spid
PRINT @MSG
SELECT @msg = '(PLAN REUSE) TEXTSIZE: ' + CONVERT(VARCHAR(11),text_size) FROM sys.dm_exec_sessions WHERE session_id = @@spid
PRINT @MSG
-- As of late 2013, the only way I could find to determine the current session values of FORCEPLAN and NO_BROWSETABLE is to actually create a stored procedure and see what values it was created with.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[DELETE_THIS_PROCEDURE_IF_YOU_SEE_IT_PURELY_FOR_SESSION_TESTING_jbupqmco11Qq4iZlk9NhSHxVwyyrfn4Ts3RzcSR0rdHYU7CqbER2RfGuabtBQjMoB]') AND OBJECTPROPERTY(object_id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[DELETE_THIS_PROCEDURE_IF_YOU_SEE_IT_PURELY_FOR_SESSION_TESTING_jbupqmco11Qq4iZlk9NhSHxVwyyrfn4Ts3RzcSR0rdHYU7CqbER2RfGuabtBQjMoB]
EXEC
('
CREATE PROCEDURE [dbo].[DELETE_THIS_PROCEDURE_IF_YOU_SEE_IT_PURELY_FOR_SESSION_TESTING_jbupqmco11Qq4iZlk9NhSHxVwyyrfn4Ts3RzcSR0rdHYU7CqbER2RfGuabtBQjMoB]
AS
PRINT ''''
')
EXEC [dbo].[DELETE_THIS_PROCEDURE_IF_YOU_SEE_IT_PURELY_FOR_SESSION_TESTING_jbupqmco11Qq4iZlk9NhSHxVwyyrfn4Ts3RzcSR0rdHYU7CqbER2RfGuabtBQjMoB]
IF EXISTS (SELECT * FROM tempdb.sys.objects so WHERE so.object_id = object_id(N'[tempdb].[dbo].[#PlanOptions]') AND so.type = 'U' AND so.is_ms_shipped = 0)
DROP TABLE [dbo].[#PlanOptions]
SELECT plan_handle, usecounts, CONVERT(INT,pvt.set_options) AS set_options
,CASE WHEN (4 & CONVERT(INT,pvt.set_options)) = 4 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS forceplan
,CASE WHEN (512 & CONVERT(INT,pvt.set_options)) = 512 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS No_Browsetable
,CASE WHEN (4096 & CONVERT(INT,pvt.set_options)) = 4096 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS arithabort
INTO #PlanOptions
FROM (
SELECT plan_handle, usecounts, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE cacheobjtype = 'Compiled Plan') AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "objectid")) AS pvt
WHERE pvt.objectid = OBJECT_ID('dbo.DELETE_THIS_PROCEDURE_IF_YOU_SEE_IT_PURELY_FOR_SESSION_TESTING_jbupqmco11Qq4iZlk9NhSHxVwyyrfn4Ts3RzcSR0rdHYU7CqbER2RfGuabtBQjMoB')
DROP PROCEDURE [dbo].[DELETE_THIS_PROCEDURE_IF_YOU_SEE_IT_PURELY_FOR_SESSION_TESTING_jbupqmco11Qq4iZlk9NhSHxVwyyrfn4Ts3RzcSR0rdHYU7CqbER2RfGuabtBQjMoB]
SELECT @msg = 'Compiled Plan set_options: ' + CONVERT(VARCHAR(11),set_options) FROM #PlanOptions
PRINT @msg
SELECT @msg = '(PLAN REUSE) FORCEPLAN: ' + CASE WHEN forceplan = 1 THEN 'ON' ELSE 'OFF' END from #PlanOptions
PRINT @msg
SELECT @msg = '(PLAN REUSE) NO_BROWSETABLE: ' + CASE WHEN no_browsetable = 1 THEN 'ON' ELSE 'OFF' END from #PlanOptions
PRINT @msg
Regarding DBCC FREEPROCCACHE, clyde_mcmurdy, note that you can give it a plan_handle or a sql_handle and have it clear only only the rows you want - it doesn't have to be all or nothing once you're at SQL Server 2008 or above. Reference: http://technet.microsoft.com/en-us/library/ms174283.aspx
January 20, 2014 at 8:49 am
clyde_mcmurdy (1/20/2014)
My apologies to the OP for the thread hijack.Should I start a new one?
"... ProcedureName WITH RECOMPILE means that the procedure's plan is never cached at all"
That's documented in BOL? But it explains what I saw.
What I expected:
Create the SP, no plan yet exists
Run it once, the plan is created
Run the SP again, cached plan is used.
run it with the OPTION RECOMPILE I expected the old plan to be deactivated and the recompiled one to takes its place.
Something else I saw: I have a twin processor 6 core box so 24 virtual processors.
I frequently see 6 plans created every time the system decides to create new plans, but not always.
I presume this is tied to parallel processing on one of the processors?
yes, start a new thread please
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 20, 2014 at 2:17 pm
clyde_mcmurdy (1/20/2014)
My apologies to the OP for the thread hijack.Should I start a new one?
"... ProcedureName WITH RECOMPILE means that the procedure's plan is never cached at all"
That's documented in BOL?
EXECUTE (Transact-SQL)
WITH RECOMPILE
Forces a new plan to be compiled, used, and discarded after the module is executed. If there is an existing query plan for the module, this plan remains in the cache.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply