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  .[charge_no],
        .[member_no],
        .[provider_no],
        .[category_no],
        .[charge_dt],
        .[charge_amt],
        .[statement_no],
        .[charge_code]
FROM    [dbo].[charge] AS c
INNER JOIN [JOSEPHSACK-PC\TIBERIUS].Credit.dbo.charge AS rc
        ON .[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.

Related Posts