Wierd one..

Question

Wierd one..

Viewing 15 posts - 16 through 30 (of 60 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Grant Fritchey (10/22/2010)
Oops. I spend most of my time in 2008 these days. I tend to bleed that stuff back down the chain sometimes.

No sweat, I'm glad I learned this was possible now. I had to flush the whole cache on prod more than once with all the side effects that come from it!

krypto69 SSChampion Points: 13604 More actions · Answer 2

already used with recompile...no luck

can't drop cause it's in use..

I created a new proc (myproc_2) which was a copy of the existing query and then ran against that...still slow...

any other ideas?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Have you tried running it with different parameters to try to get the "fast" plan back?

Once you get it back you can always use plan guides or always force that guide to stay in cache by dropping the wrong one and replacing with the correct plan.

krypto69 SSChampion Points: 13604 More actions · Answer 4

Didn't think about using plan guides....

I'll read up on it..

I have the plan from the stage server..can I force the prod server to use that plan?

Although the plans appear to be identical on both stage and production..

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

krypto69 (10/21/2010)
okay so I followed your advise ...ran the execution plan on both prod and stage boxes..
On my prod box (query 4) I have a clustered index scan..cost of 81 percent
I don't have that on my stage box..

Did anything change since that post?

A different plan means a different plan period. There's no grey twilight zone here.

krypto69 SSChampion Points: 13604 More actions · Answer 6

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Do a simple test in your test proc on prod.

Add with no lock or readuncommitted.

That will tell you if you are experiencing locking.

You way also be waiting on disks, cpu or ram to complete the query (in order of the most likely).

krypto69 SSChampion Points: 13604 More actions · Answer 8

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 >0

nothing...

krypto69 SSChampion Points: 13604 More actions · Answer 9

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...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

It still means different plan.

For sql server those are 4 distinct queries :

exec dbo.spProc

exec spProc

exec Dbo.spProc

exec dbo.SPPROC

Depending on what the parameters of the first call were, that's the plan that's in use.

That's why the trick to drop the plan an recreate it with "normal" parameters is effective.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Also if you are using dynamic sql, different accounts mean different permissions hence possibly different "best" plans.

At the very least it can be considered 2 distinct requests if you forgot to qualify the object name with the owner.

krypto69 SSChampion Points: 13604 More actions · Answer 12

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 I'm guessing I need to clear these out? and then run the proc again to create a new plan?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Actually I'd save both for further analysis and backup.

Then I'd clear only the bad one and see if everything is fixed.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Ninja's_RGR'us (10/22/2010)
Actually I'd save both for further analysis and backup.
Then I'd clear only the bad one and see if everything is fixed.

Except that you're on 2005 and I have no solution to offer to you do that other than flushing all the plans for that db.

Which I'd advise doing OFF hours unless the server is really, really unbusy at the moment.

krypto69 SSChampion Points: 13604 More actions · Answer 15

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

thanks for all your help...hope it works