We have a 1.5tb clustered columnstore table, 900 partitions, on SQL Server 2016.
We created a linked server on a 2012 box, pointing at this server.
Doing a simple "select fielda, fieldb, fieldc from servera.databasea.dbo.tablea where partition_field = b and otherfield = c" takes 63 seconds.
I have sysadmin on both so I have the right to stats, but when I run a query sp_whoisactive shows that it's running dbcc show_statistics(@qtbl, @statname) with stat_header join density_vector for (as far as we can see) exactly 60 seconds before it changes to doing a query, at which point it finishes in 3 seconds.
So every linked server query takes at least 60 seconds. I've tried building the linked server using "SQL Server" and the Microsoft OLE Provider for SQL Server, and both do the same thing. Is there any way to fix this behavior? Yes, we're moving off of 2012 in the next couple of months, but we have some pressing data needs before then. Our fallback is to copy the data ranges we need, but that could get ugly.
The reason for the linked server (as opposed to openquery) is to minimize the amount of code changes we need to make - if we can just point a view at that table, then no other code needs to be changed.
Collation Compatible is set to true, Data Access set to true, RPC & RPC Out are set to true.
Thanks.
You can simply put the OPENQUERY version inside a view.
CREATE VIEW dbo.whatever AS
SELECT cols FROM OPENQUERY(...);
OPENQUERY() is essentially doing what you'd be doing if you:
servername.databasename.sys.sp_executesql @sqlIn those cases (which I know aren't viable options for you) I don't believe you'd be seeing the DBCC command running at all.