The RECOMPILE Options

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Suresh Maganti » The RECOMPILE Options

34 posts, Page 1 of 4

1 2 3 4 »»»

The RECOMPILE Options

Rate Topic

Display Mode

Topic Options

Author

Message

Suresh Kumar Maganti

Posted Wednesday, July 06, 2011 9:55 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 5:24 PM
Points: 135, Visits: 98

Comments posted to this topic are about the item The RECOMPILE Options

Kindest Regards,

M Suresh Kumar

Post #1137787

Marry Krissmess

Posted Thursday, July 07, 2011 12:48 AM

SSC 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

andreas.pinhammer

Posted Thursday, July 07, 2011 1:27 AM

Valued Member

Group: General Forum Members
Last Login: Friday, November 02, 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

tommyh

Posted Thursday, July 07, 2011 1:43 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476, Visits: 1,943

Marry Krissmess (7/7/2011)

Thats where a good naming standard comes in.

/T

Post #1137854

anil_kumar32

Posted Thursday, July 07, 2011 2:00 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, August 03, 2011 2:53 AM
Points: 316, Visits: 81

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

andreas.pinhammer

Posted Thursday, July 07, 2011 2:08 AM

Valued Member

Group: General Forum Members
Last Login: Friday, November 02, 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

anil_kumar32

Posted Thursday, July 07, 2011 2:21 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, August 03, 2011 2:53 AM
Points: 316, Visits: 81

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

gary.strange-sqlconsumer

Posted Thursday, July 07, 2011 2:36 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:02 AM
Points: 34, Visits: 336

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

andreas.pinhammer

Posted Thursday, July 07, 2011 2:42 AM

Valued Member

Group: General Forum Members
Last Login: Friday, November 02, 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

anil_kumar32

Posted Thursday, July 07, 2011 2:48 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, August 03, 2011 2:53 AM
Points: 316, Visits: 81

Thanx a bunch my friend.

Post #1137883

« Prev Topic | Next Topic »

34 posts, Page 1 of 4

1 2 3 4 »»»

Permissions