Database Reference
In-Depth Information
Figure 23-16.
A successful in-memory table migration using the wizard
The Memory Optimization Advisor can then identify which tables can physically be moved into memory and can
do that work for you. But, it doesn't have the judgment to know which tables should be moved into memory. You're
still going to have to think that through on your own.
Native Compilation Advisor
Similar in function to the Memory Optimization Advisor, the Native Compilation Advisor can be run against an
existing stored procedure to determine whether it can be compiled natively. However, it's much simpler in function
than the prior wizard. To show it in action, I'm going create two different procedures, shown here:
CREATE PROCEDURE dbo.FailWizard (@City NVARCHAR(30))
AS
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 WITH ( NOLOCK)
ON cr.CountryRegionCode = sp.CountryRegionCode
WHERE a.City = @City;
GO
CREATE PROCEDURE dbo.PassWizard (@City NVARCHAR(30))
AS
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;
GO
