BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » BI Admin, General

SAS Code: Simple Macro to Benchmark Data Performance

Submitted by on 2012-08-08 – 6:11 AM 20 Comments

Courtesy of MS Office

When troubleshooting data performance problems, IO problems, or simple library connectivity, I use a simple snippet of code to push the data storage to the limits.  It is useful because it stresses the data storage component, whether it be a SAS dataset on local file storage or an external database like Oracle, and it provides useful performance metrics.  To make it even better, since this is a SAS Macro it can easily be called multiple times for different libraries by setting the 2 parameters in the %BENCHMARK() macro.

 

Data Benchmark Macro Code

The following code will take the CLASS dataset from the SASHELP library and continually append a specified number of iterations.  Be sure to include the OPTIONS statement to get the vital statistics in the log.

 

Output

This is the output produced when the FULLSTIMER option is set.  These statistics were produced on my laptop with a solid state drive.

NOTE: DATA statement used (Total process time):
    real time 15.08 seconds
    user cpu time 13.55 seconds
    system cpu time 1.45 seconds
    memory 414561.92k
    OS Memory 439844.00k
    Timestamp 08/01/2012 11:00:11 PM

How fast can you run this code?  Remember to set the macro to loop 10,000 times.

Gather Statistics Over Time with %LOGPARSE

Once a simple (or complex) benchmark task (or even multiple tasks 😀 ) has been created like the one above, you can use the log parse macro to capture and store the statistics in your own dataset over time.  SAS support provides excellent documentation on obtaining and using the %LOGPARSE macro.  That way if a pesky end user constantly complains of poor performance you can provide the analytics to either back their claim or possibly rule out the data storage.

What code snippets do you have to test performance??

 

Image: Courtesy of Microsoft Office Clipart Collection

Learn all about SAS BI

You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.

The following two tabs change content below.

Tags: , ,

20 Comments »

  • Yes! Thank you!

    I also just replaced the code with artwork.

  • Hello says:

    options fullstimer threads=yes;
    %macro benchmark(lib,num);
    data &lib..test;
    set
    %do i=1 %to #
    sashelp.class
    %end;
    ;
    run;
    %mend benchmark;

    %benchmark(work,10000);

  • Sorry – WordPress is printing the actual HTML code for the ampersand symbol, which is &
    So when I try to type an ampersand it displays the HTML code.

    You just need an ampersand is what I am trying to say.

    Ignore the HTML CODE ( & A M P ; );

  • Hello says:

    Makes no sense can you elobrate a bit more ;

    “& should be &”

    issue is what is the value of the macro varible &AMP as it is not resolved and requires a value I believe?

  • Oops .. looks like WordPress was helping. & should be &

  • Hello says:

    What is the value of the macro value &amp?

    1 data &lib..test; set
    – —-
    22 180
    200
    WARNING: Apparent symbolic reference AMP not resolved.

  • Wow … I agree we need to get your machine all tuned up. I’ll put some extra horse-power in the mail today! 🙂

  • ovaere says:

    It took 20 seconds on the server. 32G ram and 12 cores…

  • Very curious I agree Mark. Thanks for sharing.
    Here’s my theory:

    SAS EG may have an advantage over the BATCH because it’s already loaded into memory and ready to go. Batch actually has to start a SAS session to run the code – which can account for the extra time.

    What do you think? Maybe it has to do with the way Batch uses the “resources” compared to SAS EG.

  • Mark Miller says:

    Are we really certain what this tests?
    I have results from two distinctly different systems
    where the results would appear to be reversed from
    what I would expect;

    DESKTOP
    Processor: Intel Core2 Q9650 Quad CPU @3.00 GHz
    RAM: 8 GB
    Type: Windows 7 64-bit
    SAS 9.2 (ts2m3)

    This is a loaded system with 163 processes running.
    Run from inside SAS EG (program window)

    NOTE: DATA statement used (Total process time):
    real time 9.02 seconds
    user cpu time 7.53 seconds
    system cpu time 1.31 seconds
    Memory 368535k
    OS Memory 433188k
    Timestamp 2012-09-14 2:37:02 PM

    BIG SERVER
    Processor: Intel Xeon X7560 @2.27GHz 9650 8-core CPU @3.00 GHz (4 processors i.e. 32 core)
    RAM: 256 GB
    Type: Windows Server Enterprise 64-bit
    SAS 9.2 (ts2m3)

    WORK is on Adaptive Disk ( mixed SSD & spindles)

    This is a multiuser loaded system with 11 processes (of mine) running.
    Run SAS batch

    NOTE: DATA statement used (Total process time):
    real time 12.12 seconds
    user cpu time 11.18 seconds
    system cpu time 0.92 seconds
    Memory 368535k
    OS Memory 433188k
    Timestamp 2012-09-14 2:00:47 PM

    … Mark Miller

  • Uh-oh! How bad was it?

    My work machine took almost 32 mins to run the query. Eeeek.

  • ovaere says:

    Damn my machine is really slow, need to finetune this… :-s

  • Steve Overton says:

    Very nice information LeRoy, thanks!

  • Great papers – thanks for contributing LeRoy!

  • LeRoy Bessler says:

    Let me mention that another way to gather statistics, rather than from SAS logs, is from Windows itself and/or from its Windows Management Instrumentation with tools optionally configured to track any SAS processes on the platform (i.e., they could be used to monitor other processes). In this connnection see:
    http://support.sas.com/resources/papers/proceedings10/279-2010.pdf
    and
    http://support.sas.com/resources/papers/proceedings12/356-2012.pdf
    The first of these papers looks only at CPU time, but the second paper gets into more resources. These tools are for monitoring EVERY SAS process in the server’s or workstation’s total workload. When you collect the big picture, you can determine which are the heavy resource consumers easily from the outside, rather than waiting for complaints from the application user or the programmer. You can even augment passive monitoring, data collection, and after-the-fact analysis & reporting with real-time alerts based on thresholds. SAS email can send the alerts to processing-owning user, to the SAS admin, to the server-owning manager, or whomever.

  • Dave Horne says:

    The option can be set in Tools > Options > Preferences > Advanced (or via the command line).
    When running SAS interactively (in display manager), by default the log window will scroll as steps are completed which adds a bit of time to the processing. By setting the autoscroll to 0, the log does not get updated until all the submitted code completes.

    More details: http://support.sas.com/documentation/cdl//en/hostwin/63047/HTML/default/viewer.htm#p1xc5easr53lwjn1anu6l9mvstsh.htm

  • What does AutoScroll 0 mean? Or how do you set it?

  • Dave Horne says:

    Steve, just found your blog.. I’m always intrigued by benchmarks. Here are the results from my desktop:

    Dell OptiPlex 980
    Processor: Intel Core i7-870 @2.93 GHz (quad core with hyper threading)
    RAM: 16 GB
    Type: Windows 7 64-bit
    SAS 9.3 M1 (32-bit)

    NOTE: DATA statement used (Total process time):
    real time 8.92 seconds
    user cpu time 8.23 seconds
    system cpu time 0.73 seconds
    memory 155548.94k
    OS Memory 177012.00k
    Timestamp 08/08/2012 11:08:26 AM

    I made sure that “autoscroll 0” was set in the log window. Also, since I have plenty of RAM, I specify the MEMLIB option in my config so that saswork is written in memory. This seems to be a bit faster than a SSD so far in my testing. These are the memory related options I’m using at the moment:

    -MEMLIB
    -MEMCACHE 4
    -MEMMAXSZ 12G
    -MEMSIZE MAX

  • Steve Overton says:

    Nice!

  • I’m using a virtual machine with this config:

    Processor: Intel Core i7-3930K CPU @3.20 GHz (2 processors)
    RAM: 16 GB
    Type: Windows 7 64-bit
    BI: SAS BI 9.3

    This is a new demo system that was just setup with no one else on the system.

    NOTE: DATA statement used (Total process time):
    real time 9.88 seconds
    user cpu time 9.31 seconds
    system cpu time 0.51 seconds
    memory 417147.67k
    OS Memory 442660.00k
    Timestamp 08/08/2012 06:10:01 AM