Best Practices for Calling Natively Compiled Stored Procedures

THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Natively compiled stored procedures are:

  • Used typically in performance-critical parts of an application.

  • Frequently executed.

  • Expected to be very fast.

    The performance benefit of using a natively compiled stored procedure increases with the number of rows and the amount of logic that is processed by the procedure. For example, a natively compiled stored procedure will exhibit better performance if it uses one or more of the following:

  • Aggregation.

  • Nested-loops joins.

  • Multi-statement select, insert, update, and delete operations.

  • Complex expressions.

  • Procedural logic, such as conditional statements and loops.

    If you need to process only a single row, using a natively compiled stored procedure may not provide a performance benefit.

    To avoid the server having to map parameter names and convert types:

  • Match the types of the parameters passed to the procedure with the types in the procedure definition.

  • Use ordinal (nameless) parameters when calling natively compiled stored procedures. For the most efficient execution, do not use named parameters.

    Inefficiencies in parameters with natively compiled stored procedures can be detected through the XEvent natively_compiled_proc_slow_parameter_passing:

    • Mismatched types: reason=parameter_conversion
    • Named parameters: reason=named_parameters
    • DEFAULT values: reason=default

See Also

Natively Compiled Stored Procedures