More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
What is the best value for cschedspins?
It is crucial to understand that cschedspins is a tunable parameter (recommended values being between 1-2000) and the optimum value is completely dependent on the customer's environment. cschedspins is used by the scheduler only when it finds that there are no runnable tasks. If there are no runnable tasks, the scheduler has two options:
1. Let the engine go to sleep (which is done by an OS call) for a specified interval or until an event happens. This option assumes that tasks won't become runnable because of tasks executing on other engines. This would happen when the tasks are waiting for I/O more than any other resource such as locks. Which means that we could free up the CPU resource (by going to sleep) and let the system use it to expedite completion of system tasks including I/O.
2. Go and look for a ready task again. This option assumes that a task would become runnable in the near term and so incurring the extra cost of an OS context switch through the OS sleep/wakeup mechanism is unacceptable. This scenario assumes that tasks are waiting on resources such as locks, which could free up because of tasks executing on other engines, more than they wait for I/O.
cschedspins controls how many times we would choose option 2 before choosing option 1. Setting cschedspins low favours option 1 and setting it high favours option 2. Since an I/O intensive task mix fits in with option 1, setting cschedspins low may be more beneficial. Similarly since a CPU intensive job mix favours option 2, setting cschedspins high may be beneficial.
The consensus is that a single CPU server should have cschedspins set to 1. However, I strongly recommend that users carefully test values for cschedspins and monitor the results closely. I have seen more than one site that has shot themselves in the foot so to speak due to changing this parameter in production without a good understanding of their environment.
(Continued on next question...)
