Hi All,

Both my Saleslogix Server and SQL Server are on the same box, so presumably when I set up a connection I should be able to use the "Shared Memory" protocal for speed? So, I set the "Network Library" value in the connection manager to "dbmslpcn", and a clicking the test button in the connection manager works a treat. 

However, from the client end, it will not connect. I get a "Cannot initialise data dictionary" error when I test the connection at the client end. If I ignore the error and attempt to login using the connection anyway, I get the error "Cannot open database 'Saleslogix' requested by the login. Login failed."

Has anyone else successfully connected using the SQL Server "Shared Memory" protocol? 

I'm using SLX 7.22.1871, on SQL Server 2005 (latest SP), and I've ensure the protocal is enabled in the SQL configuration.

Please Help? 

Guy

You won't be able to do this - as SLX Clients need to connect via whatever is defined in ConnMgr. They pickup their setup from the server. For same reason you can't use (Local) in the server name - as the network client would try to go to (Local) and wouldn't find it. You should TCP/IP protocols throughout. Shared memory, of course, would only work if the app only ran on the server.

I've got the connection working, so I'm going to call your bluff on this one and restart the server and see what happens. I was (almost) certain that the clients connect through the oledb provider on the server, and as such all queries were essentially local tot he server with the slx service being the intermidiary which dishes all the data around. 

I expect you're right though. I'll update you at 6pm when I restart, I expect I'll be crying.

Quote: Originally posted by Guy Barrett I was (almost) certain that the clients connect through the oledb provider on the server, and as such all queries were essentially local tot he server with the slx service being the intermidiary which dishes all the data around.

This is not how it works.

The OLEDB Provider on each machine has the code to Parse the SQL Statement and apply security. It only connects to the SLX Service for Login and Licensing Information.

From there on, it connects directly to SQL Server (this is why you have to have the correct SQL Client installed on each machine running SalesLogix).

Ok then, get this. It worked! 

Now I'm totally confused. There is a noticeable increase in speed at the LAN client, having set dbmslpcn as the Network Library in the connection settings at the server end. Especially logging in and loading all the groups and menus etc...

I'm now thinking that pehaps you guys were right, apart from perhaps at the client end it ignores the Network Library setting and simply uses TCP/IP when connecting directly, but at the server end where something goes through the provider, it uses Shared Memory. That's total guess-work though and I won't really know anything until I've left a trace running for a while. 

I'll leave it running like this for a couple of days and keep a close eye on it. It's a production server (yeah! life on the edge!) so I need to be able to wonk it straight back quickly should it cause problems. If it were to try to use shared memory from the client end, it'll simply not work / crash out so I'm not worried about potential for damage, as there is none. 

If you have a DEV or Test SLX database on 7.2.2, if you get the chance give it a go yourself and let me know what happens? 

I suspect - if you look in Activity Monitor for SQL Server - you will find that the host/client is logging in as SharedMem but all the clients are not - take a look in Activity Mon and, in the meantime, I'll try here and let you know.

Hi Guy

Sorry, it didn't work. Check here for protocols. http://technet.microsoft.com/en-us/library/ms187892(v=sql.105).aspx

Also, when I tested (using dbmslpcn in NetLib) I found it made no difference at all (connections were still LPC, which is shared mem - which it will always do on a local machine - so there is no need to set this setting in ConnMgr). However, all clients connecting in from afar used TCP/IP. Which is expected. I think all you saw was a server not rebooted from other changes, missing Native Client or some other config issue.

See this image for the final proof! http://www.empath-e.com/external/dbnetlib.png

But, whilst we are still here - have you modified your Packet Size from the default - as that *does* have an impact !

Regards
Mike 

Hi,

You're absolutely right. Whilst it's not stopped anything from working, it's not changed anything either. I think it totally ignores the Network Library setting. I have seen no increase in the number of Shared Memory connections. It's odd that I have seen an increase in performance when logging in though, so I'm going to leave the setting there even if it's just a placebo for me

I raised default packet size to 8192 a little while back in both the connection manager, and at the SQL Server settings. This did increase performance, but not as much as I'd hoped. Is 8192 optimal? What is a little odd is that most connections coming in over TCP/IP only seem to connect at 8000 (see below), but I'm not complaining. I'm going to find out where the rogue 4096's are coming from as well. 

SELECT 
local_net_address, 
client_net_address, 
net_packet_size, 
net_transport, 
count(session_id) as connection_count
FROM 
sys.dm_exec_connections
group by 
local_net_address, 
client_net_address, 
net_packet_size, 
net_transport
order by 
count(session_id) desc

local_net_address client_net_address net_packet_size net_transport connection_count

172.16.3.254 172.16.3.244 8000 TCP 22

172.16.3.254 172.16.3.160 8000 TCP 16

172.16.3.254 172.16.2.238 8000 TCP 9

NULL <local machine> 4096 Shared memory 7

NULL <local machine> 8000 Shared memory 7

NULL <local machine> 8192 Shared memory 5

172.16.3.254 172.16.3.252 8000 TCP 5

172.16.3.254 172.16.2.121 8000 TCP 4

172.16.3.254 172.16.2.92 8000 TCP 4

172.16.3.254 172.16.2.246 8000 TCP 3

172.16.3.254 172.16.2.66 8000 TCP 3

172.16.3.254 172.16.2.72 8000 TCP 3

172.16.3.254 172.16.2.78 8000 TCP 3

172.16.3.254 172.16.2.222 8000 TCP 3

172.16.3.254 172.16.2.230 8000 TCP 3

172.16.3.254 172.16.2.233 8000 TCP 3

172.16.3.254 172.16.2.235 8000 TCP 3

172.16.3.254 172.16.3.252 8208 Session 2

172.16.3.254 172.16.3.254 4096 TCP 2

172.16.3.254 172.16.2.235 4096 TCP 2

172.16.3.254 172.16.2.102 8000 TCP 2

172.16.3.254 172.16.2.93 8000 TCP 2

172.16.3.254 172.16.3.155 8208 Session 1

172.16.3.254 172.16.3.155 8208 TCP 1

172.16.3.254 172.16.3.252 8208 TCP 1

172.16.3.254 172.16.3.252 4096 TCP 1

8192 is the size, but there will be overhead within that (not just data).

Whether this is optimal is dependent largely on your infrastructure. For example, the default ethernet packet size is 1512 bytes. If you have the hardware (NIC's, Switches etc) then you should definitely do a few things - (a) Investigate 802.3ad - make sure your NIC's and Switches can support that protocol and bind several NIC's together as one (b) Enable Jumbo frames - but, again, your hardware must support this *prior* to making the change (or, make sure you're at the console, not RDP, as you *will* lose connectivity!

By adjusting Jumbo frames to >8000 and increasing packet size in SQL from 4096 to 32768 you can get amazing improvements. Also, as this will now go out over 2 or more NIC's through duplexing it will get out onto the wire much faster in larger packets but with a smaller number of packets, decreasing collisions etc.

But, there's a limit. Unless something is really wrong - you will end up with a similar issue with the disk sub-system instead (SQL Server can supply to wire quickly - but can't get it off disk quick enough - or queries are performing poorly etc). So, you should then start to look at optimising the disk subsystem (SSD, RAID Levels, Spin Speed, Cache etc). Number of CPUs for SQL Server (parallel queries etc) and, finally, memory of SQL Server (how much, how much is cached, is it over committed -v- O/S?)

Once you start to optimise - it's difficult to know when to stop! (I assume you've already added the v754 indexes and disposed of older indexes, analysed cluster indexes etc?).

But, in order, Disk//CPU, Memory, Network is where you should start to look. If you need any assistance, you know where we are!

Show off 

The database is optimised to the nines as far as indexing, and disks, and partitioning goes, it's one of the few things that I do actually know a decent amount about.

As for the rest of what you mention above regarding network infrastructure, I'll get cracking. Thanks very much for the advice! 

Great stuff to share with us Mike, Thanks!!! Amazing how the fastest salelogix systems can phail when you use a fragmented Raid 5 drive subsystem....

No probs - and, yes, agreed. I'm a convert to SSD ! Just wish I could find a customer who can afford the IOFusion platform