Can I use a saved execution plan?

Question

Can I use a saved execution plan?

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

David Brusowankin-283008 SSC-Addicted Points: 471 More actions · Answer 1

Yah, but what if the reason you're trying a "USE PLAN" is because the optimizer simply won't come up with a reasonable solution.

Here's an example.

Table A: (createdDate datetime, id int identity (1,1), {other columns} )

with PK on (createdDate, id)

Table B: (startDate datetime, endDate datetime)

----

The query is:

select * from A

where createdDate >= (select startDate from B)

and endDate < (select endDate from B)

---

Table B only ever has 1 row.

I have tried max() and top 1 for the subqueries in the SARG but nothing can get the optimizer to first get the results of the subqueries and then apply the single values to the conditions of the SARG, such as

select * from A

where createdDate >= x

and endDate < y

----

So I try to use option (USE PLAN N'...') using the output of showplan_xml but all I get is Error 8698.

This is pretty lame. Any ideas?

Thanks,

David

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please post new questions in a new thread. Please include execution plans, index definitions and table definitions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Hey Peter - did you make the Kansas City SQL Saturday this year? I was the speaker with the 5yo daughter in tow. 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service