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.\u00a0 The description of this fix can be found here \u2013> New or Enhanced Features in SQL Server 2012<\/a>.<\/p>\n

So does the fix work?\u00a0 Based on one test I cobbled together today, it certainly seems so.<\/p>\n

I used the following query for my test:<\/p>\n

\r\nSELECT  [c].[charge_no],\r\n        [c].[member_no],\r\n        [c].[provider_no],\r\n        [c].[category_no],\r\n        [c].[charge_dt],\r\n        [c].[charge_amt],\r\n        [c].[statement_no],\r\n        [c].[charge_code]\r\nFROM    [dbo].[charge] AS c\r\nINNER JOIN [JOSEPHSACK-PC\\TIBERIUS].Credit.dbo.charge AS rc\r\n        ON [c].[charge_no] = [rc].[charge_no]\r\nWHERE   [rc].[member_no] = 7894\r\nOPTION  (RECOMPILE);\r\nGO\r\n<\/pre>\n
I\u2019m joining the charge tables across two SQL Server 2012 SP1 instances.\u00a0 My linked server account only has SELECT permission on the charge table on the remote server.<\/p>\n
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<\/a>):<\/p>\n
<\/a><\/p>\n
<\/a><\/p>\n
Now compare this to a plan with the 9485 flag turned off on the remote SQL Server 2012 SP1 instance:<\/p>\n
<\/a><\/p>\n
<\/a><\/p>\n
So my user account for the linked server no longer requires broader permissions to gather statistics \u2013 in SQL Server 2012 SP1.<\/p>\n
Thank you to the SQL Server product team for getting in this change!\u00a0 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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.\u00a0 The description of this fix can be found […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[42,28],"tags":[],"class_list":["post-469","post","type-post","status-publish","format-standard","hentry","category-cardinality-estimation","category-performance"],"yoast_head":"\n