ANSI settings

Question

ANSI settings

Igor Micev

SSC-Dedicated

Points: 33110
More actions
April 3, 2013 at 8:25 am

#292039

Hi All,
There is an application that uses DSN. The connection type is ODBC. In the DSN the ANSI settings are set to ON and on database level they are all OFF (http://msdn.microsoft.com/en-us/library/ms175088(v=sql.105).aspx). I'm aware that changing them can cause troubles to the application. The application is a "black box" and does not use stored procedures, i.e. only in-code queries.
Do these differences in the settings cause less reuse of the query plans?
BR
IgorMi
Igor Micev,My blog: www.igormicev.com

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

Just my two cents.

The execution plan is saved within the cashe allocated to SQL Server instance. It is not related to the other setting outside SQL Server.

The ANSI setting affects the query durations. Here is a link. May it help.

http://msdn.microsoft.com/en-us/library/ms177259.aspx

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 2

SQL ORACLE (4/3/2013)
Just my two cents.
The execution plan is saved within the cashe allocated to SQL Server instance. It is not related to the other setting outside SQL Server.
The ANSI setting affects the query durations. Here is a link. May it help.
http://msdn.microsoft.com/en-us/library/ms177259.aspx

However the ODBC overwrites the default database settings. The questions is "Is the ODBC overwrite enough for reusing the already existing plans of queries".

Thank you

Igor Micev,My blog: www.igormicev.com

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

The execution plan is saved by SQL Server after the first run. The plan will be changed if you changed your query.

If you run the same query twice, you can always find that the first run always takes more time. It is an indication that your execution plan is saved by SQL Server.

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 4

So it seems that according to table on http://msdn.microsoft.com/en-us/library/ms175088(v=sql.105).aspx the default settings does not matter because it all depends on the ODBC connection settings which overwrite most of the settings.

Igor Micev,My blog: www.igormicev.com

Igor Micev SSC-Dedicated Points: 33110 More actions · Answer 5

SQL ORACLE (4/3/2013)
The execution plan is saved by SQL Server after the first run. The plan will be changed if you changed your query.
If you run the same query twice, you can always find that the first run always takes more time. It is an indication that your execution plan is saved by SQL Server.

Hi I know about this.

Igor Micev,My blog: www.igormicev.com