When I am looking for random failures or looking to resolve performance issues, one of the first things I like to check is if there are any linked servers that could be contributing to the problem. Linked servers are the bane of good performance and are wrought with all sorts of “hidden” problems such as those outlined in this article – here.
Granted there are times when a linked server makes a lot of sense. When choosing to use a linked server, it must be understood what the purpose of the linked server will be and to not abuse the linked server. Restrict the use of the linked server to a small scope and hopefully an infrequent use. For instance, I have demonstrated good uses of linked servers in my poor mans audit presentation and in my database restore solutions article.
Do you know if you have linked servers? Now is a good time to check. Furthermore, if you do have linked servers do you know the extent of use on these linked servers? It is a quick and painless check to determine if linked servers have invaded your environment.
Linked Server Invasion?
If you need to check for the existence of linked servers in your environment, this quick tutorial can catch you up to speed and help with that task. As a short-cut here is a query that can help as well.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT a.name AS LinkedSvrName, a.product AS ConnectionType, a.provider, a.data_source, a.catalog AS DBName, b.local_principal_id AS LocalPrincipalId, b.remote_name AS RemotePrincipalName, b.modify_date, a.provider_string FROM sys.servers a LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id; |
This query will produce the following results in one of my labs.
Clearly, I have some linked servers and now it is a perfect opportunity to implement some monitoring on the linked servers. This is not just simply checking to see if the linked servers are in use – but that is an additional benefit to monitoring.
Monitoring the Linked Servers
If you have never noticed a problem with your linked servers, I am not sure if that is a good thing or a bad thing. Even when everything seems to be working well enough, it is highly probable that something is amiss with the linked servers (uncaught errors, unknown job failures, slower performance than desired, etc etc etc. So, is it a good thing or a bad thing if problems haven’t been noticed? It depends! Is it a bad thing if problems haven’t been discovered and there is no monitoring on the linked servers? That is a resounding yes it is a very bad thing!
If monitoring is so important, how do we go about monitoring the linked servers? That is a good question! The answer to that question was mentioned in the title – Extended Events (XEvents). Through the flexibility of XEvents, we have been given the beautiful gift of being able to monitor so many facets and features of SQL Server that it truly makes our job easier and makes us look like real data super heroes.
If you take advantage of some of the tools I have shared and use the KEYWORD in one of the discovery scripts to search for “oledb”, you will discover a wealth of events created to monitor various different features such as logshipping, fulltext, replication and even linked servers. I will be using several events from this KEYWORD group in order to create a monitoring session for linked servers.
XEvents Session to the Rescue
Without further ado, here is a session for XEvents to help monitor the linked servers on your database servers.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 |
-- Create the Event Session IF EXISTS ( SELECT * FROM sys.server_event_sessions WHERE name = 'LinkedServerMonitor' ) DROP EVENT SESSION LinkedServerMonitor ON SERVER; GO EXECUTE xp_create_subdir 'C:\Database\XE\'; GO CREATE EVENT SESSION [LinkedServerMonitor] ON SERVER ADD EVENT sqlserver.oledb_data_read (ACTION ( package0.callstack , sqlserver.client_app_name , sqlserver.database_name , sqlserver.nt_username , sqlserver.query_hash , sqlserver.query_plan_hash , sqlserver.session_id , sqlserver.sql_text , sqlserver.client_hostname , sqlserver.tsql_stack , package0.event_sequence , sqlserver.context_info , sqlserver.client_connection_id , sqlserver.username , sqlserver.database_id ) ) , ADD EVENT sqlserver.oledb_query_interface (ACTION ( package0.callstack , sqlserver.client_app_name , sqlserver.database_name , sqlserver.nt_username , sqlserver.query_hash , sqlserver.query_plan_hash , sqlserver.session_id , sqlserver.sql_text , sqlserver.client_hostname , sqlserver.tsql_stack , package0.event_sequence , sqlserver.context_info , sqlserver.client_connection_id , sqlserver.username , sqlserver.database_id ) ) , ADD EVENT sqlserver.oledb_error (ACTION ( package0.callstack , sqlserver.client_app_name , sqlserver.database_name , sqlserver.nt_username , sqlserver.query_hash , sqlserver.query_plan_hash , sqlserver.session_id , sqlserver.sql_text , sqlserver.client_hostname , sqlserver.tsql_stack , package0.event_sequence , sqlserver.context_info , sqlserver.client_connection_id , sqlserver.username , sqlserver.database_id ) ) , ADD EVENT sqlserver.oledb_provider_information (ACTION ( package0.callstack , sqlserver.client_app_name , sqlserver.database_name , sqlserver.nt_username , sqlserver.query_hash , sqlserver.query_plan_hash , sqlserver.session_id , sqlserver.sql_text , sqlserver.client_hostname , sqlserver.tsql_stack , package0.event_sequence , sqlserver.context_info , sqlserver.client_connection_id , sqlserver.username , sqlserver.database_id ) ) , ADD EVENT sqlserver.oledb_provider_initialized (ACTION ( package0.callstack , sqlserver.client_app_name , sqlserver.database_name , sqlserver.nt_username , sqlserver.query_hash , sqlserver.query_plan_hash , sqlserver.session_id , sqlserver.sql_text , sqlserver.client_hostname , sqlserver.tsql_stack , package0.event_sequence , sqlserver.context_info , sqlserver.client_connection_id , sqlserver.username , sqlserver.database_id ) ) ADD TARGET package0.event_file (SET filename = N'C:\Database\XE\LinkedServerMonitor', max_file_size = (256), max_rollover_files = (2)) , ADD TARGET package0.histogram (SET filtering_event_name = N'sqlserver.oledb_data_read' , slots = (10000) --modify depending on your needs , source = N'sqlserver.tsql_stack') WITH ( MAX_DISPATCH_LATENCY = 5 SECONDS , TRACK_CAUSALITY = ON , STARTUP_STATE = ON ); GO ALTER EVENT SESSION LinkedServerMonitor ON SERVER STATE = START; |
This session will help monitor for errors, linked server activity, linked server access and linked server provider information. Given a bit of time to run, the session could output information such as shown in the following image.
As can be seen in the preceding image, I was able to trap events related to my linked servers with just a minimal of effort via XEvents. In an environment that was seemingly working without problems I even have a handful of errors that should be investigated related to my linked servers.
Put a bow on it
Linked Servers can certainly provide an enigma to your finely tuned Database environment. In this article, I shared how to decrypt that enigma and provide greater value through monitoring of the linked servers. How is this monitoring performed? It is done through the super powers of Extended Events.
Interested in learning about some additional deep technical features via XEvents? Check out this library of articles!
This is the ninth article in the 2020 “12 Days of Christmas” series. For the full list of articles, please visit this page.
Thank you for this. Why is the top of the script deleting a BulkInserts session IF EXISTS?
Well, my editor (*shrug* – me) didn’t catch it. Truth be told, I was going to publish multiple sessions in the article and just didn’t clean all of that session out. That session is actually coming up in a follow-up article.
Hello, does not appear to work with SQL 2012. This error is reported.
Msg 25623, Level 16, State 1, Line 14
The event name, “sqlserver.oledb_provider_initialized”, is invalid, or the object could not be found
Here is a version for SQL Server 2012.
-- Create the Event SessionIF EXISTS
(
SELECT *
FROM sys.server_event_sessions
WHERE name = 'LinkedServerMonitor'
)
DROP EVENT SESSION LinkedServerMonitor ON SERVER;
GO
EXECUTE xp_create_subdir 'C:\Database\XE\';
GO
CREATE EVENT SESSION [LinkedServerMonitor]
ON SERVER
ADD EVENT sqlserver.oledb_data_read
(ACTION
(
package0.callstack
, sqlserver.client_app_name
, sqlserver.database_name
, sqlserver.nt_username
, sqlserver.query_hash
, sqlserver.query_plan_hash
, sqlserver.session_id
, sqlserver.sql_text
, sqlserver.client_hostname
, sqlserver.tsql_stack
, package0.event_sequence
, sqlserver.context_info
, sqlserver.client_connection_id
, sqlserver.username
, sqlserver.database_id
)
)
, ADD EVENT sqlserver.oledb_query_interface
(ACTION
(
package0.callstack
, sqlserver.client_app_name
, sqlserver.database_name
, sqlserver.nt_username
, sqlserver.query_hash
, sqlserver.query_plan_hash
, sqlserver.session_id
, sqlserver.sql_text
, sqlserver.client_hostname
, sqlserver.tsql_stack
, package0.event_sequence
, sqlserver.context_info
, sqlserver.client_connection_id
, sqlserver.username
, sqlserver.database_id
)
)
, ADD EVENT sqlserver.oledb_error
(ACTION
(
package0.callstack
, sqlserver.client_app_name
, sqlserver.database_name
, sqlserver.nt_username
, sqlserver.query_hash
, sqlserver.query_plan_hash
, sqlserver.session_id
, sqlserver.sql_text
, sqlserver.client_hostname
, sqlserver.tsql_stack
, package0.event_sequence
, sqlserver.context_info
, sqlserver.client_connection_id
, sqlserver.username
, sqlserver.database_id
)
)
, ADD EVENT sqlserver.oledb_provider_information
(ACTION
(
package0.callstack
, sqlserver.client_app_name
, sqlserver.database_name
, sqlserver.nt_username
, sqlserver.query_hash
, sqlserver.query_plan_hash
, sqlserver.session_id
, sqlserver.sql_text
, sqlserver.client_hostname
, sqlserver.tsql_stack
, package0.event_sequence
, sqlserver.context_info
, sqlserver.client_connection_id
, sqlserver.username
, sqlserver.database_id
)
)
--, ADD EVENT sqlserver.oledb_provider_initialized
-- (ACTION
-- (
-- package0.callstack
-- , sqlserver.client_app_name
-- , sqlserver.database_name
-- , sqlserver.nt_username
-- , sqlserver.query_hash
-- , sqlserver.query_plan_hash
-- , sqlserver.session_id
-- , sqlserver.sql_text
-- , sqlserver.client_hostname
-- , sqlserver.tsql_stack
-- , package0.event_sequence
-- , sqlserver.context_info
-- , sqlserver.client_connection_id
-- , sqlserver.username
-- , sqlserver.database_id
-- )
-- )
ADD TARGET package0.event_file
(SET filename = N'C:\Database\XE\LinkedServerMonitor', max_file_size = (256), max_rollover_files = (2))
, ADD TARGET package0.histogram
(SET filtering_event_name = N'sqlserver.oledb_data_read'
, slots = (10000) --modify depending on your needs
, source = N'sqlserver.tsql_stack')
WITH
(
MAX_DISPATCH_LATENCY = 5 SECONDS
, TRACK_CAUSALITY = ON
, STARTUP_STATE = ON
);
GO
ALTER EVENT SESSION LinkedServerMonitor
ON SERVER STATE = START;
Thank you very much!!
Do you have right-click and copy disabled from your website? I was trying to copy the code you have in the article and it wouldn’t copy (Edge, FireFox, Chrome). I used the copy code option in the widget and also manually highlighted it also. Neither worked. Also tried to right click one of your links to open another articole on a separate tab. That didn’t work either. (I guess you can also see that you can’t right click in comments to correct spelling.. I tried.). hmmmmm….
Yes. It is an aggressive content copy protection due to several cases of article plagiarism in the past year. I am looking for something that is a little better about letting scripts to actually be copied.
A little testing shows that if you use the pop-out window on the code segments, it will allow you to copy from those pages. The alternative is to register as a user.
Jason
I may be very dim, but I can’t see anywhere on the website to register as a user?
Thanks in advance
Nothing you were doing wrong. My registration had become hidden during a hosting provider migration. I have made it visible again. In addition, I made it more readily accessible by moving it to a menu at the top.
Thanks for the response. I registered as a user and things worked. Well they worked in chrome, but not in firefox. Weird. I guess the ‘code in new window’ option is the best way. Having to log in as a user is pita.