Sql-server – Slow Linked Server INSERTs on SQL Server 2014

insertlinked-serversql serversql server 2014

My local and remote servers were all using SQL Server 2008 R2 but the local ones were recently upgraded to SQL Server 2014 and the performance of Remote Queries has been crippled (literally from minutes to hours).

It appears that the problem is with sending data to the Remote Server so, as long as I am just reading data from the Remote Server, it performs fine except that, if the query is based on data at the local end, it seems to be effectively sending data to the remote server. I cannot setup the Linked Servers on the Remote Server end so I cannot make the remote server pull from the local one.

In most cases I can rewrite the queries to pull the data from the remote server into a local table, filter the local data, and then just send the relatively few updates to the remote server – Still slower than it was, but functional.

However, when doing anything involving lots of records, it is unuseable. The classic example is this query (originally it was doing a more complex query on the local server so I changed it to first populate a local copy of the final table so that I can do the simplest possible query for the INSERT);

INSERT INTO [REMOTE-DB].Databasename.dbo.Test_Table
SELECT Person_Nbr, FullName, Birth
FROM Test_Table WITH (nolock)

Even when I try OPENQUERY it seems no faster;

INSERT OPENQUERY ([REMOTE-DB], 'SELECT Person_Nbr, FullName, Birth FROM Databasename.dbo.Test_Table')
SELECT Person_Nbr, FullName, Birth
FROM Test_Table WITH (nolock)

If it weren't that this worked almost instantly on the older version of SQL Server, I would accept this as a fact of life but there is such a huge difference in performance that it seems that there must be some obvious fix that I am missing. I don't have control of the local servers but the DBA for them claims that the database configuration is consistent with the old setup.

Do you have any ideas what I can do to fix this or what I can point the DBA at?

Best Answer

"My local and remote servers were all using SQL Server 2008 R2 but the local ones were recently upgraded to SQL Server 2014 and the performance of Remote Queries has been crippled (literally from minutes to hours)."

When Sql Server performance degrades substantially, my first response is usually "what has changed?"

Well - you just upgraded the local server to Sql Server 2014 which introduces a new Cardinality Estimator (CE).

As quoted in the blog (Careful Testing the 2014 Cardinality Estimator with Trace Flags):

When you migrate to SQL Server 2014, rather than turning on the new cardinality estimator right away, it's recommended to stick with the old cardinality estimator initially by leaving your database in SQL Server 2012 compatibility mode. Let the database settle down for a few weeks, let folks air out their grievances, and give yourself some time to make sure the system is working at least as well as the old system.

You say that "the DBA (for the local server) claims that the database configuration is consistent with the old setup". So, how can you tell if you are being impacted by the new CE? There are a few ways.

First, run this query against the local instance.

select name, compatibility_level from sys.databases

If the compatibility level is 120 or higher, you certainly 'might' be using the new CE (assuming it has not been overridden by using session or global trace flags, or QUERYTRACEON hints on the query that force the use of the older CE.)

Another way to see if the new CE is being used is to look at the execution plan XML for a 'local' query and locate the string 'CardinalityEstimationModelVersion'.

enter image description here

If it's 70, you're using the old CE. If it's 120, you're using the new CE.

My suggestion is to 'first' figure out whether you are being bitten by the new Sql Server 2014 CE.

There is quite a bit of information on the Internet about this topic.

Another helpful link is The SQL 2014 Cardinality Estimator Eats Bad TSQL for Breakfast.

Related Question