Accessing remote data in SQL Server is inevitable sometimes and often people use linked servers for this task. Linked servers are a good option because they are easy to setup and the maintenance is relatively easy but they quickly realize the performance issues when accessing the remote data.

There are many issues with linked server performance and one of the reason for having performance problems is because of the security setup for the linked server login. Even though the user has access to SELECT the data unless the linked server login is a member of sysadmin role or db_ddladmin role or db_owner role or own the table, the user may NOT have access to the STATISTICS of the data. If you don’t have access to the STATISTICS of the data, it is inevitable to get bad query plans and bad performance for the queries.

This problem was first highlighted by Linchi Shea (blog) and more recently with a detailed technical post by Benjamin Nevarez (blog | twitter). I would recommend to read the below good posts on this topic.

Linchi Shea : Performance impact linked server security configuration and how it can hurt you

Linchi Shea : Linked server permissions and distributed query performance

Benjamin Nevarez : Optimizer Statistics on Linked Servers

Is there a way to identify the performance issues caused by this problem. Yes, the first option would be to check the logins have appropriate permissions and hope you get good query plans or you could use the below script to identify the problem using the default trace. When you issue a SELECT query against the remote server, SQL Server will try to use the statistics on the remote table using DBCC SHOW_STATISTICS but in this case (where the remote user doesn’t have access to STATISTICS) that command fails. And default trace captures this action.

Note that default trace monitors few activities on the server silently in the background and one such activity is “Audit DBCC Event”. To identify all of the activities tracked by the default trace, use the below query.

--Query to get all the activities tracked by the default trace
SELECT
	trace_event_id AS Trace_Event_ID
	, e.name AS Event_Description
FROM ::fn_trace_geteventinfo(1) t
    JOIN sys.trace_events e ON t.eventID = e.trace_event_id
GROUP BY trace_event_id, e.name

--Query to figure out if the system is affected by the linked server performance issue with security and statistics
DECLARE @filename VARCHAR(255)
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\' , REVERSE(path))+1) + '\Log.trc'
FROM sys.traces
WHERE is_default = 1;  

--Check for failed DBCC events
SELECT	gt.HostName
		, gt.ApplicationName
		, gt.ServerName
		, gt.TEXTData
		, gt.LoginName
		, gt.spid
		, gt.StartTime
		, gt.Success
		, gt.EventClass
		, te.Name

FROM [fn_trace_gettable](@filename, DEFAULT) gt
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id
WHERE EventClass = 116 --'Audit DBCC'
	AND gt.Success = 0 --Check for failures
	AND gt.TextData LIKE 'dbcc show_statistics(@qtbl, @statname) with stat_header join density_vector%'
ORDER BY StartTime;
GO

The results might look like below and one has to run the script on the target server and NOT from the originating server. If you get any rows back from the above query then you are being affected by this problem and to mitigate this you have to add the permissions as discussed above.

Identify Linked Server Performance issue using Default Trace

There is a open connect item on this issue created by none other than Erland Sommarskog (blog) and please give it a vote up if you consider its important.

https://connect.microsoft.com/SQLServer/feedback/details/475804/permissions-to-access-statistics-should-for-select-permissions-for-the-object