Is it possible to save and force a plan?

[tracy]

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]

(tr***@guisolutions.com) writes:

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?

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Damien]

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]

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.tContracts.iContractID AS contract_id,
2 AS status_id,
'Agent' AS payeetype,
faxagency.tAgents.iAgentID AS payee_id,
-1 AS paymenttype_id,
1 AS feetype_id,
0 AS issubagent,
faxagency.tContractTermAgentDealerFeeAmounts.fPrim aryAgentFee*faxagency.tCancellations.fPercentage*-1
AS Amount,
'Agent',
faxagency.tCancellations.iCancellationID
FROM faxagency.tBatchs
INNER JOIN faxagency.tContracts ON faxagency.tContracts.iBatchNumber =
faxagency.tBatchs.iBatchNumber
INNER JOIN faxagency.tCancellations ON
faxagency.tContracts.iContractID = faxagency.tCancellations.iContractID

INNER JOIN faxagency.tContractFeeAdjustments ON
faxagency.tContractFeeAdjustments.iContractID =
faxagency.tContracts.iContractID
INNER JOIN faxagency.tPlans ON faxagency.tPlans.iPlanID =
faxagency.tContracts.iPlanID
INNER JOIN faxagency.tPlanTypes ON faxagency.tPlanTypes.iPlanTypeID =
faxagency.tPlans.iPlanTypeID
INNER JOIN faxagency.tPlanContractTerms ON
faxagency.tPlanContractTerms.iPlanContractTermID =
faxagency.tContracts.iPlanContractTermID
INNER JOIN faxagency.tTPAs ON faxagency.tTPAs.iTPAID =
faxagency.tPlans.iTPAID
INNER JOIN faxagency.tContractTerms ON
faxagency.tContractTerms.iContractTermID =
faxagency.tPlanContractTerms.iContractTermID
INNER JOIN faxagency.tDealerCustomers ON
faxagency.tDealerCustomers.iDealerCustomerID =
faxagency.tContracts.iDealerCustomerID
INNER JOIN faxagency.tContractTermAgentDealers ON
faxagency.tContractTermAgentDealers.iDealerID =
faxagency.tDealerCustomers.iDealerID
INNER JOIN faxagency.tContractTermAgents ON

faxagency.tContractTermAgents.iContractTermAgentID =
faxagency.tContractTermAgentDealers.iContractTermA gentID AND
faxagency.tContractTermAgents.iPlanContractTermID
= faxagency.tPlanContractTerms.iPlanContractTermID
INNER JOIN faxagency.tContractTermAgentDealerFeeAmounts ON

faxagency.tContractTermAgentDealerFeeAmounts.iCont ractTermAgentDealerID
= faxagency.tContractTermAgentDealers.iContractTermA gentDealerID AND

faxagency.tContractTermAgentDealerFeeAmounts.dStar tDate <=
faxagency.tContracts.dSaleDate AND

faxagency.tContractTermAgentDealerFeeAmounts.dEndD ate >=
faxagency.tContracts.dSaleDate

INNER JOIN faxagency.tAgents ON faxagency.tContractTermAgents.iAgentID
= faxagency.tAgents.iAgentID
INNER JOIN faxagency.tDealers ON faxagency.tBatchs.iDealerID =
faxagency.tDealers.iDealerID
INNER JOIN faxagency.tMonths ON faxagency.tTPAs.iTPAID =
faxagency.tMonths.iTPAID
WHERE EXISTS (SELECT 1 FROM faxagency.tCancellations WHERE
faxagency.tCancellations.iContractID =
faxagency.tContracts.iContractID)
AND ((faxagency.tContractTermAgentDealerFeeAmounts.fPr imaryAgentFee <>
0) OR
(faxagency.tContractTermAgentDealerFeeAmounts.iSub AgentID1 = 0
AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID2 = 0
AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID3 = 0
AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID4 = 0
AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID5 = 0))
AND (faxagency.tBatchs.bActive = 1)
AND (faxagency.tContracts.bActive = 1)
AND (faxagency.tPlans.bUsePercentages = 0 AND faxagency.tPlans.bActive
= 1)
AND (faxagency.tPlanContractTerms.bAssigned = 1)
AND (faxagency.tContractTerms.bActive = 1)
AND (faxagency.tContractTermAgentDealerFeeAmounts.bAss igned = 1 )
AND (faxagency.tCancellations.bActive = 1 and
faxagency.tCancellations.dProcessDate BETWEEN
faxagency.tMonths.dStartDate AND faxagency.tMonths.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]

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]

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 tContractTermAgentDealerFeeAmounts
WHERE iContractTermAgentDealerID =
tContractTermAgentDealers.iContractTermAgentDealer ID
AND bAssigned = 1
)

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

Hope this helps,
Gert-Jan
tr***@guisolutions.com wrote:

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.tContracts.iContractID AS contract_id,
2 AS status_id,
'Agent' AS payeetype,
faxagency.tAgents.iAgentID AS payee_id,
-1 AS paymenttype_id,
1 AS feetype_id,
0 AS issubagent,
faxagency.tContractTermAgentDealerFeeAmounts.fPrim aryAgentFee*faxagency.tCancellations.fPercentage*-1
AS Amount,
'Agent',
faxagency.tCancellations.iCancellationID
FROM faxagency.tBatchs
INNER JOIN faxagency.tContracts ON faxagency.tContracts.iBatchNumber =
faxagency.tBatchs.iBatchNumber
INNER JOIN faxagency.tCancellations ON
faxagency.tContracts.iContractID = faxagency.tCancellations.iContractID

INNER JOIN faxagency.tContractFeeAdjustments ON
faxagency.tContractFeeAdjustments.iContractID =
faxagency.tContracts.iContractID
INNER JOIN faxagency.tPlans ON faxagency.tPlans.iPlanID =
faxagency.tContracts.iPlanID
INNER JOIN faxagency.tPlanTypes ON faxagency.tPlanTypes.iPlanTypeID =
faxagency.tPlans.iPlanTypeID
INNER JOIN faxagency.tPlanContractTerms ON
faxagency.tPlanContractTerms.iPlanContractTermID =
faxagency.tContracts.iPlanContractTermID
INNER JOIN faxagency.tTPAs ON faxagency.tTPAs.iTPAID =
faxagency.tPlans.iTPAID
INNER JOIN faxagency.tContractTerms ON
faxagency.tContractTerms.iContractTermID =
faxagency.tPlanContractTerms.iContractTermID
INNER JOIN faxagency.tDealerCustomers ON
faxagency.tDealerCustomers.iDealerCustomerID =
faxagency.tContracts.iDealerCustomerID
INNER JOIN faxagency.tContractTermAgentDealers ON
faxagency.tContractTermAgentDealers.iDealerID =
faxagency.tDealerCustomers.iDealerID
INNER JOIN faxagency.tContractTermAgents ON

faxagency.tContractTermAgents.iContractTermAgentID =
faxagency.tContractTermAgentDealers.iContractTermA gentID AND
faxagency.tContractTermAgents.iPlanContractTermID
= faxagency.tPlanContractTerms.iPlanContractTermID
INNER JOIN faxagency.tContractTermAgentDealerFeeAmounts ON

faxagency.tContractTermAgentDealerFeeAmounts.iCont ractTermAgentDealerID
= faxagency.tContractTermAgentDealers.iContractTermA gentDealerID AND

faxagency.tContractTermAgentDealerFeeAmounts.dStar tDate <=
faxagency.tContracts.dSaleDate AND

faxagency.tContractTermAgentDealerFeeAmounts.dEndD ate >=
faxagency.tContracts.dSaleDate

INNER JOIN faxagency.tAgents ON faxagency.tContractTermAgents.iAgentID
= faxagency.tAgents.iAgentID
INNER JOIN faxagency.tDealers ON faxagency.tBatchs.iDealerID =
faxagency.tDealers.iDealerID
INNER JOIN faxagency.tMonths ON faxagency.tTPAs.iTPAID =
faxagency.tMonths.iTPAID
WHERE EXISTS (SELECT 1 FROM faxagency.tCancellations WHERE
faxagency.tCancellations.iContractID =
faxagency.tContracts.iContractID)
AND ((faxagency.tContractTermAgentDealerFeeAmounts.fPr imaryAgentFee <>
0) OR
(faxagency.tContractTermAgentDealerFeeAmounts.iSub AgentID1 = 0
AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID2 = 0
AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID3 = 0
AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID4 = 0
AND faxagency.tContractTermAgentDealerFeeAmounts.iSubA gentID5 = 0))
AND (faxagency.tBatchs.bActive = 1)
AND (faxagency.tContracts.bActive = 1)
AND (faxagency.tPlans.bUsePercentages = 0 AND faxagency.tPlans.bActive
= 1)
AND (faxagency.tPlanContractTerms.bAssigned = 1)
AND (faxagency.tContractTerms.bActive = 1)
AND (faxagency.tContractTermAgentDealerFeeAmounts.bAss igned = 1 )
AND (faxagency.tCancellations.bActive = 1 and
faxagency.tCancellations.dProcessDate BETWEEN
faxagency.tMonths.dStartDate AND faxagency.tMonths.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.

[Erland Sommarskog]

(tr***@guisolutions.com) writes:

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:

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp