BookmarkSubscribeRSS Feed
jroe
Obsidian | Level 7

Hello, often when a team member needs to select a larger data set from MS SQL over to SAS I will see a wait type indicator in SQL "async_network_IO" which essentially is "Waiting for network". At this time other sql databases (non-sas) and overall performance will degrade on the server due to the large network overhead and data transfer.

 

example:

a. select * from mytable

b. select a,b, c, d, n(100+ columns) from my table (needed to recreate in SAS as a data set)

 

Data is moved from large tables (100-200gb in sql) over the network to SAS.

a. The end machines are not enabled for jumbo frames

b. SQL works in packets of 4092 bytes size, default non-jumbo fram back is 1492/1500 bytes. (4kb split to 1kb)

 

 

I have tried changing the packet size via libname statement which has no effect. Also tried newer SQL "natural" drivers to no avail. Any other ideas to reduce network io issues? My concern is SAS is not reading the data fast enough to keep up with the network and SQL. Any libname optimizations? 

 

 

 

LIBNAME myschema ODBC NOPROMPT =

"Driver=SQL Server Native Client 11.0;

Server=myserver;

Database=mydb;

dbsliceparm=all;

threads=yes CPUCOUNT=8;

READBUFF=131072;

DBOMMIT=32768;

INSERTBUFF=131072;

ROWSET_SIZE=32768;

Trusted_Connection=Yes;"

Schema=myschema;

4 REPLIES 4
Kurt_Bremser
Super User

Those 1492/1500 byte sizes point to some choke point (a hop over a WAN?) in your connection that limits packet sizes.

 

The most painless (for the database) way to export data to SAS is to unload to a text file on the database server, transfer that file with (S)FTP, and read it on SAS with a data step (or read it directly with filename FTP). FTP transfers are very economic in terms of CPU usage, we do our transfers to/from DB/2 this way for 20 years now.

jroe
Obsidian | Level 7

Thanks for the feedback.

a. 1492/1500 is the default packet size of a network card in the Windows environment without jumbo frames enabled on the NIC and your router/swich.

 

b. FTP is not practical for actuarial work (hundreds of tables, databases, employees etc) utilizing SQL as a data warehouse. 

 

c. SQL to SQL or non-SAS applications often do not have this issue. Any libname suggestions ? 

 

The error can occur from network performance but it also can occur due to SAS not being able to keep up with the number of rows being sent over. SAS by default wants to read < 1500 rows at a time which is not a modern setting. 

jroe
Obsidian | Level 7

Figured out two reasons:

a. proc sql: if you use compress in join/where statements, conversions in joins/where statements (substring etc), or use "calculated" valuable it will cause the app to go multithreaded to single threaded, often inducing the network ao errors.

 

b. data steps appear (non proc sql) to also be single threaded and prone to this issue when executed against at able in MS SQL. 

jroe
Obsidian | Level 7

another item that may be helping:

a. for the hosted server (SQL and if applicable a SAS server)... ideally your server between iscsi/raid/SAN should have JUMBO frames enabled. In most cases unless everything is hosted, jumbo frames may not be enabled on the endpoints (desktops requesting the data) and most likely shouldnt be.

 

b. however for the NIC(s) of the servers... adjust the read buffer and transmit buffer to the max. 

example for HP 10GB nic:

read buffer: set to 4096

transmit: 16384

 

(4mb and 16MB I believe)

 

Some nics only support 256 or 512 or 1024 maxes. Often READ is 1/4 or 1/2 of transmit. This helps reduce packet issues.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1593 views
  • 0 likes
  • 2 in conversation