Monday 17 May 2010

NOTE: Stored Process Server vs Workspace Server - What's the difference?

A favourite interview question of mine is: Compare and contrast SAS 9's stored process server and workspace server. This question is very good at revealing whether candidates actually understand some of what's going on behind the scenes of SAS 9.

I ask this question of developers as well as administrators and architects. Whilst some developers don't need to know this behind-the-scenes information, the fact that a developer has an inquiring mind and has made the effort to find the information tells me something positive about that person. And, I'm not certain that developers don't need to know how their code is executed...

There's a (limited) analogy that can be drawn with driving a car. If the car has a manual gearbox then the driver knows how to use the clutch to provide drive when required. But if the driver doesn't have a superficial awareness of some of the inner workings of the clutch, i.e. how two abrasive plates stick fast to each other when the clutch is engaged, and how they rub against each other when the clutch is partly engaged, then the driver is likely to "ride the clutch" at T-junctions on hills, i.e. keeping the car still by partially engaging the clutch rather than using the brakes. If I haven't stretched the analogy too far then I think you can see that the inner functioning of SAS 9 (or the clutch) can be used in a manner that "goes against the grain" and will, at some point, cause reliability problems.

So, for those of you with an inquiring mind(!), here's a superficial comparison and contrastment(?) of the two aforementioned servers: stored process and workspace.

When a user launches Enterprise Guide and connects to a SAS server, a new SAS session is created on the server. The session is a unique process that is not shared with any other user, and it has the security properties of the Enterprise Guide user. Clearly there's a small delay whilst this brand new session is created, but once that overhead is overcome, the user has sole use of the session.

When a user causes a stored process to be executed (whether from Enterprise Guide or from the Information Delivery Portal or by some other means) an existing stored process session will be assigned from the pool of existing sessions and the stored process code will execute within that session. If the same user subsequently causes the same stored process to be executed (either through the same route, or by a different route) an existing stored process session will be assigned from the pool, but it may not be the same session and even if it's the same session then some other user's different stored process may have been executed in the session between times. 

As you can guess, these stored process sessions are running all of the time, waiting for some work to do. They do not inherit the security credentials of the individual users who request execution of a stored process; all stored process sessions typically run under the security credentials of a SAS system user.

You can already see how the workspace is suited to the needs of an EG user, and how the stored process server suits quick-running, transactional processes. If a stored process takes too long to run then it will hog one of the sessions that are intended to be shared by all users. If too many of these long-running processes clog-up too many of the sessions then it'll have a detrimental effect on the overall performance of the stored process service (the slipping clutch!).

If you need to provide long-running activities as stored process speak to your SAS administrator about establishing a stored process service that uses workspace sessions rather than stored process sessions - SAS provides this as an option. The SAS platform administration guides at provide plenty of guidance on setting-up these servers and how you can use workload balancing techniques to ensure a smooth workflow. And you'll find that Rossland and Richardson's SUGI 30 paper provides a good introduction to stored processes in general.

So, now you know the distinction between workspace servers and stored process servers. Good luck with any interviews you may attend!