Compile VS Recompile

Question

Compile VS Recompile

89netanel

SSCrazy

Points: 2130
More actions
January 9, 2017 at 1:57 am

#333671

Hello All,
I am SQL Server beginner.
There is a term that i didnt understand yet.
Can you please help to understand what is Compile?
What does it do and why?
Also what is recompile and how determine when will be a recompile?
Thanks in advance!
(I know that question is very very siliy i will appriciate if somone can expand my knowledge!)

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Read Gail's post. It does a good job. Essentially, a compile produces an execution plan that is then run to return (or change) data. That's similar to compiling a C# program to produce an executable. SQL handles this for you.

As Gail explains, if there is a chance the plan is no longer valid, SQL Server has to determine then, then recompile the query (or batch) and the move it to the plan cache, then execute this.

In a multi-user system, with many people connecting, this can be a load on your server and negatively impact performance.

aalberyo Grasshopper Points: 22 More actions · Answer 3

Hi friend,

Compile talks about Store Procedure for the DB. Basicaly is modify and optimize of tables and indexes incorporated a one field of a table, compile an SP it save the data in CACHE or execution plan and recompile is run the SP again. Check a bit about Store Procedure article Compile and Recompile.

I hope have helped!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

aalberyo (1/9/2017)
Compile talks about Store Procedure for the DB. Basicaly is modify and optimize of tables and indexes incorporated a one field of a table, compile an SP it save the data in CACHE or execution plan and recompile is run the SP again.

That's not correct, read the blog post I've linked above.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

aalberyo (1/9/2017)
Hi friend,
Compile talks about Store Procedure for the DB. Basicaly is modify and optimize of tables and indexes incorporated a one field of a table, compile an SP it save the data in CACHE or execution plan and recompile is run the SP again. Check a bit about Store Procedure article Compile and Recompile.
I hope have helped!

Ugh, not remotely correct. Please read BOL and Gail's post, linked in the first response above.

89netanel SSCrazy Points: 2130 More actions · Answer 6

Hello all

Thank you very much fir your quick response!