Stored procedure and query plans different

[Thomas R. Hummel]

I have a stored procedure that suddenly started performing horribly.
The query plan didn't look right to me, so I copy/pasted the code and
ran it (it's a single SELECT statement). That ran pretty well and used
a query plan that made sense. Now, I know what you're all thinking...
stored procedures have to optimize for variable parameters, etc.
Here's what I've tried to fix the issue:

1. Recompiled the stored procedure
2. Created a new, but identical stored procedure
3. Created the stored procedure with the RECOMPILE option
4. Created the stored procedure with a hard-coded value instead of
aparameter
5. Changed the stored procedure to use dynamic SQL

In every case, performance did not improve and the query plan remained
the same (I could not easily confirm this with the dynamic SQL
version, but performance was still horrible).

I am currently running UPDATE STATISTICS on all of the involved
tables, but that will take awhile.

Any ideas?

Thanks!
-Tom.

[Erland Sommarskog]

Thomas R. Hummel (to********@hot mail.com) writes:

I have a stored procedure that suddenly started performing horribly.
The query plan didn't look right to me, so I copy/pasted the code and
ran it (it's a single SELECT statement). That ran pretty well and used
a query plan that made sense. Now, I know what you're all thinking...
stored procedures have to optimize for variable parameters, etc.
Here's what I've tried to fix the issue:

1. Recompiled the stored procedure
2. Created a new, but identical stored procedure
3. Created the stored procedure with the RECOMPILE option
4. Created the stored procedure with a hard-coded value instead of
aparameter
5. Changed the stored procedure to use dynamic SQL

In every case, performance did not improve and the query plan remained
the same (I could not easily confirm this with the dynamic SQL
version, but performance was still horrible).

I would guess that the query depends on an indexed view or an indexed
computed columns, and you are creating stored procedures from Enterprise
Manager or OSQL that has QUOTED_IDENTIFI ER off by default.

This setting is saved with the stored procedure, and this setting must
be on, for SQL Server to use indexes on views and computed columns.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Thomas R. Hummel]

Excellent suggestion, but that is not the case. The select statement is
from tables only, none of which use computed columns.

Just to be safe, I recreated the stored procedure with QUOTED_IDENTIFI ER
on and it did not have any effect on the query plan. I also tried saving
it with ANSI NULLS set to both ON and OFF and neither had an effect.

Last night I was able to update the statistics on all of the tables.
That also had no effect. At the moment I am out of ideas :(

Thanks,

-----------------
Thomas R. Hummel
Senior Database Administrator

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Thomas R. Hummel (to************ **@hotmail.com) writes:

Excellent suggestion, but that is not the case. The select statement is
from tables only, none of which use computed columns.

Just to be safe, I recreated the stored procedure with QUOTED_IDENTIFI ER
on and it did not have any effect on the query plan. I also tried saving
it with ANSI NULLS set to both ON and OFF and neither had an effect.

Last night I was able to update the statistics on all of the tables.
That also had no effect. At the moment I am out of ideas :(

Not seeing the code or plans it's difficult to say. But how does the
bad plan look like? Does it involve parallellism? In that case an
OPTION (MAXDOP 1) may do the trick.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Thomas R. Hummel]

Sorry for not including this sooner. The query statement and plan are
below. Hopefully the text will be readable in your newsreader or you can
easily cut and paste it.

There is a clustered index on Lead_Action_His tory.report_dat e.
Lead_Master, Report_Group, Report_Month, Import_Batch, and
Project_Disposi tions are all joined on primary keys.
Project_Disp_Co nfig_Detail is joined using columns that make up the
first two columns of the primary key, which is also clustered.
Lead_Master has about 4.6M records. Lead_Action_His tory has about 22.5M
records. For the report_date in question there are just over 65K
records. All of the other tables are very small.

As a reminder... the code only uses the plan below when it is run within
the stored procedure. Running it directly in QA uses a more efficient
query plan, also included below.

I tried setting the MAXDOP option to 1 and there was no substantial
change. The parallelism in the below plan was on a pretty insignificant
part of the query.

Thanks!

QUERY:

SELECT RM.report_month _abrev + ' ' + RG.report_Group _descr [Lead
Type],
LAH.calldisp AS [Call Disposition],
CASE WHEN LEN(IsNull(PD.f disp_id, '')) = 0 THEN ' ' ELSE
IsNull(PD.fdisp _id, '') END AS [FDisp],
CASE WHEN LEN(IsNull(PDCD .output_code, '')) = 0 THEN ' '
ELSE IsNull(PDCD.out put_code, '') END AS [Output Code],
COUNT(*) AS [Count],
CAST(PD.calldis p_descr AS VARCHAR(30)) AS [Calldisp
Description]
FROM Lead_Action_His tory LAH WITH (NOLOCK)
INNER JOIN Lead_Master LM WITH (nolock) ON LAH.lead_seq = LM.lead_seq
INNER JOIN Mercury.dbo.Rep ort_Group RG WITH (nolock) ON
RG.report_group _seq = LM.report_group _seq
INNER JOIN Mercury.dbo.Rep ort_Month RM WITH (nolock) ON
RG.report_month = RM.report_month _seq
INNER JOIN Import_Batch IB WITH (nolock) ON IB.import_batch _seq =
LM.import_batch _seq AND IB.batch_type = 'P'
INNER JOIN Mercury.dbo.Pro ject_Dispositio ns PD WITH (nolock) ON
PD.project_seq = 10440
AND
PD.calldisp = LAH.calldisp
INNER JOIN Mercury.dbo.Pro ject_Disp_Confi g_Detail PDCD WITH (nolock)
ON PDCD.project_se q = 10440

AND PDCD.calldisp = LAH.calldisp
WHERE report_date = @in_report_date
AND LAH.calldisp IS NOT NULL
GROUP BY RM.report_month _abrev + ' ' + RG.report_Group _descr,
LAH.calldisp,
PD.fdisp_id,
PD.calldisp_des cr,
PDCD.output_cod e
PLAN WHEN RUN WITHIN STORED PROCEDURE:

StmtText
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1009]=If
(len(isnull([PD].[FDISP_ID], ''))=0) then ' ' else
Convert(isnull([PD].[FDISP_ID], '')), [Expr1010]=If
(len(isnull([PDCD].[OUTPUT_CODE], ''))=0) then ' ' else
isnull([PDCD].[OUTPUT_CODE], ''),
[Expr1011]=Convert([PD].[CALLDISP_DESCR])))
|--Compute Scalar(DEFINE:([Expr1008]=Convert([Expr1032])))
|--Stream Aggregate(GROUP BY:([Expr1007],
[LAH].[calldisp], [PD].[FDISP_ID], [PD].[CALLDISP_DESCR],
[PDCD].[OUTPUT_CODE]) DEFINE:([Expr1032]=Count(*)))
|--Sort(ORDER BY:([Expr1007] ASC, [LAH].[calldisp]
ASC, [PD].[FDISP_ID] ASC, [PD].[CALLDISP_DESCR] ASC,
[PDCD].[OUTPUT_CODE] ASC))
|--Compute
Scalar(DEFINE:([Expr1007]=[RM].[report_month_ab rev]+'
'+[RG].[report_group_de scr]))
|--Parallelism(Gat her Streams)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([PDCD].[CALLDISP]))

|--Filter(WHERE:([LAH].[calldisp]=[PDCD].[CALLDISP]))
| |--Bookmark
Lookup(BOOKMARK :([Bmk1000]), OBJECT:([ALWOB].[dbo].[Lead_Action_His tory]
AS [LAH]))
| |--Nested
Loops(Inner Join, OUTER REFERENCES:([LM].[lead_seq]) WITH PREFETCH)
| |--Nested
Loops(Inner Join)
| |
|--Filter(WHERE:([IB].[batch_type]='P'))
| | |
|--Bookmark Lookup(BOOKMARK :([Bmk1004]),
OBJECT:([ALWOB].[dbo].[Import_Batch] AS [IB]))
| | |
|--Nested Loops(Inner Join, OUTER REFERENCES:([LM].[import_batch_se q]))
| | |
|--Bookmark Lookup(BOOKMARK :([Bmk1001]),
OBJECT:([ALWOB].[dbo].[Lead_Master] AS [LM]))
| | |
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([RG].[report_group_se q]))
| | |
| |--Hash Match(Inner Join,
HASH:([RM].[report_month_se q])=([RG].[report_month]),
RESIDUAL:([RG].[report_month]=[RM].[report_month_se q]))
| | |
| | |--Parallelism(Bro adcast)
| | |
| | | |--Table Scan(OBJECT:([Mercury].[dbo].[Report_Month]
AS [RM]))
| | |
| | |--Parallelism(Dis tribute Streams)
| | |
| | |--Clustered Index
Scan(OBJECT:([Mercury].[dbo].[Report_Group].[PK_REPORT_GROUP] AS [RG]))
| | |
| |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1029],
[Expr1030], [Expr1031]))
| | |
| |--Compute
Scalar(DEFINE:([Expr1029]=Convert([RG].[report_group_se q])-1,
[Expr1030]=Convert([RG].[report_group_se q])+1, [Expr1031]=If
(Convert([RG].[report_group_se q])-1=NULL) then 0 else 6|If
(Convert([RG].[report_group_se q])+1=NULL) then 0 else 10))
| | |
| | |--Constant Scan
| | |
| |--Index
Seek(OBJECT:([ALWOB].[dbo].[Lead_Master].[LeadMaster_IDX0 4] AS [LM]),
SEEK:([LM].[report_group_se q] > [Expr1029] AND [LM].[report_group_se q] <
[Expr1030]),
WHERE:([RG].[report_group_se q]=Convert([LM].[report_group_se q])) ORDERED
FORWARD)
| | |
|--Index Seek(OBJECT:([ALWOB].[dbo].[Import_Batch].[pk_import_batch] AS
[IB]), SEEK:([IB].[import_batch_se q]=[LM].[import_batch_se q]) ORDERED
FORWARD)
| |
|--Clustered Index
Seek(OBJECT:([Mercury].[dbo].[PROJECT_DISP_CO NFIG_DETAIL].[ProjectDispCo
nfigDetail_PK] AS [PDCD]), SEEK:([PDCD].[PROJECT_SEQ]=10440) ORDERED
FORWARD)
| |--Index
Seek(OBJECT:([ALWOB].[dbo].[Lead_Action_His tory].[LeadActionHisto ry_IDX0
3] AS [LAH]), SEEK:([LAH].[lead_seq]=[LM].[lead_seq] AND
[LAH].[report_date]=[@in_report_date]) ORDERED FORWARD)
|--Clustered Index
Seek(OBJECT:([Mercury].[dbo].[PROJECT_DISPOSI TIONS].[PK_PROJECT_DISP OSIT
IONS] AS [PD]), SEEK:([PD].[CALLDISP]=[PDCD].[CALLDISP] AND
[PD].[PROJECT_SEQ]=10440) ORDERED FORWARD)
-----------------------------------------------------------

PLAN WHEN RUN OUTSIDE STORED PROCEDURE:

StmtText
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------------------------------------------------
-
|--Compute Scalar(DEFINE:([Expr1009]=If (len(isnull([PD].[FDISP_ID],
''))=0) then '&nbsp;' else Convert(isnull([PD].[FDISP_ID], '')),
[Expr1010]=If (len(isnull([PDCD].[OUTPUT_CODE], ''))=0) then '&nbsp;'
else isnull([PDCD].[OUTPUT_CODE], ''),
[Expr1011]=Convert([PD].[CALLDISP_DESCR])))
|--Compute Scalar(DEFINE:([Expr1008]=Convert([Expr1027])))
|--Stream Aggregate(GROUP BY:([Expr1007], [LAH].[calldisp],
[PD].[FDISP_ID], [PD].[CALLDISP_DESCR], [PDCD].[OUTPUT_CODE])
DEFINE:([Expr1027]=Count(*)))
|--Sort(ORDER BY:([Expr1007] ASC, [LAH].[calldisp] ASC,
[PD].[FDISP_ID] ASC, [PD].[CALLDISP_DESCR] ASC, [PDCD].[OUTPUT_CODE]
ASC))
|--Compute
Scalar(DEFINE:([Expr1007]=[RM].[report_month_ab rev]+'
'+[RG].[report_group_de scr]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([PDCD].[CALLDISP]))
|--Filter(WHERE:([IB].[batch_type]='P'))
| |--Bookmark
Lookup(BOOKMARK :([Bmk1004]), OBJECT:([ALWOB].[dbo].[Import_Batch] AS
[IB]))
| |--Nested Loops(Inner Join,
OUTER REFERENCES:([LM].[import_batch_se q]))
| |--Hash Match(Inner Join,
HASH:([RM].[report_month_se q])=([RG].[report_month]),
RESIDUAL:([RG].[report_month]=[RM].[report_month_se q]))
| | |--Table
Scan(OBJECT:([Mercury].[dbo].[Report_Month] AS [RM]))
| | |--Nested
Loops(Inner Join, OUTER REFERENCES:([LM].[report_group_se q]))
| | |--Nested
Loops(Inner Join, OUTER REFERENCES:([LAH].[lead_seq]) WITH PREFETCH)
| | | |--Hash
Match(Inner Join, HASH:([PDCD].[CALLDISP])=([LAH].[calldisp]),
RESIDUAL:([LAH].[calldisp]=[PDCD].[CALLDISP]))
| | | |
|--Clustered Index
Seek(OBJECT:([Mercury].[dbo].[PROJECT_DISP_CO NFIG_DETAIL].[ProjectDispCo
nfigDetail_PK] AS [PDCD]), SEEK:([PDCD].[PROJECT_SEQ]=10440) ORDERED
FORWARD)
| | | |
|--Clustered Index
Seek(OBJECT:([ALWOB].[dbo].[Lead_Action_His tory].[LeadActionHisto ry_IDX0
2] AS [LAH]), SEEK:([LAH].[report_date]=[@in_report_date]) ORDERED
FORWARD)
| | |
|--Clustered Index
Seek(OBJECT:([ALWOB].[dbo].[Lead_Master].[pk_lead_master] AS [LM]),
SEEK:([LM].[lead_seq]=[LAH].[lead_seq]) ORDERED FORWARD)
| | |--Clustered
Index Seek(OBJECT:([Mercury].[dbo].[Report_Group].[PK_REPORT_GROUP] AS
[RG]), SEEK:([RG].[report_group_se q]=Convert([LM].[report_group_se q]))
ORDERED FORWARD)
| |--Index
Seek(OBJECT:([ALWOB].[dbo].[Import_Batch].[pk_import_batch] AS [IB]),
SEEK:([IB].[import_batch_se q]=[LM].[import_batch_se q]) ORDERED FORWARD)
|--Clustered Index
Seek(OBJECT:([Mercury].[dbo].[PROJECT_DISPOSI TIONS].[PK_PROJECT_DISP OSIT
IONS] AS [PD]), SEEK:([PD].[CALLDISP]=[PDCD].[CALLDISP] AND
[PD].[PROJECT_SEQ]=10440) ORDERED FORWARD)
-----------------
Thomas R. Hummel
Manager, Data Systems
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Thomas R. Hummel (to************ **@hotmail.com) writes:

Sorry for not including this sooner. The query statement and plan are
below. Hopefully the text will be readable in your newsreader or you can
easily cut and paste it.

Admittedly it would have helped if the showplan text would not have
been wrapped. It would maybe have been better if you had placed it in an
attachment.

It would also have helped if you had included the complete query batch
and the complete stored procedure. After all, we are looking for the
differences between the two.

So I make another shot from the hip: in the query batch you have:

DECLARE @recorddate datetime
SELECT @recorddate = <value>
-- query follows here.

And in the stored procedure you have @recorddate as a parameter. This means
that in the query batch, the optimizer is blind to the value, while for the
procedure it is not. And this time, the optimizer makes a better choice
of plans when it does not know the value.

Looking at the query and the plans, it appers that the optimizer has two
choices: take all matching 65000 rows in Lead_Action_His tory and from
there look up Lead_Master for each of them. Or try to narrow down the rows
in Lead_Master from the other tables, and then approach Lead_Action_His tory
from Lead_Master.

The performance tells us that of these two approaches, the first is the
one to go. I don't know if you ran UPDATE STATISTICS WITH FULLSCAN, and
I don't know if it would help.

You can of course do the trick of hiding the parameter by copying it to a
local parameter in the procedure, but since the query appears to balance
between two plans, it's only a matter of time, until you get bad performance
again.

In that case an (INDEX = 1) for Lead_Action_His tory to force the clustered
index, is probably a better option, although that too could backfire the
day the other plan really is the better one.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[MissLivvy]

4. Created the stored procedure with a hard-coded value instead of
aparameter

I have had this problem before when in the WHERE clause a datetime column
was being compared with a datetime variable. Hardcoding the datetime into
the query fixed the query plan and proved this was the problem. But you say
you tried this?

[Thomas R. Hummel]

Your assumptions on the stored procedure and the query batch were
correct. The batch was simply, DECLARE, SET, query, while the stored
procedure used a paramater and consisted of SET NOCOUNT ON and the
query, using the parameter.

I did an UPDATE STATISTICS WITH FULLSCAN on Sunday when we had some
downtime and it made no difference. Also, I think that I mentioned this,
but I tried hard-coding the paramater value rather than passing anything
and that did not affect the query plan chosen, so I don't think that it
is a matter of binding the value in the batch. I also tried using a
local variable and setting that to the parameter value, again with no
change in the query plan.

I've also tried setting all of the connection specific options that I
could think of (ANSI_NULLS, QUOTED_IDENTIFI ER, etc.) specifically during
the creation of the stored procedure and in the batch so that I could be
sure that they were the same in both cases. That also had no effect.

Lastly... I also tried using an optimized hint of INDEX=1 and while that
did change the query plan slightly, it was still very much different
from the plan used with the batch and it was also very inefficient.

Thanks for all of your help on this one. Still has me baffled though :(

-----------------
Thomas R. Hummel
Senior Database Administrator
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Thomas R. Hummel]

>> 4. Created the stored procedure with a hard-coded value instead of

aparameter

I have had this problem before when in the WHERE clause a datetime column was being compared with a datetime variable. Hardcoding the datetime into the query fixed the query plan and proved this was the problem. But you say you tried this?

Yes, that was indeed something that I tried.

-----------------
Thomas R. Hummel
Senior Database Administrator
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Thomas R. Hummel (to************ **@hotmail.com) writes:

Lastly... I also tried using an optimized hint of INDEX=1 and while that
did change the query plan slightly, it was still very much different
from the plan used with the batch and it was also very inefficient.

Too bad to hear. But without more information about the case, it's
difficult for me to come with any more suggestions.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp