Lowell (3/4/2011)

---

the other option isusing locally declared variables. then the compiler cannot use parameter sniffing;

something like

CREATE PROCEDURE AVOIDSNIFFING(@Param1 int=0,@Param2 datetime = NULL)

AS

DECLARE @localParam1 int,

...

Anyone?

Lowell (3/4/2011)

---

definitely my first instinct, the parameter sniffing issue, is what i would check first. what you are describing is a classic symptom.

does your stored procedure have default values for...

We are selecting only few columns. Do the above SET options could be an issue?

I am pretty sure it does. Did...

Leo.Miller (3/1/2011)

---

The RPC protocol is used when a client calls a procedure on a server. In SQL terms typically when an application server makes a procedure call against the database...

Dave Ballantyne (3/1/2011)

---

Just to add to Lowell's list , different SET options can cause different query plans to be created.

But 9 times out of 10 ( or even more) this...

thanks everyone. I would really like to have a performance tuning specialist look at our environment , however we are aware that our SQL code is not written the right...

i really need some assistance here. I am not able to find the parent object. Can someone please advice me? I am capturing data using trace, i can get the...

Lowell (3/1/2011)

---

is your code a stored procedure which runs quickly when static values are tested in SSMS, but slow when it is called with parameters from the application?

That's a sign...

DO you know of any good affordable performance tuning consultants?

thanks lowell..But i was refering to a parent object in sql trace. Lets say you have a proc inside that you have another proc with view,functions etc. Inside function...

one way to test this would be, detach one of the db's and see if the other one is still available or not.

Thanks Jeff. I am very positive memory is not an issue, how can i measure if AUTO stats is causing an issue?

I have both the options turned ON. My understanding was that i have a table of 12 millions records and if the data in table changes upto 20% then statistics...