ANSI Connection Settings

It’s reasonably well known that you can get different execution plans if you change the ANSI connection settings. But the question comes up, fairly often, how do you know what the settings are. It’s actually surprisingly simple. They’re stored right inside the execution plan, but they’re in one of the operators that most people ignore. Heck, I used to ignore this operator. Which operator is it you ask? Let’s find out.

Let’s use AdventureWorks2008R2 (because I’m lazy). We’ll call one of the stored procedures there like so:

EXEC dbo.uspGetBillOfMaterials @StartProductID = 0, -- int
    @CheckDate = '2011-03-10 02:31:39' – datetime

If you execute this with “Include Actual Execution Plan” enabled you’re likely to end up with the following execution plan:

Don’t worry about the fact that you can’t really read this plan, we’re only focused on one operator this time. All the way on the left, right at the beginning of the plan (and plans are laid out logically from left to right), you see the lonely little SELECT operator. This operator is actually chock full of all kinds of Execution Plan Goodness, but it’s frequently ignored.If you right click on the operator and bring up the Properties sheet for it, scrolling down near the bottom (everything is in alphabetical order) you’ll find the property, “Set Options” with a little plus sign next to it. Expanding that out, you can see all the ANSI settings that were used when this query was executed:

If you use a trace to capture actual execution plans or you capture them through SSMS, you can get this information.