Hi Lynn,

I did include the DDL for the table directly in the post... I was asking kind of a generic question about the approach to the problem, not really specific...

Do you have sysadmin permissions?

Look at this error:

Msg 208, Level 16, State 1, Procedure AHCAuditAllObjects, Line 17

Invalid object name 'amOperations.dbo.DatabaseObjectAudit'.

the only way you are getting that error is if a...

Do you have sysadmin level permissions on the server? somewhere a proc named AHCAuditAllObjects exists, or you would not be getting that error.

select * from sys.objects where name='AHCAuditAllObjects'

run that across all your databases... did you find this proc? Use the GUI to "find dependencies"... did you find a trigger?

What's this object: AHCAuditAllObjects

do you have a trigger based audit in place or something like that?

Yep- the only thing that's a little suspect is that this has happened 3 times on 3 consecutive Mondays after updating stats and/or reindexing. It's hard to believe that...

I think what happened was what you initially suggested- after the recompile, it compiled based on the first call made. I'm guessing that call had a outlier value for...

thanks again. will have to wait until the next time it runs (probably Monday) to get the compile time options.

It does choose the "good" plan with Optimize...

On second thought, I guess your explanation is more likely than mine (though I really cant figure out why it would choose the other index and always the right one...

Thanks for the reply. I'm skeptical about it being a case of a "normal" scenario where it's choosing a plan thats appropriate for the first parameter executed after the...

that tool is cool, pretty much what I'm looking for- thank you!

You dont understand- I dont want to create the linked servers every day. I want to generate the scripts every day... so that if new linked servers are...

That's what I'm after, but I want to automate that... Instead of right clicking, I want to genereate the scripts via SQL Agent job every morning. Essentially, I'm looking...

Figured this out... the problem is a missing statistic. Stat updates will cause recompiles for plans that use those statistics. If stats are updated and the plan persists,...

Did you ever get an answer on this? I am seeing a similar behavior. For example:

1.) Table has few rows

2.) query table (plan gets compiled)

3.) insert millions of...