A couple of weeks ago I was asked to take a look on a Database View that had some serious performances issues, but with some odd behavior.
I will describe the scenario for you here..
- SQL Server 2000 SP4 Engine, Standard Edition (build 2055)
- This is a very complex view with several subqueries in it, many of them in the Where clause in many others as part of the result set.
The application were this was running it's an old version of an application which uses ODBC to connect to the database.
The thing is that same query to that view runned within the application and from Query Analyzer or SSMS gave different behaviors.
In the first case the query seems to hang and never return the results.
In the second case the query returned the results in just 5 secs.
So, I took several approaches to this problem, here I will enumerate everything that I've considered and tested in order to get to a solution.
- I ruled out the possibility of the problem was the query or the view itself seems the query works on from SSMS or Query Analyzer, so it could not be the query syntax by itself.
- I ran the query using the option SET SHOWPLAN_ALL ON, so it throws me the execution plan.
The problem here it's that I needed to emulate the ODBC connection to the DB, so I needed to get a third party tool from my reliable Pendrive (my personal toolbox), a little application totally portable called WINSQL which connects to any database using the ODBC connections from the local machine. So I ran it in both once in SSMS and another run with WINSQL.
The results from both apps I put it in another app from my toolbox WinMerge to compare both plans line by line.
Here was my surprise when I found out that the same query throwed very different results depending if you use SSMS or WINSQL to run it.
So my first question was, why the hell the SQL optimizer is producing two different plans for the exactly same query.
My first thought was, must be the god damn ODBC Driver. So I performed another battery of test making the following changes on the ODBC Drivers:
- Ran the query using the SQL Server Native Cliente Drivers from SQL 2008 SP1.
- Replace the sqlsrv32.dll DLL from MDAC 2.81 SP1 from the one from MDAC 2.81 SP2.
- Installed a third party ODBC Driver using the DataDirect SQL Wired Drivers.
Non of these test produced different results, the execution plan for every of them was the exactly horrible plan from the beginning.
So I start to think that may be some specific configuration from the ODBC could be messing the plan, my first thoughts were the SET ANSI_NULLS and QUOTED_IDENTIFIERS and the PACKETSIZE. So I did these test to probe if I was right:
Ran the query changing in the ODBC Connection String the following parameters:
- SET QUOTED_IDENTIFIERS and ANSINULLS ON
- SET QUOTED_IDENTIFIERS and ANSINULLS OFF
- SET QUOTED_IDENTIFIERS ON and ANSINULLS OFF
- SET QUOTED_IDENTIFIERS OFF and ANSINULLS ON
- All the same of above both specifically set the PACKETSIZE in the connection string to the same value as it's configured on the SQL Server (using sp_configure to know which value was).
Non of the tests worked as expected, everyone throw the exact laussy execution plan.
So finally scratching my head without much more in it, I start to make a deep search on the wave to try to find anything about this kind of behavior.
Finally one blog article describe that some oddities in the execution plan could occur when you have the SET ARITHABORT user option OFF.
And checking I found out that the damn option was automatically set as default from the Query Analyzer or SSMS (it's a default in the user preferences).
But since this is a execution parameter the ODBC does not have any way to pre-set this to be a default, so of course the ODBC was taking the default settings configured on the Database or Instance level of SQL Server which off course was setted in OFF (it's the default).
I made a final test and found that when I explicitly SET ARITHABORT ON on the ODBC query pane and execute it with the query TA DA, magically the query return results in about 5-6 secs and the execution plan it's identical to the produced when run it from SSMS.
According to BOL, the change of the choices of the optimizer to generate the execution plan it's affected by the ARITHABORT when you have Indexed Views or Computed Columns (which I don't have in my database).
So this was the solution, I mean I only had to change the Database ARITHABORT option setting using ALTER DATABASE Statement and everything start to work fine (don't reboot or outage needed).
For a quick fix was good, well quick(?).. It took me the whole day to find this out.
A couple of days later there was something bothering me, because it does not make sense that the ARITHABORT option affected the execution plan when non of the conditions to it was present, so I started to make a deep analysis of the problem.
What I found out it's that the execution plan for this View has an operation on it called RANK, this operation as per it's description on the visual plan, says that creates a computed column to sort the results. TA DA again, that was the reason, the optimizer was creating at runtime a computed column to perform some kind of sorting and therefore triggering the ARITHABORT constraint.
I still have to analyze which part exactly of this gigantic view triggers the RANK operation (I didn't have time to do it yet), but I will.
This is a pretty weird case at least in my experience and make you thing twice when you analyze something like this because many times the optimizer could do things that indirectly affects the behavior or add constraints to the behavior of the resulting execution plan.
Hope this helps to anyone!.
MAX!
No comments:
Post a Comment