4

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?

Community
  • 1
  • 1
Mike
  • 7,500
  • 8
  • 44
  • 62
  • I don't know the answer, but given that the CTP just came out the other day and that the people who would have a chance to answer this informatively are under NDA, I'd say you're not likely to get a satisfying answer any time soon. That said, it could go either way. If I were to put money on it though, I'd say that parameter sniffing is likely always going to be an issue unless they've come up with a way to store different plans for different parameters. – Ben Thul Jun 26 '13 at 12:57

2 Answers2

3

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.

Gjorgji
  • 22,458
  • 10
  • 31
  • 39
0

As far as I know when you create "native" stored procedure it will be compiled to the native code immediately and it will not go through Query Optimizer. So I don't think "parameter sniffing" issue will be an issue.

sha
  • 17,824
  • 5
  • 63
  • 98
  • 1
    The Query Optimizer will be invoked during procedure compilation and the execution plan will be determined at that point (i.e. there will be a single execution plan for all invocations). – Gjorgji Oct 04 '13 at 16:18