Some hidden things about ASE
Different States of SLEEP
When all processes are shown as SLEEPING by sp_who except the one, which issued the sp_who command, how can a user tell the difference between hangings versus running processes?
Definitions In pre-4.9.2 SQL Servers, the output of sp_who could be difficult to interpret. Processes
showed only one type of SLEEP status, "sleeping". In System 10, and 4.9.2 Rollup 2115 and above, sp_who shows four types of sleep along with the other possible statuses:
Value Meaning
Infected The server erred with a stack trace, and the process got an error that would normally kill it. The process is infected instead of killed.
Background This process is in the background
recv sleep The process is waiting for input from the client
Send sleep The process is waiting for a write to the client to complete
alarm sleep The process is waiting for an alarm (usually means the process is waiting for a wait for command to complete).
Lock sleep The process is waiting for a lock to be granted
Sleeping The process is waiting for something not listed above. This is "normal" sleep.
Runnable The process is not waiting for anything, and is ready to run, but is not the currently running process
Running The process is currently running (in a multiprocessing system, there can be more than one such process).
Stopped The process is stopped. In ancient history (before version 4.0.1), all processes stopped during a checkpoint. Now the only time a process is in the stopped state is when someone is using the kill command on it.
Bad status There is a bad value for the status of this process.
In uniprocessor hardware there can be only one process RUNNING and all other processes are either SLEEPING or RUNNABLE. The next RUNNABLE process gets scheduled to run after sp_who finishes. Processes sleep for certain events like disk I/O, network I/O, alarm, etc. If all the threads are shown as SLEEPING, at least one of them will become RUNNABLE after an event on which the thread is waiting.
On a multi-processor machine, if more than one SQL Server engine is started, you can see more than one thread in the RUNNING state. The number of processes running cannot exceed the number of SQL engines running.
It is not possible to find out from sp_who output which client process is hung waiting for Server response. But it is possible to find out if any process (i.e. thread) is blocked by another, by looking at the "blk" field of sp_who.
Zombie Process
On Unix operating systems, a zombie process or defunct process is a process that has completed execution but still has an entry in the process table, allowing the process that started it to read its exit status. In the term's colorful metaphor, the child process has died but has not yet been reaped.
When a process ends, all of the memory and resources associated with it are deallocated so they can be used by other processes. However, the process's entry in the process table remains. The parent is sent a SIGCHLD signal indicating that a child has died; the handler for this signal will typically execute the wait system call, which reads the exit status and removes the zombie. The zombie's process ID and entry in the process table can then be reused. However, if a parent ignores the SIGCHLD, the zombie will be left in the process table. In some situations this may be desirable, for example if the parent creates another child process it ensures that it will not be allocated the same process ID.
Pre-System 10 SQL Servers can end up with "zombie" (unkillable hanging) processes if the event on which a thread is sleeping never happens. In this case, the thread does not run and cannot be killed. This anomaly existed right from the first release of 4.0 SQL Server until a recent Rollup of 4.9.2 (2115 and above).
The problem is that the SQL Server scheduler is non-preemptive. This means that tasks cannot be put to sleep or woken up arbitrarily by the SQL Server scheduler; all task context switching is done voluntarily by running tasks.
Pre-System 10 SQL Servers handle attention through a signal handler set up to catch OUT-OF-BAND data which sets a status bit in the PSS (Process Status Structure). This is an asynchronous event. For example: a task is about to go to sleep waiting for input, but the client cancels the query with dbcancel(). If the signal handler sets the bit between the time the task is going to sleep and the time it is actually put to sleep, then the server task sleeps forever waiting for the client to send some data, and the client sleeps waiting for the server to acknowledge the cancel request. This is the well-known "dbcancel problem."
Another source of trouble can be a DBMS task in the Server which is sleeping on a network I/O from a client that just isn't there any more (maybe because somebody rebooted the PC on which the front end was running).
This kind of task cannot be killed because:
The task must be in RUNNABLE state so that the scheduler can kill the task the next time it
runs.
The task cannot be preempted because its state is unknown.
To complicate the above scenario, if the eternally-sleeping task started a transaction, it may potentially hold locks on different pages. The only solution for older versions is to reboot the SQL Server.
Killing Zombie Processes (New Feature)
As of the 10.0 SQL Server, and 4.9.2 SQL Server Rollup 2115 and above, zombie processes can now be killed. The new kill command not only sets the bit in the PSS as it used to, but also wakes up the task if it determines that the task is sleeping in one of four states: waiting to receive something from the client, a common state
(RECV SLEEP)
waiting for a send to be completed by the network service task
(SEND SLEEP)
waiting on an alarm because user did a waitfor delay command
(ALARM SLEEP)
waiting on a lock (resource, logical, semaphore, etc.) (LOCK SLEEP)
This means that any task can be cleaned up properly as if an exception has occurred while the task was running, provided the task is in one of the RECV, SEND, LOCK and ALARM sleep states. The new kill command can kill infected processes as well, also a new feature.
The kill command can almost instantaneously kill a task that is sleeping on any one of the events except the fifth state: normal sleep (where the task is waiting for a resource to post network or disk I/O). This was true for older versions of SQL Server, and is still true. The reason for this is that all sleeps except "normal sleep" have well-known and well-understood backout paths; however, tasks sleeping on resources have a variety of different backout paths.
The new kill command will:
• set the "kill yourself" bit on the task
• wake up the task normally
• put the task into the runnable queue
When the scheduler is ready to run the task it finds the "kill yourself" bit and aborts the task. For tasks that are in normal sleep or for running tasks, the new kill command acts exactly as the old kill command: it sets the kill bit in the PSS and it is up to the task to wake up and test the bit and decide to kill itself. Note that this means that the new kill command may not have an effect on all tasks.
NOTE! If a killed task is in the midst of a transaction, the entire transaction will abort. All resource cleanup will occur in the task backout path so that no inconsistent resources are left hanging around that might cause the SQL Server to hang in a hibernating state and eventually have to be rebooted.
The most practical solution for a Zombie process is to boot the server.
"Too Many Open Files" on Solaris
If you have a Sun_SVR4 (Solaris) SQL Server that reports the error "Too many open files" from a kernel subsystem, you will need to change the maximum number of file descriptors per process (NOFILES kernel parameter on most systems). There are two ways to reset this value:
1. Modify your RUNSERVER script as follows, depending on your shell:
sh or ksh RUNSERVER script: ulimit -n ## csh RUNSERVER script: limit descriptors ##
where ## = the new value for file descriptors
2. Run a program which calls setrlimit() to increase the maximum number of file descriptors and then invoke a process which requires a large number of fd's (file descriptors).
Here are a sample program and makefile called set_nofiles.c to show you how to do this. Build the executable by typing the command (assuming you named the makefule set_nofiles.mk):
make -f set_nofiles.mk
Run the executable by giving it the name of any program to run along with its command line options, for example: set_nofiles foobar x. You can have it run startserver -fRUNSERVER or the dataserver program.
You must run the ulimit/limit or makefule commands as root in order to set the maximum number of file descriptors > 1024.
Backup Server: Load from a Single Device of a Striped Dump
Backup Server customers may wonder if it is possible to do a striped dump but then load from a single device. The answer is "yes", but only when the dump is to tape or floppy devices and those devices are local.
Specific steps to do this are:
• Install the first tape to load and execute:
load database from `/dev/nrst0'
• Follow the prompts when Backup Server asks for subsequent mounts, issuing sp_volchanged as appropriate.
• sp_volchanged must be issued from a separate server login than the one doing the dump or
• load. SQL Server is not set up to pause in the middle of command execution, so the sp_volchanged command (which is a separate command) must be issued by a separate server session.
• Stripes may be loaded in any order, but if any stripe spans volumes, the volumes of that stripe must be loaded in sequential order.
Hiding the isql sa Password from a ps Command
How do you run isql scripts without having to specify the -Ppasswd option on the command line? Because the command line is visible from the ps command, running isql -Ppasswd is a security breach. In newer versions of SQL Server, we delete the password from the command line by copying it into an internal buffer and setting the password in argv to all blanks. Unfortunately, this technique does not work for all versions of UNIX; some versions of UNIX give the program a copy of argv, but display the original version in the ps output. Even on Sun, our method of blanking out the password on the command line does not provide security. If one uses the -c option with ps, it will print the original command line, not the copy that was given to the program. ps -e will show the password if it has been saved as an environment variable.
There are two possible solutions to this problem, one for batched and another for non-batched sessions, both requiring that the isql password be saved in an external file.
Method 1: Non-batched Sessions:
Save the password in a file that is readable only by the owner, as follows (you need type this one time only, from the command line):
cat > scratchfile mypassword ^D chmod og-rwx,u+rw scratchfile
Run the session from the command line, or as a shell script:
isql -Uusername <
.go EOSQL
isql will prompt for a password. The "<
Method 2: Batched Sessions:
Create the scratch file as described above, and set the protections. Then run all scripts as follows:
cat scratchfile | isql -Uuser -i script-file-name
These methods should be secure enough for the majority of users, as they have the following properties:
The password never appears on the command line, which always seems to be open to security breach.
The location of the password will be known to anyone using ps, but they cannot see it because the file permissions have been set against them.
Users can run as many scripts as they like without typing their password for each script.
All they must do is to follow the second method for all script executions.
The password is kept in a scratch file, privately controlled by the programmer using that method.
The programmer can change the password easily because it is changed in one file only.
The password is not hard-coded into script files which are shared by an entire organization.
Different States of SLEEP
When all processes are shown as SLEEPING by sp_who except the one, which issued the sp_who command, how can a user tell the difference between hangings versus running processes?
Definitions In pre-4.9.2 SQL Servers, the output of sp_who could be difficult to interpret. Processes
showed only one type of SLEEP status, "sleeping". In System 10, and 4.9.2 Rollup 2115 and above, sp_who shows four types of sleep along with the other possible statuses:
Value Meaning
Infected The server erred with a stack trace, and the process got an error that would normally kill it. The process is infected instead of killed.
Background This process is in the background
recv sleep The process is waiting for input from the client
Send sleep The process is waiting for a write to the client to complete
alarm sleep The process is waiting for an alarm (usually means the process is waiting for a wait for command to complete).
Lock sleep The process is waiting for a lock to be granted
Sleeping The process is waiting for something not listed above. This is "normal" sleep.
Runnable The process is not waiting for anything, and is ready to run, but is not the currently running process
Running The process is currently running (in a multiprocessing system, there can be more than one such process).
Stopped The process is stopped. In ancient history (before version 4.0.1), all processes stopped during a checkpoint. Now the only time a process is in the stopped state is when someone is using the kill command on it.
Bad status There is a bad value for the status of this process.
In uniprocessor hardware there can be only one process RUNNING and all other processes are either SLEEPING or RUNNABLE. The next RUNNABLE process gets scheduled to run after sp_who finishes. Processes sleep for certain events like disk I/O, network I/O, alarm, etc. If all the threads are shown as SLEEPING, at least one of them will become RUNNABLE after an event on which the thread is waiting.
On a multi-processor machine, if more than one SQL Server engine is started, you can see more than one thread in the RUNNING state. The number of processes running cannot exceed the number of SQL engines running.
It is not possible to find out from sp_who output which client process is hung waiting for Server response. But it is possible to find out if any process (i.e. thread) is blocked by another, by looking at the "blk" field of sp_who.
Zombie Process
On Unix operating systems, a zombie process or defunct process is a process that has completed execution but still has an entry in the process table, allowing the process that started it to read its exit status. In the term's colorful metaphor, the child process has died but has not yet been reaped.
When a process ends, all of the memory and resources associated with it are deallocated so they can be used by other processes. However, the process's entry in the process table remains. The parent is sent a SIGCHLD signal indicating that a child has died; the handler for this signal will typically execute the wait system call, which reads the exit status and removes the zombie. The zombie's process ID and entry in the process table can then be reused. However, if a parent ignores the SIGCHLD, the zombie will be left in the process table. In some situations this may be desirable, for example if the parent creates another child process it ensures that it will not be allocated the same process ID.
Pre-System 10 SQL Servers can end up with "zombie" (unkillable hanging) processes if the event on which a thread is sleeping never happens. In this case, the thread does not run and cannot be killed. This anomaly existed right from the first release of 4.0 SQL Server until a recent Rollup of 4.9.2 (2115 and above).
The problem is that the SQL Server scheduler is non-preemptive. This means that tasks cannot be put to sleep or woken up arbitrarily by the SQL Server scheduler; all task context switching is done voluntarily by running tasks.
Pre-System 10 SQL Servers handle attention through a signal handler set up to catch OUT-OF-BAND data which sets a status bit in the PSS (Process Status Structure). This is an asynchronous event. For example: a task is about to go to sleep waiting for input, but the client cancels the query with dbcancel(). If the signal handler sets the bit between the time the task is going to sleep and the time it is actually put to sleep, then the server task sleeps forever waiting for the client to send some data, and the client sleeps waiting for the server to acknowledge the cancel request. This is the well-known "dbcancel problem."
Another source of trouble can be a DBMS task in the Server which is sleeping on a network I/O from a client that just isn't there any more (maybe because somebody rebooted the PC on which the front end was running).
This kind of task cannot be killed because:
The task must be in RUNNABLE state so that the scheduler can kill the task the next time it
runs.
The task cannot be preempted because its state is unknown.
To complicate the above scenario, if the eternally-sleeping task started a transaction, it may potentially hold locks on different pages. The only solution for older versions is to reboot the SQL Server.
Killing Zombie Processes (New Feature)
As of the 10.0 SQL Server, and 4.9.2 SQL Server Rollup 2115 and above, zombie processes can now be killed. The new kill command not only sets the bit in the PSS as it used to, but also wakes up the task if it determines that the task is sleeping in one of four states: waiting to receive something from the client, a common state
(RECV SLEEP)
waiting for a send to be completed by the network service task
(SEND SLEEP)
waiting on an alarm because user did a waitfor delay command
(ALARM SLEEP)
waiting on a lock (resource, logical, semaphore, etc.) (LOCK SLEEP)
This means that any task can be cleaned up properly as if an exception has occurred while the task was running, provided the task is in one of the RECV, SEND, LOCK and ALARM sleep states. The new kill command can kill infected processes as well, also a new feature.
The kill command can almost instantaneously kill a task that is sleeping on any one of the events except the fifth state: normal sleep (where the task is waiting for a resource to post network or disk I/O). This was true for older versions of SQL Server, and is still true. The reason for this is that all sleeps except "normal sleep" have well-known and well-understood backout paths; however, tasks sleeping on resources have a variety of different backout paths.
The new kill command will:
• set the "kill yourself" bit on the task
• wake up the task normally
• put the task into the runnable queue
When the scheduler is ready to run the task it finds the "kill yourself" bit and aborts the task. For tasks that are in normal sleep or for running tasks, the new kill command acts exactly as the old kill command: it sets the kill bit in the PSS and it is up to the task to wake up and test the bit and decide to kill itself. Note that this means that the new kill command may not have an effect on all tasks.
NOTE! If a killed task is in the midst of a transaction, the entire transaction will abort. All resource cleanup will occur in the task backout path so that no inconsistent resources are left hanging around that might cause the SQL Server to hang in a hibernating state and eventually have to be rebooted.
The most practical solution for a Zombie process is to boot the server.
"Too Many Open Files" on Solaris
If you have a Sun_SVR4 (Solaris) SQL Server that reports the error "Too many open files" from a kernel subsystem, you will need to change the maximum number of file descriptors per process (NOFILES kernel parameter on most systems). There are two ways to reset this value:
1. Modify your RUNSERVER script as follows, depending on your shell:
sh or ksh RUNSERVER script: ulimit -n ## csh RUNSERVER script: limit descriptors ##
where ## = the new value for file descriptors
2. Run a program which calls setrlimit() to increase the maximum number of file descriptors and then invoke a process which requires a large number of fd's (file descriptors).
Here are a sample program and makefile called set_nofiles.c to show you how to do this. Build the executable by typing the command (assuming you named the makefule set_nofiles.mk):
make -f set_nofiles.mk
Run the executable by giving it the name of any program to run along with its command line options, for example: set_nofiles foobar x. You can have it run startserver -fRUNSERVER or the dataserver program.
You must run the ulimit/limit or makefule commands as root in order to set the maximum number of file descriptors > 1024.
Backup Server: Load from a Single Device of a Striped Dump
Backup Server customers may wonder if it is possible to do a striped dump but then load from a single device. The answer is "yes", but only when the dump is to tape or floppy devices and those devices are local.
Specific steps to do this are:
• Install the first tape to load and execute:
load database
• Follow the prompts when Backup Server asks for subsequent mounts, issuing sp_volchanged as appropriate.
• sp_volchanged must be issued from a separate server login than the one doing the dump or
• load. SQL Server is not set up to pause in the middle of command execution, so the sp_volchanged command (which is a separate command) must be issued by a separate server session.
• Stripes may be loaded in any order, but if any stripe spans volumes, the volumes of that stripe must be loaded in sequential order.
Hiding the isql sa Password from a ps Command
How do you run isql scripts without having to specify the -Ppasswd option on the command line? Because the command line is visible from the ps command, running isql -Ppasswd is a security breach. In newer versions of SQL Server, we delete the password from the command line by copying it into an internal buffer and setting the password in argv to all blanks. Unfortunately, this technique does not work for all versions of UNIX; some versions of UNIX give the program a copy of argv, but display the original version in the ps output. Even on Sun, our method of blanking out the password on the command line does not provide security. If one uses the -c option with ps, it will print the original command line, not the copy that was given to the program. ps -e will show the password if it has been saved as an environment variable.
There are two possible solutions to this problem, one for batched and another for non-batched sessions, both requiring that the isql password be saved in an external file.
Method 1: Non-batched Sessions:
Save the password in a file that is readable only by the owner, as follows (you need type this one time only, from the command line):
cat > scratchfile mypassword ^D chmod og-rwx,u+rw scratchfile
Run the session from the command line, or as a shell script:
isql -Uusername <
.go EOSQL
isql will prompt for a password. The "<
Method 2: Batched Sessions:
Create the scratch file as described above, and set the protections. Then run all scripts as follows:
cat scratchfile | isql -Uuser -i script-file-name
These methods should be secure enough for the majority of users, as they have the following properties:
The password never appears on the command line, which always seems to be open to security breach.
The location of the password will be known to anyone using ps, but they cannot see it because the file permissions have been set against them.
Users can run as many scripts as they like without typing their password for each script.
All they must do is to follow the second method for all script executions.
The password is kept in a scratch file, privately controlled by the programmer using that method.
The programmer can change the password easily because it is changed in one file only.
The password is not hard-coded into script files which are shared by an entire organization.
Comments
Post a Comment