Temp tables vs Permanent table with deletes
in [Programming SQL]
|
From: Kalen Delaney on 9 Apr 2010 13:38 You have several choices. You can create the proc using WITH RECOMPILE in the definition, to come up with a new plan for the proc on every execution. You can execute the proc and add WITH RECOMPILE to create a one-time use new plan. Or, you can add OPTION (RECOMPILE) to an individual statement in the proc, which is really what I was thinking of. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "tshad" <tfs(a)dslextreme.com> wrote in message news:#2TMwNA2KHA.5856(a)TK2MSFTNGP05.phx.gbl... > > "Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message > news:18C230E3-4D4D-4F32-AF04-DF5C404AF40F(a)microsoft.com... >> Each statement has a plan, but the proc also has a plan that includes the >> plan for each of the statements. >> > So when you are doing a WITH RECOMPILE. > > Does it do only the PROC plan? Or does it do the PROC plan and all the > plans for all the queries. > > Then, according to Erlands article (if I am reading it right), if you then > run the procedure again, it hashes the text of the procedure and tries to > get the plan from cache. If it does, it now has the PROC Plan. > > What about the individual queries plans? > > Are they picked up as well? > > Thanks, > > Tom >> -- >> HTH >> Kalen >> ---------------------------------------- >> Kalen Delaney >> SQL Server MVP >> www.SQLServerInternals.com >> >> "tshad" <tfs(a)dslextreme.com> wrote in message >> news:OQ7aWj41KHA.6108(a)TK2MSFTNGP06.phx.gbl... >>> >>> "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message >>> news:4BBE343B.B8798813(a)xs4all.nl... >>>> tshad wrote: >>>>> >>>>> "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message >>>>> news:4BBE13F1.890696A8(a)xs4all.nl... >>>>> > Tom, >>>>> > >>>>> > When you call a stored procedure for the first time, it is compiled >>>>> > as a >>>>> > batch of statements, then it is executed. >>>>> > >>>>> > If you create a temp table in the SP, then the next statement that >>>>> > uses >>>>> > this temp table will have to be recompiled. In fact, any next >>>>> > statement >>>>> > that uses the temp table will be marked for recompilation. However, >>>>> > after that, if you execute the same statement again, it will not >>>>> > recompile. >>>>> > >>>>> What do you mean "Next statement"? >>>> >>>> "CREATE TABLE" is a statement. In your example (below), "SELECT ... >>>> FROM >>>> #Tom" is the next (2nd) statement. >>>> >>>> >>>>> If I create the table, there is no recompile there. But the next >>>>> statement >>>>> it sees (select, update etc) that uses the table will be recompiled. >>>>> >>>>> And every other statement will be. >>>>> >>>>> For example: >>>>> >>>>> CREATE TABLE #Tom... <--no recompile >>>>> >>>>> SELECT ... FROM #Tom <- recompile each time SP called >>>>> >>>>> UPDATE #Tom... <- recompiled each time SP called >>>>> >>>>> WHILE... >>>>> BEGIN >>>>> INSERT #tom <-recompiled 1st loop only each time SP called. >>>>> SELECT... >>>>> END >>>> >>>> What you describe in your example above is exactly what I was saying. >>>> >>>> >>>>> But there was also something about recompiling each time if the CREATE >>>>> TABLE >>>>> was mixed with the code instead of being at the start of the >>>>> procedure. >>>> >>>> You seem to be missing the point what triggers a recompile or why there >>>> are recompiles. The key to this all is changed statistics! Nothing >>>> more, >>>> nothing less. >>>> >>> >>> I may be. >>> >>>> Whenever you create a table, that table will have "new" statistics, as >>>> well as the definition about the actual columns, data types, etc. >>>> Obviously no possible query plan can exist (and still be valid) for any >>>> "old" table with the same name. >>>> >>>> The 2nd statement in your example ("SELECT ... FROM #Tom") is a >>>> different statement than the 4th ("INSERT #tom SELECT ..."), so >>>> obviously the recompile of the 2nd statement doesn't help the 4th >>>> statement at all. Each distinct statement requires its own query plan. >>>> >>> >>> I think I am misunderstanding here. >>> >>> We talk about whether a stored procedure has to be recompiled or not and >>> we have a statement on the SP (with recompile). >>> >>> I was always thinking of the query place as being the plan for the whole >>> procedure. >>> >>> But is seems what you are saying that there are multiple query plans for >>> each procedure if there are multiple statements. >>> >>> So if the above example is in a SP, there are 3 query plans not one for >>> the stored procedure. >>> >>> Tom >>> >>>> There are only two other situations that cause a recompile: >>>> 1) you have done so many inserts/update/deletes that the statistics are >>>> recalculated. These new statistics can only be effectively used after a >>>> recompilation, which is exactly what happens >>>> 2) because of memory pressure, the existing query plan was purged from >>>> the cache to make room for something else >>>> >>>> -- >>>> Gert-Jan >>> >>> > >
From: tshad on 9 Apr 2010 14:01 "Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message news:84D29AB2-0A4C-46CA-A641-9194CF4DC1D2(a)microsoft.com... > You have several choices. You can create the proc using WITH RECOMPILE in > the definition, to come up with a new plan for the proc on every > execution. You can execute the proc and add WITH RECOMPILE to create a > one-time use new plan. Or, you can add OPTION (RECOMPILE) to an individual > statement in the proc, which is really what I was thinking of. > I understand that you can do that. I am trying to understand what it does. Does the WITH RECOMPILE only compile the PROC plan? Or does it do all the plans inside the procedure as well? If one of the plans inside the Stored procedure needs to be recompiled, dos the whole PROC plan get redone as well as all the other plans or just the single query plan? Thanks, Tom > -- > HTH > Kalen > ---------------------------------------- > Kalen Delaney > SQL Server MVP > www.SQLServerInternals.com > > "tshad" <tfs(a)dslextreme.com> wrote in message > news:#2TMwNA2KHA.5856(a)TK2MSFTNGP05.phx.gbl... >> >> "Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message >> news:18C230E3-4D4D-4F32-AF04-DF5C404AF40F(a)microsoft.com... >>> Each statement has a plan, but the proc also has a plan that includes >>> the plan for each of the statements. >>> >> So when you are doing a WITH RECOMPILE. >> >> Does it do only the PROC plan? Or does it do the PROC plan and all the >> plans for all the queries. >> >> Then, according to Erlands article (if I am reading it right), if you >> then run the procedure again, it hashes the text of the procedure and >> tries to get the plan from cache. If it does, it now has the PROC Plan. >> >> What about the individual queries plans? >> >> Are they picked up as well? >> >> Thanks, >> >> Tom >>> -- >>> HTH >>> Kalen >>> ---------------------------------------- >>> Kalen Delaney >>> SQL Server MVP >>> www.SQLServerInternals.com >>> >>> "tshad" <tfs(a)dslextreme.com> wrote in message >>> news:OQ7aWj41KHA.6108(a)TK2MSFTNGP06.phx.gbl... >>>> >>>> "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message >>>> news:4BBE343B.B8798813(a)xs4all.nl... >>>>> tshad wrote: >>>>>> >>>>>> "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message >>>>>> news:4BBE13F1.890696A8(a)xs4all.nl... >>>>>> > Tom, >>>>>> > >>>>>> > When you call a stored procedure for the first time, it is compiled >>>>>> > as a >>>>>> > batch of statements, then it is executed. >>>>>> > >>>>>> > If you create a temp table in the SP, then the next statement that >>>>>> > uses >>>>>> > this temp table will have to be recompiled. In fact, any next >>>>>> > statement >>>>>> > that uses the temp table will be marked for recompilation. However, >>>>>> > after that, if you execute the same statement again, it will not >>>>>> > recompile. >>>>>> > >>>>>> What do you mean "Next statement"? >>>>> >>>>> "CREATE TABLE" is a statement. In your example (below), "SELECT ... >>>>> FROM >>>>> #Tom" is the next (2nd) statement. >>>>> >>>>> >>>>>> If I create the table, there is no recompile there. But the next >>>>>> statement >>>>>> it sees (select, update etc) that uses the table will be recompiled. >>>>>> >>>>>> And every other statement will be. >>>>>> >>>>>> For example: >>>>>> >>>>>> CREATE TABLE #Tom... <--no recompile >>>>>> >>>>>> SELECT ... FROM #Tom <- recompile each time SP called >>>>>> >>>>>> UPDATE #Tom... <- recompiled each time SP called >>>>>> >>>>>> WHILE... >>>>>> BEGIN >>>>>> INSERT #tom <-recompiled 1st loop only each time SP called. >>>>>> SELECT... >>>>>> END >>>>> >>>>> What you describe in your example above is exactly what I was saying. >>>>> >>>>> >>>>>> But there was also something about recompiling each time if the >>>>>> CREATE TABLE >>>>>> was mixed with the code instead of being at the start of the >>>>>> procedure. >>>>> >>>>> You seem to be missing the point what triggers a recompile or why >>>>> there >>>>> are recompiles. The key to this all is changed statistics! Nothing >>>>> more, >>>>> nothing less. >>>>> >>>> >>>> I may be. >>>> >>>>> Whenever you create a table, that table will have "new" statistics, as >>>>> well as the definition about the actual columns, data types, etc. >>>>> Obviously no possible query plan can exist (and still be valid) for >>>>> any >>>>> "old" table with the same name. >>>>> >>>>> The 2nd statement in your example ("SELECT ... FROM #Tom") is a >>>>> different statement than the 4th ("INSERT #tom SELECT ..."), so >>>>> obviously the recompile of the 2nd statement doesn't help the 4th >>>>> statement at all. Each distinct statement requires its own query plan. >>>>> >>>> >>>> I think I am misunderstanding here. >>>> >>>> We talk about whether a stored procedure has to be recompiled or not >>>> and we have a statement on the SP (with recompile). >>>> >>>> I was always thinking of the query place as being the plan for the >>>> whole procedure. >>>> >>>> But is seems what you are saying that there are multiple query plans >>>> for each procedure if there are multiple statements. >>>> >>>> So if the above example is in a SP, there are 3 query plans not one for >>>> the stored procedure. >>>> >>>> Tom >>>> >>>>> There are only two other situations that cause a recompile: >>>>> 1) you have done so many inserts/update/deletes that the statistics >>>>> are >>>>> recalculated. These new statistics can only be effectively used after >>>>> a >>>>> recompilation, which is exactly what happens >>>>> 2) because of memory pressure, the existing query plan was purged from >>>>> the cache to make room for something else >>>>> >>>>> -- >>>>> Gert-Jan >>>> >>>> >> >>
From: Kalen Delaney on 9 Apr 2010 18:48
Tom... I thought I answered this... CREATE WITH RECOMPILE or EXEC WITH RECOMPILE will come up with a plan for the proc, which means a new plan for every optimizable statement in the proc. There is no such thing as 'only compiling the proc plan' ... the proc plan means the plans for all the statements. These options have no concept of what 'needs' to be recompiled. It just does it. The above options recompile the whole proc, OPTION (RECOMPILE) recompiles one statement. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "tshad" <tfs(a)dslextreme.com> wrote in message news:uzTIq6A2KHA.5212(a)TK2MSFTNGP04.phx.gbl... > > "Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message > news:84D29AB2-0A4C-46CA-A641-9194CF4DC1D2(a)microsoft.com... >> You have several choices. You can create the proc using WITH RECOMPILE in >> the definition, to come up with a new plan for the proc on every >> execution. You can execute the proc and add WITH RECOMPILE to create a >> one-time use new plan. Or, you can add OPTION (RECOMPILE) to an >> individual statement in the proc, which is really what I was thinking of. >> > I understand that you can do that. I am trying to understand what it > does. > > Does the WITH RECOMPILE only compile the PROC plan? > > Or does it do all the plans inside the procedure as well? > > If one of the plans inside the Stored procedure needs to be recompiled, > dos the whole PROC plan get redone as well as all the other plans or just > the single query plan? > > Thanks, > > Tom > >> -- >> HTH >> Kalen >> ---------------------------------------- >> Kalen Delaney >> SQL Server MVP >> www.SQLServerInternals.com >> >> "tshad" <tfs(a)dslextreme.com> wrote in message >> news:#2TMwNA2KHA.5856(a)TK2MSFTNGP05.phx.gbl... >>> >>> "Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message >>> news:18C230E3-4D4D-4F32-AF04-DF5C404AF40F(a)microsoft.com... >>>> Each statement has a plan, but the proc also has a plan that includes >>>> the plan for each of the statements. >>>> >>> So when you are doing a WITH RECOMPILE. >>> >>> Does it do only the PROC plan? Or does it do the PROC plan and all the >>> plans for all the queries. >>> >>> Then, according to Erlands article (if I am reading it right), if you >>> then run the procedure again, it hashes the text of the procedure and >>> tries to get the plan from cache. If it does, it now has the PROC Plan. >>> >>> What about the individual queries plans? >>> >>> Are they picked up as well? >>> >>> Thanks, >>> >>> Tom >>>> -- >>>> HTH >>>> Kalen >>>> ---------------------------------------- >>>> Kalen Delaney >>>> SQL Server MVP >>>> www.SQLServerInternals.com >>>> >>>> "tshad" <tfs(a)dslextreme.com> wrote in message >>>> news:OQ7aWj41KHA.6108(a)TK2MSFTNGP06.phx.gbl... >>>>> >>>>> "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message >>>>> news:4BBE343B.B8798813(a)xs4all.nl... >>>>>> tshad wrote: >>>>>>> >>>>>>> "Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in >>>>>>> message >>>>>>> news:4BBE13F1.890696A8(a)xs4all.nl... >>>>>>> > Tom, >>>>>>> > >>>>>>> > When you call a stored procedure for the first time, it is >>>>>>> > compiled as a >>>>>>> > batch of statements, then it is executed. >>>>>>> > >>>>>>> > If you create a temp table in the SP, then the next statement that >>>>>>> > uses >>>>>>> > this temp table will have to be recompiled. In fact, any next >>>>>>> > statement >>>>>>> > that uses the temp table will be marked for recompilation. >>>>>>> > However, >>>>>>> > after that, if you execute the same statement again, it will not >>>>>>> > recompile. >>>>>>> > >>>>>>> What do you mean "Next statement"? >>>>>> >>>>>> "CREATE TABLE" is a statement. In your example (below), "SELECT ... >>>>>> FROM >>>>>> #Tom" is the next (2nd) statement. >>>>>> >>>>>> >>>>>>> If I create the table, there is no recompile there. But the next >>>>>>> statement >>>>>>> it sees (select, update etc) that uses the table will be recompiled. >>>>>>> >>>>>>> And every other statement will be. >>>>>>> >>>>>>> For example: >>>>>>> >>>>>>> CREATE TABLE #Tom... <--no recompile >>>>>>> >>>>>>> SELECT ... FROM #Tom <- recompile each time SP called >>>>>>> >>>>>>> UPDATE #Tom... <- recompiled each time SP called >>>>>>> >>>>>>> WHILE... >>>>>>> BEGIN >>>>>>> INSERT #tom <-recompiled 1st loop only each time SP called. >>>>>>> SELECT... >>>>>>> END >>>>>> >>>>>> What you describe in your example above is exactly what I was saying. >>>>>> >>>>>> >>>>>>> But there was also something about recompiling each time if the >>>>>>> CREATE TABLE >>>>>>> was mixed with the code instead of being at the start of the >>>>>>> procedure. >>>>>> >>>>>> You seem to be missing the point what triggers a recompile or why >>>>>> there >>>>>> are recompiles. The key to this all is changed statistics! Nothing >>>>>> more, >>>>>> nothing less. >>>>>> >>>>> >>>>> I may be. >>>>> >>>>>> Whenever you create a table, that table will have "new" statistics, >>>>>> as >>>>>> well as the definition about the actual columns, data types, etc. >>>>>> Obviously no possible query plan can exist (and still be valid) for >>>>>> any >>>>>> "old" table with the same name. >>>>>> >>>>>> The 2nd statement in your example ("SELECT ... FROM #Tom") is a >>>>>> different statement than the 4th ("INSERT #tom SELECT ..."), so >>>>>> obviously the recompile of the 2nd statement doesn't help the 4th >>>>>> statement at all. Each distinct statement requires its own query >>>>>> plan. >>>>>> >>>>> >>>>> I think I am misunderstanding here. >>>>> >>>>> We talk about whether a stored procedure has to be recompiled or not >>>>> and we have a statement on the SP (with recompile). >>>>> >>>>> I was always thinking of the query place as being the plan for the >>>>> whole procedure. >>>>> >>>>> But is seems what you are saying that there are multiple query plans >>>>> for each procedure if there are multiple statements. >>>>> >>>>> So if the above example is in a SP, there are 3 query plans not one >>>>> for the stored procedure. >>>>> >>>>> Tom >>>>> >>>>>> There are only two other situations that cause a recompile: >>>>>> 1) you have done so many inserts/update/deletes that the statistics >>>>>> are >>>>>> recalculated. These new statistics can only be effectively used after >>>>>> a >>>>>> recompilation, which is exactly what happens >>>>>> 2) because of memory pressure, the existing query plan was purged >>>>>> from >>>>>> the cache to make room for something else >>>>>> >>>>>> -- >>>>>> Gert-Jan >>>>> >>>>> >>> >>> > > |