Good morning !
早上好 !
Is there any way to pre compile stored procedures in SQL Server? My requirement goes like this.. I have some stored procedures, which take more time in compiling than executing. So I want to precompile all the stored procedures. It would be nice to precompile them when the db server is started and running.
有没有办法在SQL Server中预编译存储过程?我的要求是这样的..我有一些存储过程,编译比执行需要更多时间。所以我想预编译所有存储过程。在db服务器启动并运行时预编译它们会很不错。
Any ideas on this would be greatly helpful!
任何有关这方面的想法都会非常有帮助!
Thanks & Regards, Pavan.
谢谢和问候,帕万。
6 个解决方案
#1
you can force a recompile, but it will not happen until the next time it is run
您可以强制重新编译,但直到下次运行才会发生
EXEC SP_RECOMPILE YourProcedureName
EXEC SP_RECOMPILE YourProcedureName
更多信息在这里......
force it to recompile each time it is run:
CREATE PROCEDURE YourProcedureNameWITH RECOMPILE .....
强制它在每次运行时重新编译:CREATE PROCEDURE YourProcedureNameWITH RECOMPILE .....
force it to recompile this time:
EXEC YourProcedureName WITH RECOMPILE
强制它重新编译这次:EXEC YourProcedureName WITH RECOMPILE
here is a good article on Optimizing SQL Server Stored Procedures to Avoid Recompiles
这是一篇关于优化SQL Server存储过程以避免重新编译的好文章
EDIT based on OP's comments:
编辑基于OP的评论:
why don't you manually run it (outside the application) with bogus data (not so bogus that the execution plan is bad though, google: sql server parameter spoofing and sniffing) the first time, that can force a compile, then run some sql to delete out what was inserted. when the users run it for the first time, it will have already run and have been compiled.
为什么你不用手动运行它(在应用程序之外)伪造数据(不是那么伪造,执行计划不好,google:sql server参数欺骗和嗅探)第一次,这可以强制编译,然后运行一些sql删除插入的内容。当用户第一次运行它时,它已经运行并且已经被编译。
#2
Write a script that executes each stored procedure using "SET FMTONLY ON".
编写一个脚本,使用“SET FMTONLY ON”执行每个存储过程。
The procedure will be compiled, but no permanent changes will be made to the DB during execution. You can't use this with procedures that use temporary tables (#table syntax).
将编译该过程,但在执行期间不会对DB进行永久性更改。您不能将此用于使用临时表的过程(#table语法)。
That's how Microsoft does it in Visual Studio to determine what the output of your stored procedure should be.
这就是Microsoft在Visual Studio中如何确定存储过程的输出应该是什么。
#3
If you are using SQL Server 2008 then you may be able to use a Plan Guide in order to enforce the re-use of an existing, pre compiled, Execution Plan.
如果您使用的是SQL Server 2008,那么您可以使用计划指南来强制重复使用现有的预编译执行计划。
See Understanding Plan Guides, for more details and in particular read "OBJECT Plan Guides"
有关详细信息,请参阅了解计划指南,特别是阅读“OBJECT计划指南”
I suspect however that the source of your issue is the process logic being implemented within your stored procedure and would suggest this as your first point of review for performance tuning.
但我怀疑您的问题的根源是在您的存储过程中实现的流程逻辑,并建议将其作为性能调优的第一个检查点。
#4
Is it possible to just execute the SP once without affecting any data? If so, you can then probably find a way to trigger this SP on server start up.
是否可以在不影响任何数据的情况下执行一次SP?如果是这样,那么您可能会找到一种在服务器启动时触发此SP的方法。
#5
A stored procedure should only compile (and for that matter only create a query plan) when it is created and first executed.
存储过程应仅在创建并首次执行时进行编译(并且仅为此创建查询计划)。
If you are using WITH RECOMPILE a lot, you should stop. If you're doing that to force recalculation of query plans because different parameters work more efficiently with different query plans (and if that matters, performance-wise) then you need to consider creating different SPs for the different query plans, perhaps with a "parent" SP to decide which to call. But it's not a pain-free exercise.
如果你经常使用WITH RECOMPILE,你应该停下来。如果你这样做是为了强制重新计算查询计划,因为不同的参数可以更有效地使用不同的查询计划(如果这很重要,在性能方面),那么你需要考虑为不同的查询计划创建不同的SP,也许用“ parent“SP决定调用哪个。但这不是一次无痛苦的运动。
If your tables are really in the sub-million row category then I'd look most carefully at indexing and keeping statistics up to date, recompiling periodically at quiet times to keep the query plans efficient. Once you're into tens or hundreds of millions of rows then there may be a case for going through the pain of duplication.
如果你的表真的属于亚万亿行类别,那么我会仔细查看索引并保持统计数据是最新的,在安静的时间定期重新编译以保持查询计划的有效性。一旦你进入数万或数亿行,那么可能会遇到重复的痛苦。
#6
Seldom is compilation time significant these days. Insert this code in the top of your program and see for yourself just how little time compilation takes.
这些天很少有编译时间。将此代码插入程序的顶部,并亲自查看编译所需的时间。
SET STATISTICS TIME ON
GO
You will find compilation times are typically given at 0ms, which means too small to bother with, whereas execution times are in the tens, hundreds, or even thousands of milliseconds.
您会发现编译时间通常为0ms,这意味着太小而无法理解,而执行时间则为数十,数百甚至数千毫秒。
#1
you can force a recompile, but it will not happen until the next time it is run
您可以强制重新编译,但直到下次运行才会发生
EXEC SP_RECOMPILE YourProcedureName
EXEC SP_RECOMPILE YourProcedureName
更多信息在这里......
force it to recompile each time it is run:
CREATE PROCEDURE YourProcedureNameWITH RECOMPILE .....
强制它在每次运行时重新编译:CREATE PROCEDURE YourProcedureNameWITH RECOMPILE .....
force it to recompile this time:
EXEC YourProcedureName WITH RECOMPILE
强制它重新编译这次:EXEC YourProcedureName WITH RECOMPILE
here is a good article on Optimizing SQL Server Stored Procedures to Avoid Recompiles
这是一篇关于优化SQL Server存储过程以避免重新编译的好文章
EDIT based on OP's comments:
编辑基于OP的评论:
why don't you manually run it (outside the application) with bogus data (not so bogus that the execution plan is bad though, google: sql server parameter spoofing and sniffing) the first time, that can force a compile, then run some sql to delete out what was inserted. when the users run it for the first time, it will have already run and have been compiled.
为什么你不用手动运行它(在应用程序之外)伪造数据(不是那么伪造,执行计划不好,google:sql server参数欺骗和嗅探)第一次,这可以强制编译,然后运行一些sql删除插入的内容。当用户第一次运行它时,它已经运行并且已经被编译。
#2
Write a script that executes each stored procedure using "SET FMTONLY ON".
编写一个脚本,使用“SET FMTONLY ON”执行每个存储过程。
The procedure will be compiled, but no permanent changes will be made to the DB during execution. You can't use this with procedures that use temporary tables (#table syntax).
将编译该过程,但在执行期间不会对DB进行永久性更改。您不能将此用于使用临时表的过程(#table语法)。
That's how Microsoft does it in Visual Studio to determine what the output of your stored procedure should be.
这就是Microsoft在Visual Studio中如何确定存储过程的输出应该是什么。
#3
If you are using SQL Server 2008 then you may be able to use a Plan Guide in order to enforce the re-use of an existing, pre compiled, Execution Plan.
如果您使用的是SQL Server 2008,那么您可以使用计划指南来强制重复使用现有的预编译执行计划。
See Understanding Plan Guides, for more details and in particular read "OBJECT Plan Guides"
有关详细信息,请参阅了解计划指南,特别是阅读“OBJECT计划指南”
I suspect however that the source of your issue is the process logic being implemented within your stored procedure and would suggest this as your first point of review for performance tuning.
但我怀疑您的问题的根源是在您的存储过程中实现的流程逻辑,并建议将其作为性能调优的第一个检查点。
#4
Is it possible to just execute the SP once without affecting any data? If so, you can then probably find a way to trigger this SP on server start up.
是否可以在不影响任何数据的情况下执行一次SP?如果是这样,那么您可能会找到一种在服务器启动时触发此SP的方法。
#5
A stored procedure should only compile (and for that matter only create a query plan) when it is created and first executed.
存储过程应仅在创建并首次执行时进行编译(并且仅为此创建查询计划)。
If you are using WITH RECOMPILE a lot, you should stop. If you're doing that to force recalculation of query plans because different parameters work more efficiently with different query plans (and if that matters, performance-wise) then you need to consider creating different SPs for the different query plans, perhaps with a "parent" SP to decide which to call. But it's not a pain-free exercise.
如果你经常使用WITH RECOMPILE,你应该停下来。如果你这样做是为了强制重新计算查询计划,因为不同的参数可以更有效地使用不同的查询计划(如果这很重要,在性能方面),那么你需要考虑为不同的查询计划创建不同的SP,也许用“ parent“SP决定调用哪个。但这不是一次无痛苦的运动。
If your tables are really in the sub-million row category then I'd look most carefully at indexing and keeping statistics up to date, recompiling periodically at quiet times to keep the query plans efficient. Once you're into tens or hundreds of millions of rows then there may be a case for going through the pain of duplication.
如果你的表真的属于亚万亿行类别,那么我会仔细查看索引并保持统计数据是最新的,在安静的时间定期重新编译以保持查询计划的有效性。一旦你进入数万或数亿行,那么可能会遇到重复的痛苦。
#6
Seldom is compilation time significant these days. Insert this code in the top of your program and see for yourself just how little time compilation takes.
这些天很少有编译时间。将此代码插入程序的顶部,并亲自查看编译所需的时间。
SET STATISTICS TIME ON
GO
You will find compilation times are typically given at 0ms, which means too small to bother with, whereas execution times are in the tens, hundreds, or even thousands of milliseconds.
您会发现编译时间通常为0ms,这意味着太小而无法理解,而执行时间则为数十,数百甚至数千毫秒。