S::STARTUP is probably a legacy throwback to pre-A2k. Although I still use it for loading some applications at startup and setting variables required for our working environment, there really isn't much else it is needed for. Of course the opinions will vary widely.
Lets just think about it for a minute ... S::STARTUP is executed whenever the first drawing is opened, unless you load it in each drawing. It then executes whatever functions you have programmed it to do.
So what might you want to do? Well, you might want to set things like limits, create layers, load linetypes, set variables etc ...
Ok, cool ... except most of that can be done within your template ...
Well, there is the possibility of having it autoload other lisp, vba and arx files ... except that can be done very easily in the startup suite (except, don't forget the startup suite is different for each profile)
Ok, maybe you want to autoexecute some of your lisps at startup ... ok, but that can be done within the lisp file itself ... i.e. (C:MYLISP)
Well .. then really there isn't anything that S::STARTUP can do for you that you cannot do in other ways ... however, I find it is still much simpler to utilize, especially considering the workspaces, profiles, templates etc. that can be changed and used by the person behind the keyboard. By using S::STARTUP you will be reasonably sure that no matter what profile is being used, no matter what menu, workspace or template is loaded, this function will be executed at least the first time a drawing is opened.
At one of my previous jobs, we used it exclusively to track drawing usage utilizing acadlspasdoc and acad.lsp to load it into each drawing.
So the "proper" use could be debated and you will likely find many people who use it as who don't ... kinda like in vba
sub startup()
'do stuff
end sub
it runs when loaded, but many people don't use it.