2

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
  • 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? – GregGalloway Nov 2 '15 at 6:17
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.