sqlskills-logo-2015-white.png

Distributed Query Plan Quality and SQL Server 2012 SP1

SQL Server 2012 Service Pack 1 is out today and it fixes an issue that has been around for several versions regarding distributed queries and poor quality execution plans due to bad cardinality estimates when the distributed query principal had insufficient permissions to gather the applicable statistics.  The description of this fix can be found here –> New or Enhanced Features in SQL Server 2012.

So does the fix work?  Based on one test I cobbled together today, it certainly seems so.

I used the following query for my test:

SELECT  [c].[charge_no],
        [c].[member_no],
        [c].[provider_no],
        [c].[category_no],
        [c].[charge_dt],
        [c].[charge_amt],
        [c].[statement_no],
        [c].[charge_code]
FROM    [dbo].[charge] AS c
INNER JOIN [JOSEPHSACK-PC\TIBERIUS].Credit.dbo.charge AS rc
        ON [c].[charge_no] = [rc].[charge_no]
WHERE   [rc].[member_no] = 7894
OPTION  (RECOMPILE);
GO

I’m joining the charge tables across two SQL Server 2012 SP1 instances.  My linked server account only has SELECT permission on the charge table on the remote server.

If I enable trace flag 9485 on the destination SQL Server instance (hosting the remote data) for my session, I can see the legacy behavior in the plan (showing estimates vs. actuals using SQL Sentry Plan Explorer):

image

image

Now compare this to a plan with the 9485 flag turned off on the remote SQL Server 2012 SP1 instance:

image

image

So my user account for the linked server no longer requires broader permissions to gather statistics – in SQL Server 2012 SP1.

Thank you to the SQL Server product team for getting in this change!  This issue has sprung up several times over the years for my own clients and its good to know that there is a supported solution once folks upgrade.

3 thoughts on “Distributed Query Plan Quality and SQL Server 2012 SP1

  1. Thanks for pointing out this improvement. Unfortunately there still appear to be issues with row estimates when views are used over linked servers. If we create a view on the remote side of the form "create view dbo.MyView with schemabinding as select charge_no, member_no from dbo.charge" and use that in the query rather than the remote table we get row estimates of 10,000 rows regardless of the credentials used on the linked server.

    Unfortunately for me this still leaves linked servers a poor second class citizen and a source of many performance issues.

Comments are closed.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.