May 21, 2009 at 12:53 pm
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
May 24, 2009 at 5:14 am
Hey Nick,
It's really difficult to give more than general advice based on what you have provided, so:
Take a look at http://support.microsoft.com/kb/308737 for an approach you might take.
Plan guides and USE PLAN hints are for SELECT only in 2K5.
You can use OPTION(KEEP PLAN) and OPTION (KEEPFIXED PLAN) to reduce the number of compiles for optimization reasons. This will not affect compiles for correctness for example.
As you probably suspect, it is more likely the design of the queries themselves (DISTINCT + NOT IN are not a happy combination!) than compilations, but see how you go.
Paul
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply