Architecting, installing and maintaining your SAS environment

Poor SAS ETL performance - Request for assistance reading perf mon reports

Reply
Occasional Contributor
Posts: 8

Poor SAS ETL performance - Request for assistance reading perf mon reports

Good day,

 

Let me preface by saying that I know that this might not be the best forum for this question but I will bravely ask it anyway and ask for your forgiveness.

 

Scenario:

My company has a SAS ETL process that runs nightly which suddenly started underperforming in the worst way.  The code responsible for the ETL hasnt changed and neither myself nor my team can account for any server side changes that may be the cause of the issue.  In an attempt at finding the source of the issue I decided to do some performance monitoring while the process ran running to get some insight into what might be happening. 

 

The problem:

the perfmon reports are not making any sense to me, I would be grateful for some assistance in reading the output to determine if there is in fact an issue and how to go about addressing it.  The attachment has the output.

 

Thanks much!

 

Regular Contributor
Posts: 205

Re: Poor SAS ETL performance - Request for assistance reading perf mon reports

Posted in reply to genelle_lake
We're here to help...no forgiveness required Smiley Happy
Firstly, can you ensure the -fullstimer is enable for your system. Then, post the overall job statistics for the job i.e. The last step in the job. Then also 
proc options group=memoryrun;
Then, some info about your system. OS, hardware, etc. 


Trusted Advisor
Posts: 1,326

Re: Poor SAS ETL performance - Request for assistance reading perf mon reports

[ Edited ]
Posted in reply to genelle_lake

Hello @genelle_lake,

 

no problem asking those questions here. And I think it might be a perfectly right forum to ask this your question. We deal with those every day, hunting ghosts.

 

By my experience, there might be a couple of things to check:

- Did code did not change at all? For this question, you can check the last modification of your .sas files and any execultable related, such as bat files.

 

- Secondly, if code did not change, actually, my next focus point would be, either data (data grows and vary along time, which might create differences on running times) and external processes (such as backups, antivirus and such). Any of those factors can give nasty surprises in case they are not in control.

 

- Of course, there is a third possible focus point, but they are not the most common (or they should not be): hardware degradation. Disk headers broken, faulty bits on the disk or memory, fragmentation, and other factors as such, can cause variations on performance, and sometimes even lose of data.

 

Looking at your performance report, a couple of questions come to my mind: I see high queues on the disk, and I see high usage of the cache.

- The second one is quite clear to me that it means that your SAS processes are caching or paginating data (virtual memory) way too much. For this one, probably what you would need is to assign additional memory to your SAS processes. MEMSIZE, SORTSIZE and such would help a lot.

 

- The first one is not that much clear to me. It might mean that the processes are waiting a lot to be processesed due to other processes (see the logic?), or just that your processes take very short time and the disk is naturally waiting. The first case is a huge issue, where you might need to check external processes that make other processes to iddle, and the secon one is just OK to happen.

 

All of the above is based on your data, but it is not really enough to bring conclussions to the table. I would advise you to get additional information.

 

One way, is to get the information from SAS itself. Information as asked by @nhvdwalt would help: enable FULLSTIMER, proc options group="memory, performance";run;

 

SAS Environment Manager, if you have it, it would help as well.

 

One of the best tools that is helping me with SAS ETLs (and interactive sessions) during the last period, is Enterprise Session Monitor (ESM) from Boemska. Very recommended, created by @boemskats.

 

Last but not the least, if you find yourself expending too much time or your feel yourself on a blocked road, you can always contact SAS Consulting or a trusted Partner, and I am sure that one day or a few days from them would help you a lot.

Hope it helps.

 

Post edit: Just remembered a paper that explains this really well: 
Troubleshoot Your Performance Issues: SAS® Technical Support Shows You How
http://support.sas.com/resources/papers/proceedings09/333-2009.pdf

Please let us know how it goes, and all the additional information you get, would help us as well to get additional ideas or conclussions.

 

Kind regards,

Juan

Ask a Question
Discussion stats
  • 2 replies
  • 152 views
  • 2 likes
  • 3 in conversation