How can I reduce OLEDB wait type in a SSAS linked server connection?

Question

I have recently created a linked server to one of my SSAS servers.

when I go ahead and open the catalogs, to see which ssas databases I have there, I use the following procedure:

create procedure sys.sp_catalogs
(
 @server_name sysname
)
as
 select
 CATALOG_NAME = f_rc.CATALOG_NAME,
 DESCRIPTION = convert (nvarchar(255), f_rc.DESCRIPTION)
 from
 sys.fn_remote_catalogs (@server_name, NULL) f_rc
 order by CATALOG_NAME

this is how I call it:

sys.sp_catalogs 'sasbidev01'

When I see it takes too long, I check what it is running:

enter image description here

I see the OLEDB wait type.

Is this on its own, indication that I could do something to improve the speed of this connection?

The Linked server script creation:

USE [master]
GO
IF NOT EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'SASBIDEV01')
BEGIN
EXEC master.dbo.sp_addlinkedserver 
 @server = N'SASBIDEV01'
, @srvproduct=N''
, @provider=N'MSOLAP'
, @datasrc=N'SASBIDEV01'
EXEC master.dbo.sp_addlinkedsrvlogin 
 @rmtsrvname=N'SASBIDEV01'
,@useself=N'False'
,@locallogin=NULL
,@rmtuser=N'mycompany.CO.UK\SASBIDEV01_SSAS'
,@rmtpassword='B4l4r4m4__sbidev01'
END
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SASBIDEV01', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

• GregGalloway: Can you reproduce it two times in a row being slow? I'm wondering if your linked server connection is the first user to connect to SSAS after a reboot. Also describe more what you are trying to accomplish and why a delay is a problem. Is this sp_catalogs something that you need run often?

Answer 1

The OLEDB wait type is what SQL Server uses to report the amount of time it's waiting for a response from code running outside the control of SQL Server via an OleDB provider. Typically, this is from a linked server, and in your particular example is the amount of time SQL Server is waiting for SSAS to provide its response to that query.

There is nothing you can do to SQL Server to speed up this query. You need to ensure SSAS has the dedicated memory required to ensure it never gets paged out of memory to the paging file. This Microsoft Docs page might be helpful.