July 6, 2011 at 9:55 pm
Comments posted to this topic are about the item The RECOMPILE Options
July 7, 2011 at 12:48 am
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?)?
July 7, 2011 at 1:27 am
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.
July 7, 2011 at 1:43 am
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
July 7, 2011 at 2:00 am
[font="Times New Roman"]
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
[/font]
July 7, 2011 at 2:08 am
anil_kumar32 (7/7/2011)
[font="Times New Roman"]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.
[/font]
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.
July 7, 2011 at 2:21 am
[font="Times New Roman"]
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
[/font]
July 7, 2011 at 2:36 am
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.
July 7, 2011 at 2:42 am
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.
July 7, 2011 at 2:48 am
[font="Times New Roman"]
Thanx a bunch my friend.
[/font]
July 7, 2011 at 2:50 am
anil_kumar32 (7/7/2011)
[font="Times New Roman"]Good things that comes with SP are security, less network traffic and SQL injection aviodance.
[/font]
Attention with security. Sometimes SPs execute with admin permissions even when the user does not have this level. Network traffic is indeed reduced. BUT you can easily write an SP where injection is possible for example when you create the ad-hoc SQL inside the SP.
July 7, 2011 at 2:53 am
double post
July 7, 2011 at 2:59 am
Developers (mostly in the beginning stages of their career) often tend to use OPTION (RECOMPILE) an an excuse for introducing more and more adhoc T-SQL into the application and then tend to eliminate SP's altogether. And then they reach a stage where plan cache gets filled up to the brim again and again.
I'm not a native speaker, but this part in the blog is a little bit confused for me. I understand that T-SQL can fill up the plan cache by the described reasons in your article, but the opton RECOMPILE will not cache any statements in the cache. The explanation is also only related to T-SQL statement usage instead of SPs and not to the RECOMPILE option?
http://msdn.microsoft.com/en-us/library/ms181714.aspx
Instructs the SQL Server Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed.
July 7, 2011 at 6:14 am
Could be wrong, but I think what he means is that, because these developers switch from using stored procedures to inline ad-hoc queries, the plan cache gets filled up by all the queries being executed. Even if they are deleted and repopulated each time they are run, each different query adds to the cache.
July 7, 2011 at 6:21 am
Good reading and very informative. Thanks.
Hakim Ali
www.sqlzen.com
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply