Natively Compiled Procedures and Execution Plans

The combination of in-memory tables and natively compiled procedures in SQL Server 2014 makes for some seriously screaming fast performance. Add in all the cool functionality around optimistic locking, hash indexes and all the rest, and we’re talking about a fundamental shift in behavior. But… Ah, you knew that was coming. But, you can still write bad T-SQL or your statistics can get out of date or you can choose the wrong index, or any of the other standard problems that come up that can negatively impact all those lovely performance enhancements. Then what? Well, same as before, take a look at the execution plan to understand how the optimizer has resolved your queries. But… Yeah, another one. But, things are a little different with the natively compiled procedures and execution plans. I have a very simple little example in-memory database with just a few tables put up into memory and a straight forward procedure that I’ve natively compiled:

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
GO

The fun thing is, even with these really small examples, the performance differences are staggering when compared to standard tables or just in-memory tables alone. Anyway, this is what the estimated plan for this procedure looks like:

Looks like a pretty standard execution plan right? Here’s the actual plan:

No, don’t bother refreshing your browser, that’s just a blank couple of lines because, there is no actual plan. You’re not dealing with a standard query, remember. There are only a couple of reasons to get an actual plan. First, if you’re experiencing recompiles, you might want to see the plan that was ultimately executed. An actual plan will reflect this, as will a plan pulled from cache. Second, you want to see some of the run-time metrics, actual rows, actual executions, runtime parameter values. Well, the first is not an issue since you’re not going to see these things recompile. It’s a DLL. The second could be an issue. I’d like to see actual versus estimated to understand how the optimizer made it’s choices. Regardless, the actual plan won’t generate in SSMS when you execute the natively compiled procedure.

There are some more differences between the plans for natively compiled procedures and standard procedures. I’ll go over a few more in another blog post.

Hey, if you do want to talk query tuning? I’m taking my one day seminar on the road to a bunch of different events. There’s still time to get to the event in Louisville on the 20th of June. That’s right before the SQL Saturday there. Go here to register. I’m also going to be putting this on the day before SQL Saturday Albany. You can register here. I’m very honored to have been selected to speak at SQL Connections in September in Las Vegas. This includes an all day seminar on query tuning. Go here to register for this great event. I’m also very excited to be able to say that I’m also going to be doing a different seminar in Belgium for SQL Server Days. I’ll be presenting an all day seminar on execution plans, including lots of details on SQL Server 2014. Go here to register for this event.

That’s four opportunities to get together and spend an entire day talking about query tuning, execution plans, statistics, the optimizer, extended events, oh, all sorts of things.