Thursday, May 31, 2012 12:33 PM
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.
Thursday, May 31, 2012 12:48 PM
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.
Thursday, May 31, 2012 6:43 PMModerator
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
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.
- Proposed As Answer by Elvis LongMicrosoft Contingent Staff, Moderator Thursday, June 07, 2012 11:34 AM
Monday, June 11, 2012 5:59 PM
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?
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
Monday, June 11, 2012 7:42 PMModerator
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.
Tuesday, June 26, 2012 2:25 PM
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 . ; 08001.
Tuesday, July 03, 2012 12:33 PM
Thursday, July 19, 2012 12:12 PMAnyone???
Friday, July 20, 2012 5:49 PMModerator
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.