dynamic stored procedure query paln

Question

Post reply

dynamic stored procedure query paln

pb-916752

Old Hand

Points: 323
More actions
November 4, 2010 at 1:19 am

#231079

if we run the dynamic stored procedure, based on the input criteria given to the sp query formation changes and even the query plan also changes.
Which will have the impact on performance of the stored procedure
how the qury plan is stored in SQL 2008
is it possible to clear plan of the dynamic stored procedure in cache memory .

Viewing 8 posts - 1 through 7 (of 7 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You will have to take plan caching and parameter sniffing into account.

See this great guide by MVP Erland Sommarskog:

http://www.sommarskog.se/dyn-search.html

EDIT: I'm unable to spell "URL"...

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 2

You have to know that with dynamic queries, every single change to the string that defines the query means you get a new execution plan, unless you use parameterized queries or the query is simple that SQL Server uses simple parameterization for you. So, for example:

SELECT * FROM TABLEA AS a WHERE a.ID = 42

is going to be a completely different plan from

SELECT * FROM TABLEA AS a WHERE a.ID = 52

is going to be a completely different plan from

SELECT a.ID FROM TABLEA AS a WHERE a.ID = 42

You really have to take that into account. By different plan, I mean a different plan in cache, they may all result in identical execution plans, but you'd have three of them.

NOTE: The examples are so simple they might lead to simple parameterization, so you'd only see a single plan, but you get the idea.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 3

One other thing, SQL Server 2008 does give you some ability to help with dynamic SQL. You can set the server to "Optimize For Ad Hoc Workloads." Here's a description of how it works.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 4

Along a similar vein, is it possible to clear the plan for a single stored procedure without using sp_recompile or with recompile?

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 5

In SQL Server 2008, yes. You can call DBCC FREEPROCACHE(plan_handle). You just have to get the appropriate plan handle out of the procedure cache.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 6

thank you. I'll follow up on that

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

caladba SSCommitted Points: 1514 More actions · Answer 7

I agree with Grant's suggestion of using parameterized dynamic queries (sp_executesql).

This will reuse query plans even though the query is built dynamically.