# Database Discussions > Microsoft SQL Server 2005 >  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.

----------

