Is it possible to save and force a plan?

[tracy@guisolutions.com]

I have a complex query (16 table join) that until five days ago took 30
sec to run. It now takes eight hours. I restored a backup of the
database from five days ago and the query plans are completely
different for the same exact query. Five days ago the final estimated
row count is 1.6M now it is 1.7E+10 OUCH! The amount of data added in
those five days is insignificant (percentage wise). I rebuilt all the
indexes using DBCC DBREINDEX with no change in the plan or results. Is
there anyway to save the query plan (from the backup) and run it
against the new data?

TIA,
Tracy

[Erland Sommarskog]

Re: Is it possible to save and force a plan?

(tracy@guisolut ions.com) writes:[color=blue]
> I have a complex query (16 table join) that until five days ago took 30
> sec to run. It now takes eight hours. I restored a backup of the
> database from five days ago and the query plans are completely
> different for the same exact query. Five days ago the final estimated
> row count is 1.6M now it is 1.7E+10 OUCH! The amount of data added in
> those five days is insignificant (percentage wise). I rebuilt all the
> indexes using DBCC DBREINDEX with no change in the plan or results. Is
> there anyway to save the query plan (from the backup) and run it
> against the new data?[/color]

No. The query plan exists in cache only.

If the query plan includes parallelism, try adding OPTION(MAXDOP 1) at
the end of the query - that may do wonders.

Another thing to try is UPDATE STATISTICS WITH FULLSCAN on all tables
(or at least the table where the optimizer goes astray).

While the amount of data added in these five days are insignificant as
such, you may have come over some threashold where the optimizer makes
a different decision. Or maybe the problem is that statistics were
updated in these five days, and the optimizer caught up with reality -
and unfortunately took the wrong path.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Damien]

Re: Is it possible to save and force a plan?

If you are looking to force the same types of joins/merges, then it is
possible to force the system to use the old join/merge types using a
lot of join hints and OPTION FORCE ORDER (see BOL).

However, this does result in an EXTREMELY brittle query - any slight
change to the underlying data may mean that you are performing your
query in the most pessimal manner.

I probably wouldn't recommend this approach, unless you're happy to
hand optimize the query every time anything changes in the underlying
data - better to look at the stats, the indexes, etc, and leave the
query optimizer to do it's job.

[tracy@guisolutions.com]

Re: Is it possible to save and force a plan?

Thanks for suggestions but unfortunately neither one helped. There was
no parallelism in the original query and OPTION(MAXDOP 1) did not
change the plan. I updated all statistics:

SET NOCOUNT ON
DECLARE @Table char(64)
DECLARE IndexCursor
CURSOR FOR SELECT name
FROM sysobjects
WHERE type='U'
AND uid=5
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @Table
WHILE @@FETCH_STATUS= 0 BEGIN
EXEC ('UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN')
FETCH NEXT FROM IndexCursor
INTO @Table
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
GO

I made a seemingly silly change to the query that bought it down to one
hour. I added the EXISTS statement to the WHERE clause that mimics the
INNER JOIN in the SELECT statement. See below:

SET NOCOUNT ON
SELECT 1 AS source_id,
faxagency.tCont racts.iContract ID AS contract_id,
2 AS status_id,
'Agent' AS payeetype,
faxagency.tAgen ts.iAgentID AS payee_id,
-1 AS paymenttype_id,
1 AS feetype_id,
0 AS issubagent,
faxagency.tCont ractTermAgentDe alerFeeAmounts. fPrimaryAgentFe e*faxagency.tCa ncellations.fPe rcentage*-1
AS Amount,
'Agent',
faxagency.tCanc ellations.iCanc ellationID
FROM faxagency.tBatc hs
INNER JOIN faxagency.tCont racts ON faxagency.tCont racts.iBatchNum ber =
faxagency.tBatc hs.iBatchNumber
INNER JOIN faxagency.tCanc ellations ON
faxagency.tCont racts.iContract ID = faxagency.tCanc ellations.iCont ractID

INNER JOIN faxagency.tCont ractFeeAdjustme nts ON
faxagency.tCont ractFeeAdjustme nts.iContractID =
faxagency.tCont racts.iContract ID
INNER JOIN faxagency.tPlan s ON faxagency.tPlan s.iPlanID =
faxagency.tCont racts.iPlanID
INNER JOIN faxagency.tPlan Types ON faxagency.tPlan Types.iPlanType ID =
faxagency.tPlan s.iPlanTypeID
INNER JOIN faxagency.tPlan ContractTerms ON
faxagency.tPlan ContractTerms.i PlanContractTer mID =
faxagency.tCont racts.iPlanCont ractTermID
INNER JOIN faxagency.tTPAs ON faxagency.tTPAs .iTPAID =
faxagency.tPlan s.iTPAID
INNER JOIN faxagency.tCont ractTerms ON
faxagency.tCont ractTerms.iCont ractTermID =
faxagency.tPlan ContractTerms.i ContractTermID
INNER JOIN faxagency.tDeal erCustomers ON
faxagency.tDeal erCustomers.iDe alerCustomerID =
faxagency.tCont racts.iDealerCu stomerID
INNER JOIN faxagency.tCont ractTermAgentDe alers ON
faxagency.tCont ractTermAgentDe alers.iDealerID =
faxagency.tDeal erCustomers.iDe alerID
INNER JOIN faxagency.tCont ractTermAgents ON

faxagency.tCont ractTermAgents. iContractTermAg entID =
faxagency.tCont ractTermAgentDe alers.iContract TermAgentID AND
faxagency.tCont ractTermAgents. iPlanContractTe rmID
= faxagency.tPlan ContractTerms.i PlanContractTer mID
INNER JOIN faxagency.tCont ractTermAgentDe alerFeeAmounts ON

faxagency.tCont ractTermAgentDe alerFeeAmounts. iContractTermAg entDealerID
= faxagency.tCont ractTermAgentDe alers.iContract TermAgentDealer ID AND

faxagency.tCont ractTermAgentDe alerFeeAmounts. dStartDate <=
faxagency.tCont racts.dSaleDate AND

faxagency.tCont ractTermAgentDe alerFeeAmounts. dEndDate >=
faxagency.tCont racts.dSaleDate

INNER JOIN faxagency.tAgen ts ON faxagency.tCont ractTermAgents. iAgentID
= faxagency.tAgen ts.iAgentID
INNER JOIN faxagency.tDeal ers ON faxagency.tBatc hs.iDealerID =
faxagency.tDeal ers.iDealerID
INNER JOIN faxagency.tMont hs ON faxagency.tTPAs .iTPAID =
faxagency.tMont hs.iTPAID
WHERE EXISTS (SELECT 1 FROM faxagency.tCanc ellations WHERE
faxagency.tCanc ellations.iCont ractID =
faxagency.tCont racts.iContract ID)
AND ((faxagency.tCo ntractTermAgent DealerFeeAmount s.fPrimaryAgent Fee <>
0) OR
(faxagency.tCon tractTermAgentD ealerFeeAmounts .iSubAgentID1 = 0
AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID2 = 0
AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID3 = 0
AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID4 = 0
AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID5 = 0))
AND (faxagency.tBat chs.bActive = 1)
AND (faxagency.tCon tracts.bActive = 1)
AND (faxagency.tPla ns.bUsePercenta ges = 0 AND faxagency.tPlan s.bActive
= 1)
AND (faxagency.tPla nContractTerms. bAssigned = 1)
AND (faxagency.tCon tractTerms.bAct ive = 1)
AND (faxagency.tCon tractTermAgentD ealerFeeAmounts .bAssigned = 1 )
AND (faxagency.tCan cellations.bAct ive = 1 and
faxagency.tCanc ellations.dProc essDate BETWEEN
faxagency.tMont hs.dStartDate AND faxagency.tMont hs.dEndDate)

One hour is still unacceptable. If I could execute the old plan (from
five days ago) against the new data I bet it would still run around 30
sec. Very frustrating.

[tracy@guisolutions.com]

Re: Is it possible to save and force a plan?

Thanks for the suggestion. I thought of that but had the same concerns
that you have. It will become my full-time job.

[Gert-Jan Strik]

Re: Is it possible to save and force a plan?

Tracy,

The query you posted has a lot of literals. This is ideal for SQL-Server
and should yield the optimal (fast) query plan.

However, if you are not actually using an ad-hoc query with literals,
but for example a stored procedure with parameters or a stored procedure
with variables, then it is a different matter, and SQL-Server will
behave differently.

From the information you posted, there is not much to say about it (no
DDL, no query plan). Since adding the extra EXISTS clause helpt you a
lot, you could also add the following extra clause to see if that makes
any difference.

AND EXISTS (
SELECT 1
FROM tContractTermAg entDealerFeeAmo unts
WHERE iContractTermAg entDealerID =
tContractTermAg entDealers.iCon tractTermAgentD ealerID
AND bAssigned = 1
)

Also, you could add the predicate "AND tCancellations. bActive=1" to the
existing EXISTS subquery.

Hope this helps,
Gert-Jan


tracy@guisoluti ons.com wrote:[color=blue]
>
> Thanks for suggestions but unfortunately neither one helped. There was
> no parallelism in the original query and OPTION(MAXDOP 1) did not
> change the plan. I updated all statistics:
>
> SET NOCOUNT ON
> DECLARE @Table char(64)
> DECLARE IndexCursor
> CURSOR FOR SELECT name
> FROM sysobjects
> WHERE type='U'
> AND uid=5
> OPEN IndexCursor
> FETCH NEXT FROM IndexCursor INTO @Table
> WHILE @@FETCH_STATUS= 0 BEGIN
> EXEC ('UPDATE STATISTICS ' + @Table + ' WITH FULLSCAN')
> FETCH NEXT FROM IndexCursor
> INTO @Table
> END
> CLOSE IndexCursor
> DEALLOCATE IndexCursor
> GO
>
> I made a seemingly silly change to the query that bought it down to one
> hour. I added the EXISTS statement to the WHERE clause that mimics the
> INNER JOIN in the SELECT statement. See below:
>
> SET NOCOUNT ON
> SELECT 1 AS source_id,
> faxagency.tCont racts.iContract ID AS contract_id,
> 2 AS status_id,
> 'Agent' AS payeetype,
> faxagency.tAgen ts.iAgentID AS payee_id,
> -1 AS paymenttype_id,
> 1 AS feetype_id,
> 0 AS issubagent,
> faxagency.tCont ractTermAgentDe alerFeeAmounts. fPrimaryAgentFe e*faxagency.tCa ncellations.fPe rcentage*-1
> AS Amount,
> 'Agent',
> faxagency.tCanc ellations.iCanc ellationID
> FROM faxagency.tBatc hs
> INNER JOIN faxagency.tCont racts ON faxagency.tCont racts.iBatchNum ber =
> faxagency.tBatc hs.iBatchNumber
> INNER JOIN faxagency.tCanc ellations ON
> faxagency.tCont racts.iContract ID = faxagency.tCanc ellations.iCont ractID
>
> INNER JOIN faxagency.tCont ractFeeAdjustme nts ON
> faxagency.tCont ractFeeAdjustme nts.iContractID =
> faxagency.tCont racts.iContract ID
> INNER JOIN faxagency.tPlan s ON faxagency.tPlan s.iPlanID =
> faxagency.tCont racts.iPlanID
> INNER JOIN faxagency.tPlan Types ON faxagency.tPlan Types.iPlanType ID =
> faxagency.tPlan s.iPlanTypeID
> INNER JOIN faxagency.tPlan ContractTerms ON
> faxagency.tPlan ContractTerms.i PlanContractTer mID =
> faxagency.tCont racts.iPlanCont ractTermID
> INNER JOIN faxagency.tTPAs ON faxagency.tTPAs .iTPAID =
> faxagency.tPlan s.iTPAID
> INNER JOIN faxagency.tCont ractTerms ON
> faxagency.tCont ractTerms.iCont ractTermID =
> faxagency.tPlan ContractTerms.i ContractTermID
> INNER JOIN faxagency.tDeal erCustomers ON
> faxagency.tDeal erCustomers.iDe alerCustomerID =
> faxagency.tCont racts.iDealerCu stomerID
> INNER JOIN faxagency.tCont ractTermAgentDe alers ON
> faxagency.tCont ractTermAgentDe alers.iDealerID =
> faxagency.tDeal erCustomers.iDe alerID
> INNER JOIN faxagency.tCont ractTermAgents ON
>
> faxagency.tCont ractTermAgents. iContractTermAg entID =
> faxagency.tCont ractTermAgentDe alers.iContract TermAgentID AND
> faxagency.tCont ractTermAgents. iPlanContractTe rmID
> = faxagency.tPlan ContractTerms.i PlanContractTer mID
> INNER JOIN faxagency.tCont ractTermAgentDe alerFeeAmounts ON
>
> faxagency.tCont ractTermAgentDe alerFeeAmounts. iContractTermAg entDealerID
> = faxagency.tCont ractTermAgentDe alers.iContract TermAgentDealer ID AND
>
> faxagency.tCont ractTermAgentDe alerFeeAmounts. dStartDate <=
> faxagency.tCont racts.dSaleDate AND
>
> faxagency.tCont ractTermAgentDe alerFeeAmounts. dEndDate >=
> faxagency.tCont racts.dSaleDate
>
> INNER JOIN faxagency.tAgen ts ON faxagency.tCont ractTermAgents. iAgentID
> = faxagency.tAgen ts.iAgentID
> INNER JOIN faxagency.tDeal ers ON faxagency.tBatc hs.iDealerID =
> faxagency.tDeal ers.iDealerID
> INNER JOIN faxagency.tMont hs ON faxagency.tTPAs .iTPAID =
> faxagency.tMont hs.iTPAID
> WHERE EXISTS (SELECT 1 FROM faxagency.tCanc ellations WHERE
> faxagency.tCanc ellations.iCont ractID =
> faxagency.tCont racts.iContract ID)
> AND ((faxagency.tCo ntractTermAgent DealerFeeAmount s.fPrimaryAgent Fee <>
> 0) OR
> (faxagency.tCon tractTermAgentD ealerFeeAmounts .iSubAgentID1 = 0
> AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID2 = 0
> AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID3 = 0
> AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID4 = 0
> AND faxagency.tCont ractTermAgentDe alerFeeAmounts. iSubAgentID5 = 0))
> AND (faxagency.tBat chs.bActive = 1)
> AND (faxagency.tCon tracts.bActive = 1)
> AND (faxagency.tPla ns.bUsePercenta ges = 0 AND faxagency.tPlan s.bActive
> = 1)
> AND (faxagency.tPla nContractTerms. bAssigned = 1)
> AND (faxagency.tCon tractTerms.bAct ive = 1)
> AND (faxagency.tCon tractTermAgentD ealerFeeAmounts .bAssigned = 1 )
> AND (faxagency.tCan cellations.bAct ive = 1 and
> faxagency.tCanc ellations.dProc essDate BETWEEN
> faxagency.tMont hs.dStartDate AND faxagency.tMont hs.dEndDate)
>
> One hour is still unacceptable. If I could execute the old plan (from
> five days ago) against the new data I bet it would still run around 30
> sec. Very frustrating.[/color]

[Erland Sommarskog]

Re: Is it possible to save and force a plan?

(tracy@guisolut ions.com) writes:[color=blue]
> Thanks for suggestions but unfortunately neither one helped. There was
> no parallelism in the original query and OPTION(MAXDOP 1) did not
> change the plan. I updated all statistics:
>...
> I made a seemingly silly change to the query that bought it down to one
> hour. I added the EXISTS statement to the WHERE clause that mimics the
> INNER JOIN in the SELECT statement. See below:[/color]

Too bad that the simple tricks did not works. Looks like you are in
for some harder work then.

You will need to analyse the query plan, and see where it goes wrong.
Since I don't know the tables, nor the indexes, it's difficult for me
to have a guess, but I would assume that somewhere the optimizer discards
a non-clustered index, estimating that it would lead too many bookmark
lookups.

You may find it necessary to add index hints, but rearrangement of
some indexes could also be necessary.

Since the query is complex, it may be difficult to say something over
the newsgroup. Looking through 16 table definitions with their
indexes and a complex query plan is a quite a mouthful. (And the
non-use of table aliases in the query does not help.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp