Tuning for Excessive recompiles using in SQL Server 2005

[nduckste]

I recently started as a DBA for a new company. We're experiencing high CPU usage on our production server. As best I can tell it is due to excessive recompiles in a handful of store procedures. We have separate databases for each of our clients so whenever a stored proc behaves poorly it does so across all our client databases (several hundred).

Most resource intensive queries in terms of both high CPU and least plan-reuse are one and the same queries. That's how I get to the conclusion that recompiles is the problem. I've already tuned the stored procs so that they compile 3 times instead of 8. The remaining statements causing recompiles are as follows:

• insert into #temp6 select distinct ...delete from #temp6 where detail_key = ''
• delete from dbo.xxx_distinct where detail_key not in ( select distinct detail_key from #temp6 )
• insert into dbo.xxx_distinct ( detail_key, detail_type ) select detail_key, data_type from #temp6 where data_type = 'number' ...

Everything I read tells me that in SQL Server 2005 the way to tune these queries is by using Plan Guides. However, everything I read also:

• Only has examples for SELECT statements. Can plan guides be used for INSERT, DELETE, and UPDATE statements?
• Seems to suggest that using plan guides for #temp tables seems prohibitive. Am I missing something there?

I realize there is some bad logic in some of these procs but I can't fix all those right away. Seems like the quickest way "out of the woods" in terms of performance is to tune the procs so they stop causing recompiles without changing functionality. Then I'll have some breathing room to deal with rewriting code.

Thanks in advance

[rmiao]

How often do you update stats?

[nduckste]

Stats get updated nightly. The jobs that run these procs run every couple minutes and the data in the underlying tables will have changed as well.

[rmiao]

Tried with plan guide?

[nduckste]

Will they help if the recompile is due to an insert, update, or delete on a temp table? None of the examples I can find discuss the use of plan guides on anything but SELECTs and never anything about using them on temp tables.