SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Posted by Nakul Vachhrajani on 21 March 2011


I can't believe I didn't even try to look at the properties of the SELECT operator till date!

Thank-you for showing us the obvious, and exposing us to something new for this week!

Thanks a ton!

Posted by LondonNick on 21 April 2011

Dbcc useroptions can be used too.

Posted by tsmall 52653 on 27 April 2012

Oh my goodness! I can't belive I'e missed that info all this time.

Thanks Tony S.

Leave a Comment

Please register or log in to leave a comment.