I found the answer my self. The value 262144 represents a plan that has "SET ROWCOUNT" > 0.

NP demands an "authenticated network connection". You can try to see if you can connect with explorer to the SQL server as you say that the app's windows account has...

I also want to add following to above:

Change the primary key clustered index to non-clustered and create a new one on START_DT.

I interpreted this as create a new primary key...

Just a follow-up to setopts & 2 = 2

USE master

EXEC sp_configure 'show advanced option', '1'

EXEC sp_configure 'max degree of parallelism','1'

reconfigure

This will lead to a setopts column of 0. IE setopts...

The problem is just that Microsoft is reluctant to publish this.

We have to wait until they understands the need for official documentation.

Or that we get an official explanation of this...

This Post is lengthy (it includes a long script)

We have a lot of different business logic in our triggers.

I first need to thank you for trying to find a solution....

Thank you for the suggestions.

We have gone through the syscacheobjects table. Note that the setopts values are not completely documented for SQL 2000.

And this problem arises because a trigger has...

To minimize the recompile time your suggestion would perhaps work. We already have code that loops for executing SP's and some other code.

If Microsoft does not understand the performance...

No, the problem is not the code that makes the update. The trigger objects will recompile regardless of what object is doing the update.

From our point of view:

SQL server have...

A temporarily solution could following code change be:

select @cnt = count(*) from #a, fh where

...

if @cnt between 1 and 4

begin

...

The problem that we are facing is an unnecessary recompile of up to 5 seconds in certain situations. This is mostly due to the fact that we have most of...

Example script:

set nocount on

go

if object_id('cache_test') is not null

drop table cache_test

go

create table cache_test

(

id int not null,

descr varchar(30) not null,

rowcreateddt datetime not null default getdate(),

rowcreatedby varchar(30) not null default

right(system_user,30),

testcolumn varchar(30) null,

primary key...