High network IO processing cube

Question

Hi,

We have an active / passive SQL Cluster. the Analysis Services & SQL Server are in the same instance (Instance1) which is running on SQL1

When processing the cube, the network IO is maxed out, it looks like the physical SQL Server (SQL1) is writing 2Gbps to the instance name (Instance1) which are on the same box. this is causing timeouts on clients.

Does anyone know why this would be happening? and how to stop it? i assumed as both OLAP and SQL are in the same instance it wouldnt touch the network level.

Thanks

Marc

Answer 1

Bit more information on this.

sqlservr.exe is the process name which is sending data on the physical server name, msmdsrv.exe is the process name receiving data on the instance name.

Marc

Answer 2

If you open up SQL Server Management Studio and connect a SQL query window to your SQL instance, and run the following, what does it show? If SQL and Analysis Services are on the same machine, then I would hope it shows "Shared memory" in the net_transport column:

SELECT connection_id, connect_time, net_transport, net_packet_size, client_net_address
FROM sys.dm_exec_connections

I would refer to section 2.6 in the Operations Guide for more info on this topic and for other tricks on optimizing network IO in Analysis Services.
http://msdn.microsoft.com/en-us/library/hh226085.aspx

---

http://artisconsulting.com/Blogs/GregGalloway

Answer 3

Hi There,

Thank you for your response, i have ran the command (see below) it would appear it says TCP in the ent transport column, i assume this may be causing us a problem? how do i go about changing this?

Thanks

connection_id                                                                   connect_time                    net_transport   net_packet_size                client_net_address

A091D41A-92B1-406E-826B-BE6419912A34           2012-06-06 22:33:43.670                TCP        4096       172.16.65.1

A8904DD5-1BC8-4139-8DD4-81D41EE15CDC         2012-06-11 16:58:05.363                TCP        4096       172.16.65.1

02E89880-8CFE-462E-B065-000D7FC43872             2012-06-11 16:58:05.410                TCP        4096       172.16.65.1

0F199454-201D-4BD5-AF3D-1963E47321C9           2012-06-06 22:33:46.193                TCP        4096       172.16.65.1

1A762071-5722-4A6D-868C-0003CF8F284C            2012-06-06 22:33:51.213                TCP        4096       172.16.65.1

EF3358FE-E843-4DF8-B400-47873FB50012             2012-06-06 22:33:50.153                TCP        4096       172.16.65.1

B6AEE3BD-1D14-48F2-9E75-E9AC04FD47D2          2012-06-06 22:33:50.153                TCP        4096       172.16.65.1

EFB19FED-38B3-4601-98EC-879D31A536C6           2012-06-06 22:33:51.213                TCP        4096       172.16.65.1

52A57A8C-0108-47DD-95D3-F13A814DB8AA        2012-06-11 16:57:11.820                TCP        8000       172.16.65.1

F8F0B8FF-5D8B-4F95-9999-ECADBEA4CE9E          2012-06-06 22:45:00.300                TCP        4096       172.16.65.1

A9BD0F17-8690-4DAC-A597-3A98BD430D98        2012-06-06 22:33:53.007                TCP        4096       172.16.155.22

D089E6F0-98BE-40FB-B286-A6C0AC85340D          2012-06-06 22:34:10.423                TCP        4096       172.16.65.1

54AAB81F-0122-408E-B087-4887ED09C98F            2012-06-11 16:30:01.200                TCP        4096       172.16.65.1

1341B1BA-3E65-4854-B152-1D9E03F5DFAC           2012-06-11 16:30:01.153                TCP        4096       172.16.65.1

F51C3563-CE6F-4EF1-B3EE-B1D571B9CE32            2012-06-11 16:20:51.177                TCP        4096       172.16.155.131

0759EA8F-497A-4DAB-B2CB-43A434FCC8EB         2012-06-11 16:55:03.850                TCP        4112       172.16.155.25

022B8B11-B99F-4B63-9545-4A3A171DD310           2012-06-11 16:55:03.863                Session 4112       172.16.155.25

528B6C82-33D8-41A2-80AF-8F483D619796           2012-06-11 16:30:01.200                TCP        4096       172.16.65.1

F2B66255-E4C6-4467-8F98-1B9A1A90EC97            2012-06-11 16:30:01.823                TCP        4096       172.16.65.1

230518BF-7963-468E-BFD3-8579AF811A0C            2012-06-11 16:30:01.277                TCP        4096       172.16.65.1

0567CAFD-861A-41FF-A5F8-809B2DCF2F0C          2012-06-11 15:42:11.503                TCP        4096       172.16.155.131

817610AA-5DFB-4BC6-98F5-F077178C244A           2012-06-11 16:30:01.340                TCP        4096       172.16.65.1

7A7D95A5-B4DA-4D14-B09A-0EF29D558F04         2012-06-11 16:45:08.180                TCP        4096       172.16.155.22

782F6245-7A2C-411F-A2E0-AD34356E3F5C           2012-06-11 16:58:51.527                TCP        4096       172.16.155.131

D64C82C8-08E9-41D3-A455-907619ACCA17          2012-06-11 16:58:51.573                TCP        4096       172.16.155.131

45021161-985D-420F-A840-F7276B1488C2             2012-06-11 16:58:51.590                TCP        4096       172.16.155.131

Answer 4

Section 2.6 of the Operations Guide (mentioned above) has instructions on how to ensure it's using Shared Memory. Try out those suggestions and report back.

---

http://artisconsulting.com/Blogs/GregGalloway

Answer 5

Hi,

We implemented as recomended in section 2.6 of the Operations guide, however when processing the cube it errors - it suggests that using shared memory in a cluster isnt supported:

OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Shared Memory Provider: Shared Memory is not supported for clustered server connectivity [50]. ; 08001.

Answer 6

Hi,

Any ideas?

Answer 7

Anyone???

Answer 8

Sorry for the delay. I had some questions out to the smartest folks I know and did finally get back some suggestions.

First, if you are sure the cluster is setup so that SQL and SSAS are in the same resource group so they always will be running on the same server, then try configuring SSAS to use (local) (or localhost or just .) as the servername. See if that avoids the problem you're experiencing. Not elegant, but it may work.

The other thing is that it might be a network routing issue... shouldn't it just be leveraging the loopback adapter instead of actually registering box to box traffic?

This is a bit out of my realm of expertise, but you might try those things and report back.

Incidentally, you're using the default Packet Size... you might raise that per the recommendations in the Operations Guide as that will usually improve throughput on connection to SQL server.

Hope that helps. Keep us posted.

---

http://artisconsulting.com/Blogs/GregGalloway