the case of the unexplained - how we can get different execution plans and result sets for same query

The other day, my colleague and a good friend called me and told that her SQL Server gone nuts.
One of developers launched a query and it run for ages, once she executed the same query it returned results in a few seconds.
So how something like this can happen ?

Each compilation is done for specific set of server, database, user, connection and set options.
The most common cause in a difference between execution plans for same query, is set option "ARITHABORT". In most connection string builders it is "off" by default. In SQL Server Management Studio it's "on" by default.
Once we get problematic query we try to troubleshot it within Management Studio and might get different execution plan.

So ARITHABORT was my immediate suspect and I asked my colleague to check it.
We can compare set options of different connections by querying sys.dm_exec_sessions DMV.
Surprisingly it wasn't the case, however DMV showed difference between my colleague and application login languages - us_english and polski.

At this stage I was almost sure that execution plan was not the only thing that was different.
As a result of language difference and usage of "dependent" date format the resultset was different too, as SQL Server implicitly converted date string like "07/10/2012" to different dates October 7th and July 10th.

We'll talk about "dependent" and "independent" date formats in one of our future posts.

As you can see, the execution plan can be influenced by different factors and it is important to be aware of this.
You are welcome to explore sys.dm_exec_sessions DMV and sys.dm_exec_plan_attributes function. (https://msdn.microsoft.com/en-us/library/ms176013.aspx, https://msdn.microsoft.com/en-us/library/ms189472.aspx)

Comments

• Anonymous
  January 01, 2003
  Nice article

• Anonymous
  December 14, 2012
  Thanks for this helpful info,