Skip to main content
Database Administrators

Return to Question

added 78 characters in body
Source Link
J. Mini
J. Mini
  • 801
  • 7
  • 23

I recently finished reading Plan Caching in SQL Server 2008 and I have become confused. It seems that, short of totally flushing the plan cache or explicitly demanding a stored procedure to be recompiled, recompilations of stored procedures from SQL Server 2008 onwards are done at the statement level rather than the stored-procedure level. 

So, aside from explicitly flushing the cache or demanding recompiles (e.g. WITH RECOMPILE), what recompiles a full stored procedure in SQL Server 2019 rather than just recompiling individual statements?

To give an example of where I am confused, consider the following procedure.

CREATE PROCEDURE FOO AS
BEGIN
   SELECT * INTO #temp1 FROM table1
   INSERT BAR1 SELECT * FROM #temp1
   INSERT BAR2 SELECT * FROM #temp1
END

I can think of plenty of things that could cause SELECT * INTO #temp1 FROM table1 to recompile, but it would be strange for that to recompile without the next lines also recompiling. This makes me think that there must be some things within SQL Server that will cause entire stored procedures to recompile.

I recently finished reading Plan Caching in SQL Server 2008 and I have become confused. It seems that, short of totally flushing the plan cache or explicitly demanding a stored procedure to be recompiled, recompilations of stored procedures from SQL Server 2008 onwards are done at the statement level rather than the stored-procedure level. So, aside from explicitly flushing the cache or demanding recompiles, what recompiles a full stored procedure in SQL Server 2019?

To give an example of where I am confused, consider the following procedure.

CREATE PROCEDURE FOO AS
BEGIN
   SELECT * INTO #temp1 FROM table1
   INSERT BAR1 SELECT * FROM #temp1
   INSERT BAR2 SELECT * FROM #temp1
END

I can think of plenty of things that could cause SELECT * INTO #temp1 FROM table1 to recompile, but it would be strange for that to recompile without the next lines also recompiling. This makes me think that there must be some things within SQL Server that will cause entire stored procedures to recompile.

I recently finished reading Plan Caching in SQL Server 2008 and I have become confused. It seems that, short of totally flushing the plan cache or explicitly demanding a stored procedure to be recompiled, recompilations of stored procedures from SQL Server 2008 onwards are done at the statement level rather than the stored-procedure level. 

So, aside from explicitly flushing the cache or demanding recompiles (e.g. WITH RECOMPILE), what recompiles a full stored procedure in SQL Server 2019 rather than just recompiling individual statements?

To give an example of where I am confused, consider the following procedure.

CREATE PROCEDURE FOO AS
BEGIN
   SELECT * INTO #temp1 FROM table1
   INSERT BAR1 SELECT * FROM #temp1
   INSERT BAR2 SELECT * FROM #temp1
END

I can think of plenty of things that could cause SELECT * INTO #temp1 FROM table1 to recompile, but it would be strange for that to recompile without the next lines also recompiling. This makes me think that there must be some things within SQL Server that will cause entire stored procedures to recompile.

Source Link
J. Mini
J. Mini
  • 801
  • 7
  • 23

Aside from explicitly flushing the cache or demanding recompiles, what recompiles a full stored procedure in SQL Server 2019?

I recently finished reading Plan Caching in SQL Server 2008 and I have become confused. It seems that, short of totally flushing the plan cache or explicitly demanding a stored procedure to be recompiled, recompilations of stored procedures from SQL Server 2008 onwards are done at the statement level rather than the stored-procedure level. So, aside from explicitly flushing the cache or demanding recompiles, what recompiles a full stored procedure in SQL Server 2019?

To give an example of where I am confused, consider the following procedure.

CREATE PROCEDURE FOO AS
BEGIN
   SELECT * INTO #temp1 FROM table1
   INSERT BAR1 SELECT * FROM #temp1
   INSERT BAR2 SELECT * FROM #temp1
END

I can think of plenty of things that could cause SELECT * INTO #temp1 FROM table1 to recompile, but it would be strange for that to recompile without the next lines also recompiling. This makes me think that there must be some things within SQL Server that will cause entire stored procedures to recompile.