SQL Server 2014's "Hekaton" in-memory table optimization proclaims, "Native compilation of business logic in stored procedures." Because of issues with "parameter sniffing" (see here and here) in SQL Server 2012 and earlier, however, I have always been forced to design most of my stored procedures with OPTIMIZE FOR UNKNOWN (or its equivalent). This effectively prevents query plans from being cached and forces SQL Server to recompile/re-optimize queries each time they are run. With a significant portion of Hekaton's performance gains coming from reuse of native compiled queries, does SQL Server 2014 do anything to address the parameter sniffing issue so I can actually use compiled queries?
Interpreted Transact-SQL stored procedures are compiled at first execution time, in contrast to natively compiled (aka. Hekaton) stored procedures, which are compiled at create time (and thus, the query execution plan is determined during creation time). When interpreted stored procedures are compiled at invocation, the values of the parameters supplied for this invocation are used by the optimizer when generating the execution plan. This use of parameters during compilation is called parameter sniffing.
Parameter sniffing is not used for compiling natively compiled stored procedures. All parameters to the stored procedure are considered to have UNKNOWN values.
As a workaround, you can use OPTIMIZE FOR to instructs the query optimizer to use a particular value for a variable/parameter when the procedure is compiled.