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.
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.
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.
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.