Menu
As always I used to create ordinary stored procedures regarding the second option for creating stored procedure called "Natively Compiled Stored Procedure" I wanna know if there is any performance benefit to using compiled one?
Is there anyone can Help to find out? Thanks
622
A natively compiled stored procedure is compiled when it is created, rather than when it is executed. That compile process first translates the store procedure code into “C”, and then into machine language and stores the code as a DLL. The DLL is stored in a special folder on the SQL Server machine.
What Does Native Compiler Mean? A native compiler is a compiler that works on compilation for the same technology on which it runs. It uses the same operating system or platform as the software for which it is assembling machine language.
A native SQL procedure is a procedure whose body is written entirely in SQL. The body is written in the SQL procedural language (SQL PL). A native SQL procedure is created by issuing a single SQL statement, CREATE PROCEDURE.
When a procedure is compiled for the first time or recompiled, the procedure's query plan is optimized for the current state of the database and its objects. If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure's query plan for those changes.
Stored procedures that are marked with NATIVE_COMPILATION are natively compiled. This means the Transact-SQL statements in the procedure are all compiled to native code for efficient execution of performance-critical business logic.
For more information, see Native Compilation of Tables and Stored Procedures
54
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
