Database Reference
In-Depth Information
Unfortunately, if you attempt to run this query definition as currently defined, you'll receive the following error:
Msg 10775, Level 16, State 1, Procedure AddressDetails, Line 5530
Object 'dbo.CountryRegion' is not a memory optimized table and cannot be accessed from a natively
compiled stored procedure.
While you can query a mix of in-memory and standard tables, you can create only natively compiled
stored procedures against in-memory tables. I'm going to use the same methods shown previously to load the
dbo.CountryRegion
table into memory and then run the script again. This time it will compile successfully. If you
then execute the query using
@City = 'Walla Walla'
as before, the execution time won't even register inside SSMS.
You have to capture the event through Extended Events, as shown in Figure
23-9
.
Figure 23-9.
Extended Events showing the execution time of a natively compiled procedure
The execution time there is not in milliseconds but microseconds. So, the query execution time has gone from
the native run time of 154ms down to the in-memory run time of 15ms and then finally less than ½ of one millisecond.
That's a pretty hefty performance improvement.
But, there are restrictions. As was already noted, you have to be referencing only in-memory tables.
The parameter values assigned to the procedures cannot accept
NULL
values. If you choose to set a parameter to
NOT
NULL
, you must also supply an initial value. Otherwise, all parameters are required. You must enforce schema binding
with the underlying tables. Finally, you need to have the procedures exist with an
ATOMIC BLOCK
. An atomic blocks
require that all statements within the transaction succeed or all statements within the transaction will be rolled back.
Here are another couple of interesting points about the natively compiled procedures. You can retrieve only an
estimated execution plan, not an actual plan. If you turn on actual plans in SSMS and then execute the query, nothing
appears. But, if you request an estimated plan, you can retrieve one. Figure
23-10
shows the estimated plan for the
procedure created earlier.
