Natively Compiled Procedures and Bad Execution Plans

Uncategorized
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: 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…
Read More