Database Reference
In-Depth Information
This means there is no way to look at the statistics of the in-memory indexes. However, those statistics will still
get out-of-date as your data changes. What's more, they're not automatically updated with data changes regardless
of the settings on your systems. So, you have to plan on running the updates yourself. You can use
sp_updatestats
.
The 2014 version of the procedure is completely aware of in-memory indexes and their differences. You can also use
UPDATE STATISTICS
, but you must use
FULLSCAN
or
RESAMPLE
along with
NORECOMPUTE
as follows:
UPDATE STATISTICS dbo.Address WITH FULLSCAN, NORECOMPUTE;
If you don't use this syntax, it appears that you're attempting to alter the statistics on the in-memory table, and
you can't do that. You'll be presented with a pretty clear error.
Msg 41346, Level 16, State 2, Line 1
CREATE and UPDATE STATISTICS for memory optimized tables requires the WITH FULLSCAN or RESAMPLE and
the NORECOMPUTE options. The WHERE clause is not supported.
Defining the sampling as either
FULLSCAN
or
RESAMPLE
and then letting it know that you're not attempting to turn
on automatic update by using
NORECOMPUTE
, the statistics will get updated. You'll need to do this in a regular schedule
based on the volatility of your data, but there's no clear way at this point to observe the statistics themselves.
In-memory indexes aren't accessed from disk, so there is no fragmentation you need to worry about these
indexes, eliminating the need to defragment them.
Natively Compiled Stored Procedures
Just getting the table into memory and radically reducing the locking contention with the optimistic approaches
results in fairly impressive performance improvements. But to really make things move quickly, you can also
implement the new feature of compiling stored procedures into a DLL that runs within the SQL Server executable.
This really makes the performance scream. The syntax is straightforward. This is how you could take the query from
before and compile it:
CREATE PROC dbo.AddressDetails @City NVARCHAR(30)
WITH NATIVE_COMPILATION,
SCHEMABINDING,
EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
SELECT a.AddressLine1,
a.City,
a.PostalCode,
sp.Name AS StateProvinceName,
cr.Name AS CountryName
FROM dbo.Address AS a
JOIN dbo.StateProvince AS sp
ON sp.StateProvinceID = a.StateProvinceID
JOIN dbo.CountryRegion AS cr
ON cr.CountryRegionCode = sp.CountryRegionCode
WHERE a.City = @City;
END
