how can i tune an sql-server2000 running on w2k (2 gb ram)?
Tuning can involve many different things. [This is probably not the answer you were looking for.] However, "tuning" suggests prioritized needs and applying finite (Sql Server) resources appropriately. That much said, tuning generally involves the following:
I Assuming your system was originally appropriately sized, the first things to do involve establishing / verifying what total system resources are available (RAM, CPUs, Array Controller Stats, etc.); and generating baseline production activity profiles to understand current utilization patterns and existing residual (resource) capacities.
II The next logical step is to ascertain patterns, changes, and trends in resource usage e.g.(how much disk space is consumed per week, how many more connections are made each week relative to previous weeks, how are times for key application queries changing, how is network bandwidth availability changing, etc.). This process involves taking the same measures at regular intervals so that growth rates may be determined.
III Once a sound understanding of available resources, usage trends (and any existing resource bottlenecks or over utilization problems) has been attained; that will facilitate the identification of reasonable immediate tuning objectives (and predictive forecasting of future issues). The actual tuning strategies selected for implementation should come directly from your tuning objectives (which are ultimately dictated by business needs).
IV Good predictive forecasts lead to better long range tuning goals and allows for such things as proactive resource procurement efforts (to keep production efficient, and minimize the risk of resource related system failures). Here again, the actual long term tuning strategies implemented will come from appropriately prioritized business goals set for the system.