topic Re: Simple SAS queries continue to run single threaded in MS SQL with Async_Network_IO wait types in New SAS User

Simple SAS queries continue to run single threaded in MS SQL with Async_Network_IO wait types

JroeJroe — Fri, 27 Sep 2019 15:02:06 GMT

Concern 1: how to reduce async_network_io (allow sas to read rows quicker so it can keep up with sql)

Concern 2: how to have SAS's code sent to the MS SQL server utilize parallel processing (instead of 1 core)

I have utilized various options and hints to encourage SAS to utilize parallel processing, however the code continues to utilize 1 cpu core (SQL is set to MAXDOP = 😎 and single threaded. The same queries ran in MS SQL directly run in 10% of the time than SAS.

Is there a hint or driver that would resolve this issue? I have observed the same issue with the "SQL driver", "Natural SQL Driver 11.0 and 10.0" and the latest "ODBC Driver 17 for SQL Server".

The select/create table statement should be pulling the data down from sql multi/parallel threaded, any assistance? Thanks a ton!

/* tried with and without option flags */
OPTIONS BUFNO=1000;
OPTIONS VBUFSIZE=1024K;
OPTIONS UBUFNO=20;
OPTIONS UBUFSIZE=1024K;
OPTIONS IBUFSIZE=32767;
OPTIONS IBUFNO=100;
OPTIONS BUFSIZE=64k;
OPTIONS THREADS CPUCOUNT=6; 
OPTIONS THREADS=YES;

/* tried with and without libname options */

LIBNAME MYSCHEMA ODBC NOPROMPT = 
"Driver=ODBC Driver 17 for SQL Server;
Server=MYSERVER; 
BULKLOAD=YES;
READBUFF=32767;
DBOMMIT=32767;
INSERTBUFF=32767;
ROWSET_SIZE=32767;
Database=MYDATABASE;
DBSLICEPARM=all;
THREADS=yes;
CPUCOUNT=8;
Trusted_Connection=Yes;
" 
Schema = MYSCHMEA;

/* example - however I am selecting about 30-50 columns
also tried proc sql THREADS; without noticing a change.
 */

proc sql;
Create table simple_Test as SELECT
a
,b
,c
,d
,e
,f
,g
FROM MySCHEMA.MYTABLE
; quit;

Installed sas plugins:

For Base SAS Software ...
Custom version information: 9.4_M6
Image version information: 9.04.01M6P110718
For SAS/STAT ...
Custom version information: 15.1
For SAS/GRAPH ...
Custom version information: 9.4_M6
For SAS/CONNECT ...
Custom version information: 9.4_M6
For High Performance Suite ...
Custom version information: 2.2_M7
For SAS/ACCESS Interface to PC Files ...
Custom version information: 9.4_M6
For SAS/ACCESS Interface to ODBC ...
Custom version information: 9.4_M6

Re: Simple SAS queries continue to run single threaded in MS SQL with Async_Network_IO wait types

JosvanderVelden — Fri, 27 Sep 2019 17:22:35 GMT

Have you tried using fedsql?
https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=proc&docsetTarget=n18135hihi4fi0n1ra7ge29nh3hw.htm&locale=en
and https://support.sas.com/resources/papers/proceedings15/3390-2015.pdf

Re: Simple SAS queries continue to run single threaded in MS SQL with Async_Network_IO wait types

JroeJroe — Fri, 27 Sep 2019 17:47:53 GMT

Thanks for the response. I primarily utilize SSMS directly as a DBA. However assisting SAS users with their code (who would not convert to federated sql) , I would like the queries to consistently utilize parallel processing (on the sql server) when they use sas data steps or proc sql.

I believe it may be single threaded as a "select" statement without computations occurring on the server (Group by/sum etc) . A large bottleneck is the async_network_io wait type that is observed in sql. This is due to sql processing data faster than SAS will retrieve it and I am not sure how to alleviate that pressure.

Re: Simple SAS queries continue to run single threaded in MS SQL with Async_Network_IO wait types

JroeJroe — Thu, 03 Oct 2019 14:28:47 GMT

Anyone else resolve the Async_Network_IO wait types?

https://www.sqlshack.com/reducing-sql-server-async_network_io-wait-type/

I observe parallel processing working and no wait types when an aggregate summary is utilized or more complex where statements (data is computed on SQL server then brought back to SAS).

For simple requests such as "select a-z from SQL and create a new table in SAS" appear to be "single threaded/core" on SQL and generate the async_network_IO wait which reduces performance. Primarily this occurs when the application (SAS) cannot keep up with the data being sent from SQL .

I feel like I have exhausted all libname and proc sql hints to alleviate this bottleneck.