November 19, 2016 at 9:53 am
Our environment was running on SQL 2012 with the database in SQL 2005 compatibility mode (90). The application database has been updated to run in native 2012 (finally) and an old FreeTDS driver for the application servers has been replaced with the MS ODBC driver for SQL server for Linux.
Post update the most common (and simple - just a select on one table with two simple where conditions (the table fields have not changed field types)) query is now performing an index scan as part of its query plan instead of its previous index seek and is going parallel (it wasn't before).
Looking at the plan, it appears an implicit conversion (between char and nchar) is now occuring on a column in the where predicate (which has a unique non-clustered index on it). So suddenly we have a load of CXPACKET waits and the query takes forever. We have rebuilt the indexes, updated the stats, cleared the plan cache etc but the problem remains. MAXDOP settings remain the same.
The developer of the application has an identical environment (allegedly) and is not experiencing the problem with similarly sized tables and indexes. I feel like we are missing a setting somewhere (possibly in the new ODBC driver?).
Does anyone have any ideas as to what might be causing this?
November 19, 2016 at 11:16 am
Is your app telling your queries to parameterize, i.e. do they look like this in Profiler:
exec sp_executesql ...
If they do and you default everything this is what will happen - all parameters will be declared as Nvarchar somethings. A swift and painful CONVERT_IMPLICIT death follows.
Can you post some of the queries you see from profiler?
If you run the same queries in SSMS query window do they still show same issues?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 20, 2016 at 8:08 am
Quick question, which version of FreeTDS are you using?
😎
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply