3

I was executing the script I found on this site to mark all my tables and stored procedures for recompilation.
(I changed it to also mark the stored procedures)

I have a Windows Forms (.net 3.5) application which uses a lot of data tables and after executing this script the time it took to open a data heavy form went from 8 seconds to just 4 seconds.

Do I have to run such a script regularily? I thought SQL Server would take care of compiling procedures and such.


BTW: Should this question be on SO? I thought the DBAs would be here on SF.

Marc
  • 405
  • 2
  • 6
  • 12
  • 1
    This is one of those overlapping areas, but it's fine to ask here. If you don't get many replies, we can always migrate it over to SO to get you additional input. – Kara Marfia Aug 07 '09 at 12:28

3 Answers3

9

From Microsoft :

"As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes

Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer."

Marko Carter
  • 4,092
  • 1
  • 29
  • 38
1

Consider that the use of the word compile differs in SQL Server than it does, in say, a normal application. In a normal app the code is compiled to a machine language ready state so it doesn't have to be interpreted at run time. SQL Server's use of compile is to determine the best execution plan to perform the query. Since data has the potential to be constantly changing, SQL Server may have to make new determinations based on the indexes that are available, the quality of the statistics, etc., and all these can affect the execution plan. As a result, whenever a stored procedure is executed, SQL Server makes a determination to see if it can re-use an exisiting execution plan for that query or if it needs to generate a new one. Of course, if no plan exists, it'll generate a new one.

This, BTW, is not very different from what .NET apps do, if you think about it. .NET apps are compiled to an intermediate language state, but only fully compiled by default at the first run time.

K. Brian Kelley
  • 9,004
  • 31
  • 33
0

When the SP is compiled, its execution path is fixed. It cannot take advantage of new indexes, or different strategies based on table sizes. Recompiling allows it to re-evaluate the execution conditions.

I'm not vastly experiences here - but a rule of thumb is to let simple SPs recompile each time. Only set no compile on the more complex cases

Mesh
  • 213
  • 3
  • 10
  • So you mean that recompiling+executing will be faster for simple SPs? But what about the heavier ones? Should they be manually recompiled from time to time then? – Marc Aug 07 '09 at 09:43
  • 2
    Forcing the recompile before every time it runs will slow down execution. Not a problem if the SP is called infrequently but will impact on a highly used SP. Would be better to schedule an sp_recompile so that it only recompiles the next time it is executed. – PaulPlum Aug 07 '09 at 10:05
  • hmmm Just changes my rule of thumb to: Simple SPs set NO COMPILE, complex ones RECOMPILE. And Schedule a sp_recompile as part of maintenance. -The small evaluation time may result in better overall performance from optimization - complex sps take time anyway. – Mesh Aug 07 '09 at 10:17