09-10-2013 12:56 AM
What is needed is either a system option or a method to stop a running sas stored process after certain time, say-10 minutes. Now , different stored processes can have different time limits allowed for them to run. So , a system level option/method is preferrable. A server level option is last option.
09-10-2013 03:15 AM
Sumit, There are a lot of timers everywhere. Your stored process can run by a Web-based connection or by Eguide.
You have to more specific about your question and the problem you are trying to solve.
- Browser time-out. (Internet Explorer) but also Eguide has time-outs
- Web-server time outs
- Firewall/routers time-outs
- Session time-outs. For this see: SAS(R) 9.4 Stored Processes: Developer's Guide (STPSRVSET Function inhertited from SAS/intrnet)
If there is a load-balancing problem there are multiple processes for one Stored ProcessServer.
Interactive processes should respond quick enough. (less than a minute, more in the seconds)
Longer running processes should be scheduled. With scheduling mostly there is an expected end time with an options alert signalling when something is going not as should be.
Always the contradiction having it run for some additional time it would finish to the decision to stop the proces.
Stopping a proces could also possible having impact on the consistency of the data.
By the way Stored Processes are Service processes started by the first requestor and will be reused by possible many requestors afterwards.
When something wrong is done by one requestor it is possible to have this service process got blocked. The only solution is to kill a blocked service process on the OS-level. StoredProcesses should be well tested before giving into procution when having a good serviced operation.
Splitting multiple users groups to have their own appsrv context is another.
09-10-2013 03:26 AM
Thanks for the reply. Here is the complete problem.
I have made an web-based interface using sas stored process . Through this interface, you can query on your sas datasets stored on server. Now, i take inputs from the user and process the query in proc sql. Now , the users may fire big queries or queries which consume lots of server time/space. I dont want this to happen. I want user to specify the maximum query time for which the query will be run and when the max query time elapses, the running sql should stop and should not show any result.
Now the option/method should not be set at server level because that will apply the max query time to all the stored processes of that server.
I hope i make my point very clear. please let me know if you want more details.
09-10-2013 05:51 AM
Congratelations, You are beyond your comfortzone, having a big volume (big queries) variety. I a missing the velocity. That is "Big data" :smileygrin:
Just kidding on that. But it looks to be you have a performance&tuning problem. The best way would be to prevent it happen.
SAS(R) 9.4 SQL Procedure User's Guide ( Using PROC SQL Options to Create and Debug Queries)
Another is you did not mention whether a external DBMS is involved or not. Sometimes a classic OLTP RDBMS admin can limit the resources on that side of the query. In that case you have to dig into the implicit/explicit sql pass through.
When it are SAS-datasets also a lot is possible like definining indexes for often used quiries. Proc SQL is multithreading it could use more cpu-time than the wall clock time. With this I never have seen something at Proc SQL limits on resources.
You have a WEB-interface so you are in the situation to limit the code of the users not being able to do the bad things.
It is like preventing the SQL-injection abuse. If the users are needing free format code and free format queries you have an issue.
I remember once something having seen in SMC but I cannot figure it out anymore.
SAS(R) 9.2 Intelligence Platform: System Administration Guide, Second Edition (Welcome | Communities on SASUsing SAS Management Console to Monitor SAS Servers)
Monitoring performance tuning: http://support.sas.com/resources/papers/proceedings13/463-2013.pdf http://www2.sas.com/proceedings/forum2008/387-2008.pdfIt is the most disappreciated kind of work. You are bothering business users as the computer does not do what they want. You are bothering IT-staff as those beautifull machines are being stressed and destroyed by the business users. You are bothering managers as nobody is happy.
09-10-2013 06:14 AM
i appreciate your reply.. i dont actually have a performance and tuning problem. i am just restricting the end user to shoot queries which take large time to execute because of server size restrictions. the server size is small and the number of users is very high. if every body shoots big queries, it will cause the server to go for a toss. So, i want something to stop proc sql/sas-program mid-way of execution.
09-11-2013 03:10 AM
Sumit, you are have the classical performance&tuning problem no doubt about that.
You mentioned them your options (the classic ones) :
a/ buy better hardware (your hardware is limited)
b/ Let the queries better perform (optimize code)
c/ Spread of load, prioritze classes of them
This was common about 1980's with very expensive hardware (mainframes) to cheap labor human hours as I started as SystemsProgrammer. Workloadmanager (WLM) is the approach to tell online (interactive users) getting more priority than batch usage. Scheduling usage for planning and running as fast a possible the needed batch-jobs.
http://en.wikipedia.org/wiki/Thrashing_(computer_science) was the one to fear for limited resources on cpu/memory.
You are describing the same behavior (toss). Getting the machine overloaded it get ionto a process of not proceeding anything.
To cancel long running queries als the Ivory-tower IT-man will not help you. Load will go up as users are trying to put more into it (restarting).
An option for spread/balance load on machine is mentioned. make several classes. Fast queries (limited in usage) / heavy big queries. And limit the option on parallel / simultanous processing.
Allowing less (decrease) work to the machine will increase the performance and troughput.
09-11-2013 03:17 AM
lets get straight to this sir
there is no option of increasing server capacity or doing anything server side .
i just wanted to have some option in sas or some method in sas . There is no alternative. Please dont go to parallel / simultanous processing,increasing hardware,increasing server capacity kind of stuff.
09-11-2013 03:37 AM
Your are having a web-service with SAS with A StoredProcess Server?
That is having (seen many times 3) sub process as simulteanous processing-load balancing.
You have that, and it is SAS.
Your are using "PROC SQL" having multiple logical processors in the machine it will do multi-threading.
(Parallel processing) there options to switch it off on.
You have that and it is SAS.
So my prorosal is rather simpel.
- switch multithreading off
- define two Stored Process servers.
One with those 3 subworkers for light queries
One with one worker for the heavy load queries
- Look for the OS options to prioritize at the OS level.
- try to offload heavy queries into batch (sas batch-jobs) processing and let then run planned/scheduled.
By that light query users being serviced getting the best possible. All other workoad being spread.
Using Windows Unix (Linux) or Mianframe as server running SAS?
They are all Multi-users Multi-process/tasking operating systems. Trying to operate those is handeling the logic simulteanously/parallel processing.
The babbage machine is history.