SAS query taking lot of time to run

[Analysts12]

Hi ,

There are various users who have complained to us regarding performance issue on SAS servers .
We checked cpu utilisation at server level and few user ids showed cpu consumption above threshold .

What can we do to control this ?
Also is there a way we can find why processes are taking more time to run . Like a simple query takes hours .

[Reeza]

Are they pulling from columns via index or do you need to add indexes to your data set? Are you limits appropriate for all users? In my experience you'll always have a small subset of power users so you need to give them more resources typically.
Has your data grown over time and are you quota's accounting for that?

What is slow? Where in the queries are the issues? It could be programming choices, network speed, SAS server quotes/limitations. You need to determine the source of the issue first.

Assuming you've already worked through most of these: https://support.sas.com/rnd/scalability/papers/solve_perf.pdf

[SASKiwi]

A good starting point would be to post the complete SAS logs of of several queries that run slow. Comparing run times with CPU usage will help. Also what is the performance difference at peak times versus low usage times? If queries run in a reasonable time during periods of low usage it is safe to assume they are running reasonably efficiently and don't necessarily need tuning. If they are still slow then they either need tuning or there are some serious bottlenecks in your SAS infrastructure that need fixing.

You should be monitoring the SAS Environment Manager dashboards as these will help isolate where your SAS performance problems are. 

[Analysts12]

Yes, SAS environment manager access is something which are discussing for our project .

I need one more suggestion, what all can be good business reason to get environment manager access .
Because we cannot monitor peak time via server , like monitoring becomes tough

[SASKiwi]

In my view SAS Environment Manager is an essential tool for monitoring SAS 9.x server health and performance. The fact that you can see all important statistics in a few dashboards is a no brainer to me.  

[Kurt_Bremser]

Maxim 2: Read the Log.

Start with inspecting the log(s) and identify the long-running steps. Then see if other techniques to achieve the same result are more efficient.

If no logs are available, have the users send you the code so you can test it yourself.

If code proves to be efficient and no further significant optimization can be done, move the server load to off-business hours by creating batch jobs and running them from the scheduler. Power users who run large scale projects and employ efficient coding techniques may need additional resources (larger MEMSIZE, additional high-speed - SSD! - temporary disk space)

If remote DBMS systems are involved, look to reduce the amount of data transported over the network.

Once you have identified problematic steps and are not sure what to do, run them with FULLSTIMER and show them here.

[MargaretC]

To further what KurtBremer was saying, see if you can add these statements to the SAS code:

OPTIONS FULLSTIMER SOURCE SOURCE2 MSGLEVEL=I MPRINT NOTES;
PROC OPTIONS GROUP=MEMORY;
PROC OPTIONS GROUP=PERFORMANCE;
RUN;
LIBNAME _ALL_ LIST;
PROC OPTIONS OPTION=WORK;
PROC OPTIONS OPTION=UTILLOC;
RUN;
DATA _NULL_;
%PUT This job started on &sysdate at &systime;
RUN;
OPTIONS SASTRACE=”,,,s” SASTRACELOC=saslog NOSTSUFFIX;

The last statement will help with information if the query is going to an external database.  It tells you the amount of time spent in the database and the amount of time spent transferring data back to SAS.