Wierd one..

Question

Wierd one..

Viewing 15 posts - 31 through 45 (of 60 total)

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

Wildcat SSCarpal Tunnel Points: 4774 More actions · Answer 1

Grant Fritchey (10/22/2010)
It sounds like a parameter sniffing issue. A quick fix would be to recompile the procedure on prod and see if the plan changes.

I use a local variable inside proc to prevent the sniffing. 😎

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 2

I think you have more number of rows of data in your prod tables than your stage server.

This is what the issue the tables you use in your store proc may have more data in production .

Always consider the tables using in the store proc

Step wise tuning I follow

--> Execution plan

--> Indexes

--> Code bugs

--> Data in tables.

--> hope fully

Thanks,

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Oksana March SSChasing Mays Points: 603 More actions · Answer 3

So did you ever find out why the same thing ran slow on one server and fast on another?

And could someone please explain what you mean by "parameter sniffing"? Thanks!

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

Oksana March (10/23/2010)
So did you ever find out why the same thing ran slow on one server and fast on another?
And could someone please explain what you mean by "parameter sniffing"? Thanks!

There must be 1000 posts on this site about that. Just search for it and you'll find all you need to know.

WayneS SSC Guru Points: 95460 More actions · Answer 5

Oksana March (10/23/2010)
And could someone please explain what you mean by "parameter sniffing"? Thanks!

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

krypto69 SSChampion Points: 13604 More actions · Answer 6

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 'D','00400003414', 'CK',null, null, null,null,null,null,null,null,null,null, 'DP','10/20/2010', 0, Y,2000,Y,152,0,@rnSQLError output, 'null', 'Y'WITH RECOMPILE

I also did a dbcc freeproccache

nothing helped..

do you guys still think it's a parameter sniffing issue?

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

Yes. Or more to the point, the wrong compiled plan is generated when using those parameters.

Assuming you saved the correct plan. Flush the proc cache from your test server and see what happens when you rerun the proc with the same parameters that are giving the correct plan on that server.

You may be able to reproduce the change.

Also another possible explaination. Check to see if you really have 100% identical setups (sql versions, patches, and close enough hardware).

krypto69 SSChampion Points: 13604 More actions · Answer 8

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 runs fast on both stage and my test server....but not in production..

There is a difference in SQL versions...

Production - is SQL 2005 Enterprise version...where my stage and test servers are both SQL05 standard ... both have service pack 2.

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

I'm out of ideas at this point... I'll call in reinforcement.

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

krypto69 (10/25/2010)
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 runs fast on both stage and my test server....but not in production..
There is a difference in SQL versions...
Production - is SQL 2005 Enterprise version...where my stage and test servers are both SQL05 standard ... both have service pack 2.

Have you tried moving the parameters into local variables to see if that fixed it?

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

WayneS (10/23/2010)
Oksana March (10/23/2010)
And could someone please explain what you mean by "parameter sniffing"? Thanks!
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]

What have you tried in there? I've never seen that fail so far?

krypto69 SSChampion Points: 13604 More actions · Answer 12

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

krypto69 SSChampion Points: 13604 More actions · Answer 13

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 14

Have statistics been updated on both servers? The indexes rebuilt the same way? Besides the version of sQL Server, is there more/less memory, faster/slower processors, more/fewer disk drives, more/fewer drive controllers...

It has to be something.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 15

krypto69 (10/25/2010)
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..

If scans/seeks/whatever, if the data is identical and the plans are identical, then you should see roughly the same performance... unless, you've got contention or there's a radical difference in the hardware.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning