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.
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.
-- Create the Event Session
IF EXISTS
(
SELECT *
FROM sys.server_event_sessions
WHERE name = 'BulkInserts'
)
DROP EVENT SESSION BulkInserts ON SERVER;
GO
EXECUTE xp_create_subdir 'C:DatabaseXE';
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:DatabaseXELinkedServerMonitor', 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.
The post Powerful Monitoring for Your Linked Servers with XEvents first appeared on SQL RNNR.