25Jun 2014 by Grant Fritchey 2 Comments

The Utility of Execution Plans in Natively Compiled Procedures

SQL Server 2014, TSQL
I'm actually having problems identifying the utility of execution plans when working with natively compiled procedures. Or, put another way, why bother? I've posted a couple of times on natively compiled procedures and SQL Server execution plans. I've found the differences interesting and enlightening, but I'm seriously questioning why I should bother, at least currently. I'm sure there will be many changes to the behaviors of the natively compiled procedures and their relationship with execution plans. But right now, well, let's look at an example. I have three simple tables stored in-memory. Here's the definition of one: [crayon-5a6d1c2e36a2f870032344/] I can then create the following code as a natively compiled procedure: [crayon-5a6d1c2e36a42443868271/] When I call for an estimated plan (remember, no actual plans) I'll get this: If you click on it,…
Read More
17Jun 2014 by Grant Fritchey 2 Comments

Natively Compiled Procedures and Bad Execution Plans

SQL Server 2014
I've been exploring how natively compiled procedures are portrayed within execution plans. There have been two previous posts on the topic, the first discussing the differences in the first operator, the second discussing the differences everywhere else. Now, I'm really interested in generating bad execution plans. But, the interesting thing, I wasn't able to, or, rather, I couldn't see evidence of plans changing based on silly things I did to my queries and data. To start with, here's a query: [crayon-5a6d1c2e37722427829104/] And this is a nearly identical query, but with some stupid stuff put in: [crayon-5a6d1c2e3772f991275862/] I've change the primary filter parameter value to a VARCHAR when the data is NVARCHAR. This difference is likely to lead to differences in an execution plan, although not necessarily. If I load my tables up…
Read More
05Jun 2014 by Grant Fritchey 2 Comments

Natively Compiled Procedures and Execution Plans

SQL Server 2014, TSQL
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…
Read More