Conor vs. Distributed Query Provider Hints

(I promised I’d write more )

Today I will talk a bit about Distributed Query (DQ), a feature that lets you query across databases/servers.  I’ve previously posted an academic paper we wrote on the subject some time back (https://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.59.8007), but today I’ll post something up there for folks considering how query plans get optimized.

SQL Server’s optimizer does lots of different things before a plan is selected.  It considers lots and lots of alternatives and tries to pick one that it thinks will be cheap to run.  Usually this works fine, but there are some nuances to this process.  In distributed query, most of those nuances are tied to which provider you are talking.  If you are talking to another SQL Server, then life is pretty good for customers – we do most of the work to get queries to largely remote without too much of an issue for you.  However, if you are a different provider (either for another database product or perhaps you are writing a custom provider to expose data so that you can use the SQL Server QP to query it), then things get a bit more complicated.

In DQ, SQL Server has an operator algebra and it tries to find portions of queries that it can remote.  It does that by converting our internal operator tree back into a SQL statement that can be executed by the back-end provider.  If that back-end provider is a SQL-exposing provider (some just expose tables with no query capability), then DQ needs to understand the nuances of the SQL grammar.  It needs to understand details of its transaction coordinator.  It needs to understand its data types (which do not always line up exactly with those in SQL Server).  As such, there are a lot of important details to get a query that you execute remotely to return the same result as if you had just pulled a copy of the table from the remote server over and executed a local SQL Server query.

There are some cases where SQL Server will NOT remote things because there is a potential to get slightly different results between the local and remote cases.  Often, this is not a problem for most customers, but it can be a concern for things like banks where money is involved .  SQL Server exposes a set of optional properties for provider writers to help give hints to SQL Server’s QP about what is safe to remote and what is not safe.  By default, we assume it is NOT safe to remote, so there is value in paying attention to these if you want to get queries to remote more successfully through the DQ feature.

We expose these through a special optional property set that the DQ feature looks for in each provider.  The documented logic is here:

https://technet.microsoft.com/en-us/library/ms178039(SQL.90).aspx

You can see that there are various boolean properties for things like subqueries and group by.  If you are trying to understand why a distributed query is NOT remoting some portion of the query, this is a good place to start if you are using a provider other than SQL Server.  (You should assume SQL Server can remote these things all fine – there are more detailed nuances for the SQL Server-SQL Server cases when things do not remote).

This should exist in all versions of SQL Server from 2000 onwards, so please go take a look at any of your linked server providers and see what they do!

Happy Querying

Conor