Parameter Sniffing problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Parameter Sniffing problem

Author

Topic

shubhada
Posting Yak Master

117 Posts

Posted - 2007-02-26 : 05:05:35

Hi,
Is somebody will give me some idea about the parameter sniffing?
What is parameter sniffing??
How to overcome with this problem???
I have one procedure that is causing this problem.
Plz give me some tips to modify this proc to overcome this problem

dbo.cm_test (
@fromdate datetime = '01/01/1900',
@thrudate datetime = '12/31/9999'
)
As
Begin
SET ANSI_WARNINGS OFF
set nocount on
declare @minln smallint,
@CID char(24),
@EID numeric(12,0),
@extractdate datetime

/* Begin gathering data */
select @extractdate = getdate()
/* Begin gathering data */
if exists (select * from tempdb..sysobjects where name like '#tmpWTClm%')
drop table #tmpWTClm

create table #tmpWTClm (
ClaimID char(24) not null,
EventID numeric(12,0) not null)

insert into #tmpWTClm
select Rem.ClaimID, Rem.EID
from Rem, RemOut2
where Rem.CID= RemOut2.CIDand
Rem.EID= RemOut2.EIDand
(ReimProcessDate between @fromdate and @thrudate)

insert into #tmpWTClm
select distinct ClaimID, EID
from PaymentsDetail
where ProcessDate between @fromdate and @thrudate

insert into #tmpWTClm
select distinct ClaimID, EID
from ClaimDenials
where DenialProcessDate between @fromdate and @thrudate

insert into #tmpWTClm
select distinct ClaimID, EID
from ClaimAppeals
where AppealProcessDate between @fromdate and @thrudate

declare ClmDataCursor cursor for
select distinct ClaimID, EventID
from #tmpWTClm

open ClmDataCursor
fetch ClmDataCursor into @claimid, @EID

while (@@fetch_status = 0)
begin
insert into WTDenialAppealExtract
select distinct
from Rem, Rem1
where Rem.CID= Rem1.CIDand
Rem.EID= Rem1.EIDand
Rem.CID= @CIDand
Rem.EID= @eventid

fetch ClmDataCursor into @CID , @EID
end

close ClmDataCursor
Deallocate ClmDataCursor

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-26 : 05:18:16

quote:
Is somebody will give me some idea about the parameter sniffing?
What is parameter sniffing??

found from google for "parameter sniffing"
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

quote:
How to overcome with this problem???
I have one procedure that is causing this problem.
Plz give me some tips to modify this proc to overcome this problem

You have to tell us what problem is this ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-26 : 09:21:49

You can use SQL Profiler with SP template to see which parameters that are passed to the stored procedures.

Peter Larsson
Helsingborg, Sweden

pootle_flump

1064 Posts

Posted - 2007-03-01 : 08:03:53

quote:
Originally posted by shubhada

Hi,
Is somebody will give me some idea about the parameter sniffing?
What is parameter sniffing??
How to overcome with this problem???
I have one procedure that is causing this problem.
Plz give me some tips to modify this proc to overcome this problem

Hi

I thought I recognised you from DBF
http://www.dbforums.com/showthread.php?t=1615508

I think you are going about this the wrong way round. If you are not sure what parameter sniffing is then how can you think that it is the cause of the problem? Parameter Sniffing is just one (of many) possible causes of performance problems. Start with the problem and work back to the cause.

Anyway - when SQL Server creates an execution plan it takes account of the values of the parameters at the time of creating the plan. Subsequent calls to the procedure will (typically) use that same execution plan irrespective of the parameter values. This can cause performance problems if the execution plan is not efficient for sets of parameters that are being passed to the procedure.

You can eliminate parameter sniffing pretty easily - recompile the procedure and run it again. If it still performs poorly then parameter sniffing is not the problem. If it does not the parameter sniffing may be the explanation.

I haven't read over your proc in detail but I don't see that cursor as necessary nor efficient BTW.

HTH

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-01 : 09:16:01

I also think 3 of your insert statements could be merged into one...(with "or" clauses in the WHERE statement)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources