I have a query that generates two very different execution plans based on the parameter passed to the predicate. I'll just post the exact query below with the execution plans. I'm baffled, because the subsets of data are very, very similar.Here is the DDL:CREATE TABLE [dbo].[app_Exclusions]( [customer_id] [varchar](50) NOT NULL, [transmission_id] [int] NOT NULL, [original_policy_term_number] [varchar](50) NOT NULL, [term_effective_date] [datetime] NOT NULL, [exclusion_reason] [varchar](700) NOT NULL, CONSTRAINT [PK_app_Exclusions] PRIMARY KEY CLUSTERED ( [original_policy_term_number] ASC, [term_effective_date] ASC, [transmission_id] ASC, [customer_id] ASC, [exclusion_reason] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]CREATE NONCLUSTERED INDEX [IX_app_Exclusions] ON [dbo].[app_Exclusions]( [transmission_id] ASC, [exclusion_reason] ASC)INCLUDE ( [customer_id], [original_policy_term_number], [term_effective_date]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Here is the query:SELECT COUNT(*) as sole_ex_count, e.exclusion_reasonFROM app_Exclusions e INNER JOIN (SELECT customer_id, transmission_id, original_policy_term_number, term_effective_date FROM app_Exclusions WHERE transmission_id = 36 GROUP BY customer_id, transmission_id, original_policy_term_number, term_effective_date HAVING COUNT(*) = 1) s ON e.transmission_id = s.transmission_id AND e.customer_id = s.customer_id AND e.original_policy_term_number = s.original_policy_term_number AND e.term_effective_date = s.term_effective_dateWHERE e.transmission_id = 36GROUP BY e.exclusion_reason;
When I pass in 35, or really any number other than 36, then this runs in less than 1 sec. When I pass in 36 it takes 9 minutes. The data for 35 and 36 are almost identical.First the execution plan with transmission_id = 35 this one takes less than a second to execute.I'll try this pasting the results in as Text:StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------SELECT COUNT(*) as sole_ex_count, e.exclusion_reasonFROM app_Exclusions e INNER JOIN (SELECT customer_id, transmission_id, original_policy_term_number, term_effective_date FROM app_Exclusions WHERE transmission_id = 35 GROUP BY customer 1 1 0 NULL NULL 1 NULL 16.4129 NULL NULL NULL 1.179751 NULL NULL SELECT 0 NULL |--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1012],0))) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1012],0)) [Expr1006]=CONVERT_IMPLICIT(int,[Expr1012],0) 16.4129 0 0.008915122 39 1.179751 [e].[exclusion_reason], [Expr1006] NULL PLAN_ROW 0 1 |--Stream Aggregate(GROUP BY:([e].[exclusion_reason]) DEFINE:([Expr1012]=Count(*))) 1 3 2 Stream Aggregate Aggregate GROUP BY:([e].[exclusion_reason]) [Expr1012]=Count(*) 16.4129 0 0.008915122 39 1.179751 [e].[exclusion_reason], [Expr1012] NULL PLAN_ROW 0 1 |--Filter(WHERE:([Expr1005]=(1))) 1 4 3 Filter Filter WHERE:([Expr1005]=(1)) NULL 14844.86 0 2.886908E-06 35 1.170835 [e].[exclusion_reason] NULL PLAN_ROW 0 1 |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0))) 1 5 4 Compute Scalar Compute Scalar DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0)) [Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0) 6.014392 0 6.799933E-06 39 1.170833 [e].[exclusion_reason], [Expr1005] NULL PLAN_ROW 0 1 |--Stream Aggregate(GROUP BY:([e].[exclusion_reason], [e].[original_policy_term_number], [e].[term_effective_date], [e].[customer_id]) DEFINE:([Expr1011]=Count(*))) 1 6 5 Stream Aggregate Aggregate GROUP BY:([e].[exclusion_reason], [e].[original_policy_term_number], [e].[term_effective_date], [e].[customer_id]) [Expr1011]=Count(*) 6.014392 0 6.799933E-06 39 1.170833 [e].[exclusion_reason], [Expr1011] NULL PLAN_ROW 0 1 |--Sort(ORDER BY:([e].[exclusion_reason] ASC, [e].[original_policy_term_number] ASC, [e].[term_effective_date] ASC, [e].[customer_id] ASC)) 1 7 6 Sort Sort ORDER BY:([e].[exclusion_reason] ASC, [e].[original_policy_term_number] ASC, [e].[term_effective_date] ASC, [e].[customer_id] ASC) NULL 6.321229 0.01126126 0.0001262936 61 1.170826 [e].[customer_id], [e].[original_policy_term_number], [e].[term_effective_date], [e].[exclusion_reason] NULL PLAN_ROW 0 1 |--Hash Match(Inner Join, HASH:([InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id], [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number], [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effec 1 8 7 Hash Match Inner Join HASH:([InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id], [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number], [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_date])=([e].[customer_id], [e].[original_policy_term_ NULL 6.321229 0 0.9493557 61 1.159438 [e].[customer_id], [e].[original_policy_term_number], [e].[term_effective_date], [e].[exclusion_reason] NULL PLAN_ROW 0 1 |--Index Seek(OBJECT:([InsureRight_Scoring].[dbo].[app_Exclusions].[IX_app_Exclusions]), SEEK:([InsureRight_Scoring].[dbo].[app_Exclusions].[transmission_id]=(35)) ORDERED FORWARD) 1 9 8 Index Seek Index Seek OBJECT:([InsureRight_Scoring].[dbo].[app_Exclusions].[IX_app_Exclusions]), SEEK:([InsureRight_Scoring].[dbo].[app_Exclusions].[transmission_id]=(35)) ORDERED FORWARD [InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id], [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number], [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_date] 14866.5 0.08852612 0.01651015 35 0.1050363 [InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id], [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number], [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_date] NULL PLAN_ROW 0 1 |--Index Seek(OBJECT:([InsureRight_Scoring].[dbo].[app_Exclusions].[IX_app_Exclusions] AS [e]), SEEK:([e].[transmission_id]=(35)) ORDERED FORWARD) 1 10 8 Index Seek Index Seek OBJECT:([InsureRight_Scoring].[dbo].[app_Exclusions].[IX_app_Exclusions] AS [e]), SEEK:([e].[transmission_id]=(35)) ORDERED FORWARD [e].[customer_id], [e].[original_policy_term_number], [e].[term_effective_date], [e].[exclusion_reason] 14866.5 0.08852612 0.01651015 61 0.1050363 [e].[customer_id], [e].[original_policy_term_number], [e].[term_effective_date], [e].[exclusion_reason] NULL PLAN_ROW 0 1
Now for transmission_id 36:StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------SELECT COUNT(*) as sole_ex_count, e.exclusion_reasonFROM app_Exclusions e INNER JOIN (SELECT customer_id, transmission_id, original_policy_term_number, term_effective_date FROM app_Exclusions WHERE transmission_id = 36 GROUP BY customer 1 1 0 NULL NULL 1 NULL 1 NULL NULL NULL 0.01793536 NULL NULL SELECT 0 NULL |--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1012],0))) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1006]=CONVERT_IMPLICIT(int,[Expr1012],0)) [Expr1006]=CONVERT_IMPLICIT(int,[Expr1012],0) 1 0 1.1E-06 39 0.01793536 [e].[exclusion_reason], [Expr1006] NULL PLAN_ROW 0 1 |--Stream Aggregate(GROUP BY:([e].[exclusion_reason]) DEFINE:([Expr1012]=Count(*))) 1 3 2 Stream Aggregate Aggregate GROUP BY:([e].[exclusion_reason]) [Expr1012]=Count(*) 1 0 1.1E-06 39 0.01793536 [e].[exclusion_reason], [Expr1012] NULL PLAN_ROW 0 1 |--Nested Loops(Inner Join, WHERE:([InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id] as [e].[customer_id]=[InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id] AND [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term 1 4 3 Nested Loops Inner Join WHERE:([InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id] as [e].[customer_id]=[InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id] AND [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number] as [e].[original_policy_term_nu NULL 1 0 4.18E-06 35 0.01793426 [e].[exclusion_reason] NULL PLAN_ROW 0 1 |--Index Seek(OBJECT:([InsureRight_Scoring].[dbo].[app_Exclusions].[IX_app_Exclusions] AS [e]), SEEK:([e].[transmission_id]=(36)) ORDERED FORWARD) 1 5 4 Index Seek Index Seek OBJECT:([InsureRight_Scoring].[dbo].[app_Exclusions].[IX_app_Exclusions] AS [e]), SEEK:([e].[transmission_id]=(36)) ORDERED FORWARD [e].[customer_id], [e].[original_policy_term_number], [e].[term_effective_date], [e].[exclusion_reason] 1 0.003125 0.0001581 61 0.0032831 [e].[customer_id], [e].[original_policy_term_number], [e].[term_effective_date], [e].[exclusion_reason] NULL PLAN_ROW 0 1 |--Filter(WHERE:([Expr1005]=(1))) 1 6 4 Filter Filter WHERE:([Expr1005]=(1)) NULL 1 0 4.8E-07 35 0.01464598 [InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id], [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number], [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_date] NULL PLAN_ROW 0 1 |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0))) 1 7 6 Compute Scalar Compute Scalar DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0)) [Expr1005]=CONVERT_IMPLICIT(int,[Expr1011],0) 1 0 1.1E-06 39 0.0146455 [InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id], [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number], [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_date], [Expr1005] NULL PLAN_ROW 0 1 |--Stream Aggregate(GROUP BY:([InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id], [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number], [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_da 1 8 7 Stream Aggregate Aggregate GROUP BY:([InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id], [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number], [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_date]) [Expr1011]=Count(*) 1 0 1.1E-06 39 0.0146455 [InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id], [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number], [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_date], [Expr1011] NULL PLAN_ROW 0 1 |--Sort(ORDER BY:([InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id] ASC, [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number] ASC, [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_d 1 9 8 Sort Sort ORDER BY:([InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id] ASC, [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number] ASC, [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_date] ASC) NULL 1 0.01126126 0.000100035 35 0.0146444 [InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id], [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number], [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_date] NULL PLAN_ROW 0 1 |--Index Seek(OBJECT:([InsureRight_Scoring].[dbo].[app_Exclusions].[IX_app_Exclusions]), SEEK:([InsureRight_Scoring].[dbo].[app_Exclusions].[transmission_id]=(36)) ORDERED FORWARD) 1 10 9 Index Seek Index Seek OBJECT:([InsureRight_Scoring].[dbo].[app_Exclusions].[IX_app_Exclusions]), SEEK:([InsureRight_Scoring].[dbo].[app_Exclusions].[transmission_id]=(36)) ORDERED FORWARD [InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id], [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number], [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_date] 1 0.003125 0.0001581 35 0.0032831 [InsureRight_Scoring].[dbo].[app_Exclusions].[customer_id], [InsureRight_Scoring].[dbo].[app_Exclusions].[original_policy_term_number], [InsureRight_Scoring].[dbo].[app_Exclusions].[term_effective_date] NULL PLAN_ROW 0 1
The big difference is on the SORT in plan 2.--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware
Getting different execution plans on same query
Posted - 2012-03-20 : 13:33:16
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.