Thanks Craig!

Thanks Grant ....that did it!!!

THANKS TO EVERYONE THAT HELPED ON THIS THREAD!!

Good karma to you all..

If any of you are in s.florida ...I'll buy you a beer....or two..

for statistics - can I just run exec sp_updatestats?

not sure if this helps but I created same proc called myproc2...and ran that using same acount number and got the same...

I cleared the...

wouldn't the dbcc freeproccache command clear out the cache

and therefore clear out any parameter sniffing issue?

thats the wierd thing there is no redical difference in hardware...

I've even tried restoring on to my sandbox (small client machine)...using the backup from the night before..and it takes two...

if this was parameter sniffing .

wouldn't I see scans in the execution plan?

I don't see scans...all seeks..the execution plan matches the one in stage which runs fast..

thanks for all your help

I haven't tried moving the variables yet...this particular proc has 24 input params and is kind of a bitch...(I didn't write it)... 🙂

I flushed the cache and re-ran the proc using same parameters..I can't make it run slowly on my stage server...I tried changing params and no matter which I choose it...

Not sure if this is really a parameter sniffing issue...

I added the 'with RECOMPILE' option to the end of my proc

Declare @nRC int, @rnSQLError int

exec @nRC = psp_x_hist_inq ...

okay...I setup a job to clear the cache lat tonight...

thanks for all your help...hope it works

okay I ran this :

SELECT cp.plan_handle, st.[text]

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE [text] LIKE N'%/* psp %';

and it found two plan handles...that look like my proc

now...

wierd thing is if I change to a different accoutn number then everything returns fast

same query in prod.. same everything..if I change the account number to someone elses it's fast...

yep checked for all those....doesn't seem to be waiting on anything..

and this prod server isn't heavily used...

I also tried

while (1=1)

select waitime,waittype,lastwaittype from sysprocesses where SPID=<the query SPID> where waittime...

Yeah sorry....the execution plans are identical on both stage and production...

but return times are 2 seconds on stage....3minutes in prod.