SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The RECOMPILE Options


The RECOMPILE Options

Author
Message
Suresh Kumar Maganti
Suresh Kumar Maganti
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 125
Comments posted to this topic are about the item The RECOMPILE Options


Kindest Regards,

M Suresh Kumar

Marry Krissmess
Marry Krissmess
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 29
As a developer I actually prefer to use stored procedures. If only SQLServer had a way to organize them in packages just like in Oracle so you wouldn't end up with thousands of stored procedures and no way to tell what they are used for without opening each one of them (doc, which doc?).

On the other hand, I never understood the usefulness of writing stored procedures and then recompile them every time you execute them. That basically defeats the purpose of a stored procedure except for cases where

SELECT * FROM MyTable WHERE Param = 1


and
SELECT * FROM MyTable WHERE Param = 2



would lead to different execution plans because of the statistical distribution of Param = 1 and Param = 2 is too different. But how would you know that beforehand and for how long would this be true (and what about Param = 3?)?
andreas.pinhammer
andreas.pinhammer
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 39
RECOMPILE can be very useful, if the optimal execution plan highly depends on the given parameters. As the execution plan depends on the initial parameters during first execution, this execution plan can be very poor for different parameters. If in this case OPTIMIZE FOR doesn't work for your procedure, RECOMPILE is worth having a look.
tommyh
tommyh
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2186 Visits: 2000
Marry Krissmess (7/7/2011)
As a developer I actually prefer to use stored procedures. If only SQLServer had a way to organize them in packages just like in Oracle so you wouldn't end up with thousands of stored procedures and no way to tell what they are used for without opening each one of them (doc, which doc?).


Thats where a good naming standard comes in.

/T
anil_kumar32
anil_kumar32
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 369


Hi,

Store procedures are not compiled every time. Usually execution plan for store procedure is reused.

When there are some changes in input parameters or underlying table from which SP is fetching data, store procedure is recompiled and a new execution plan is created.

Good things that comes with SP are security, less network traffic and SQL injection aviodance.


Regards,
Anil

andreas.pinhammer
andreas.pinhammer
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 39
anil_kumar32 (7/7/2011)

When there are some changes in input parameters or underlying table from which SP is fetching data, store procedure is recompiled and a new execution plan is created.


No, changing input parameters does not force recompilation of the stored procedure. Recompilation only occurs if statistics or indexes have been rebuilt, the stored procedure itself has changed or the execution plan was removed from the plan cache.
anil_kumar32
anil_kumar32
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 369


Its means when we pass diffrent values to input parameter, execution plan for SP is reused.

I am a bit confused. Can you please explain it in detail?

Regards,
Anil

gary.strange-sqlconsumer
gary.strange-sqlconsumer
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 621
To the plan cache guru's

Is it really necessary to include the database name in all object references? I've always included schema.object but not database name as it's impossible to deploy the same code base on many databases if you hardcode the db name.

Surely the search for object statistics will start in the current database so adding the db name won't help. Also shouldn't we be using synonyms when making cross database queries.
andreas.pinhammer
andreas.pinhammer
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 39
Let's say you have the following stored procedure:

SELECT Name FROM Person
WHERE City = @city

If you call this stored procedure for the first time, the value of @city will be used to create an execution plan based on statistics, indexes, etc.

Next time you call the stored procedure, this execution plan will be reused. The value of @city doesn't matter anymore. Have in mind that SQL Server only stores two plans per stored procedure: one for serial execution and one for parallel execution.

To reduce the impact of first time execution, you can use the OPTIMIZE FOR clause, which allows you to specify the parameter values for the generation of the execution plan.

By the way, we had some serious performance problems with a stored procedure after a new software release. We found out that due to changes in our nightly schedule, the rebuild index was executed at a different time. The process that was following the rebuild index now used different parameters. As a result, the stored procedure was significant slower than before.
anil_kumar32
anil_kumar32
Old Hand
Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)Old Hand (386 reputation)

Group: General Forum Members
Points: 386 Visits: 369


Thanx a bunch my friend.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search