Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

The RECOMPILE Options Expand / Collapse
Author
Message
Posted Wednesday, July 6, 2011 9:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 27, 2013 9:51 AM
Points: 141, Visits: 113
Comments posted to this topic are about the item The RECOMPILE Options


Kindest Regards,

M Suresh Kumar

Post #1137787
Posted Thursday, July 7, 2011 12:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 25, 2013 2:25 PM
Points: 94, 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?)?
Post #1137840
Posted Thursday, July 7, 2011 1:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 2, 2012 8:11 AM
Points: 61, 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.
Post #1137850
Posted Thursday, July 7, 2011 1:43 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Sunday, June 29, 2014 11:26 PM
Points: 1,481, Visits: 1,960
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
Post #1137854
Posted Thursday, July 7, 2011 2:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 3, 2014 1:22 PM
Points: 316, Visits: 133


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
Post #1137863
Posted Thursday, July 7, 2011 2:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 2, 2012 8:11 AM
Points: 61, 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.
Post #1137869
Posted Thursday, July 7, 2011 2:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 3, 2014 1:22 PM
Points: 316, Visits: 133


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
Post #1137872
Posted Thursday, July 7, 2011 2:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 4:54 AM
Points: 42, Visits: 456
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.

Post #1137876
Posted Thursday, July 7, 2011 2:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 2, 2012 8:11 AM
Points: 61, 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.
Post #1137880
Posted Thursday, July 7, 2011 2:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 3, 2014 1:22 PM
Points: 316, Visits: 133


Thanx a bunch my friend.

Post #1137883
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse