Send doneinproc tokens
With ASE 12.5.1 a few new configuration parameters are introduced. "send doneinproc tokens" is one of them. Although this is a new configuration parameter, the information related to it is also relevant for older versions of Sybase ASE. With a different command (dbcc tune) the same level of server tuning can be accomplished.
Contents
What is a doneinproc token?
A doneinproc token is a small packet of information, returned through the network back to the client (e.g. isql) when a statement inside a stored procedure or trigger has been executed. When this statement is a 'select', the doneinproc token contains the number of rows returned (rows affected). The client will show this rowcount unless "set nocount on" has been specified.
Each token is put into a separate network packet. Although the token itself is just a few bytes in size, the network packet will be a multiple of 512 bytes, depending on the connection parameters specified when a client connects to ASE. Doneinproc tokens are send for select statements and for some (not all) other statements. With the configuration option "send doneinproc tokens" the transmission of tokens send for statements other than "select" can be suppressed.
It's a myth that when you specify "set nocount on" the doneinproc token will not be sent.
How to tune doneinproc
sp_configure "send doneinproc tokens",0 /* Do not send doneinproc tokens for non-select statements */
The possible values for this configuration option are 0 (send doneinproc tokens only for SELECT statements) and 1 (always send doneinproc tokens). It's a dynamic configuration option so no reboot of ASE is needed.
When should you use this configuration parameter?
One of the most obvious reasons to use this parameter is when you have an overloaded network. Since each doneinproc token is put into a separate network packet a lot of really unneeded packets are sent. Depending on your situation, like the use of stored procedures or triggers and the number of statements within those procedures, you might see a better throughput. Another good reason to use it, is when you use a lot of cursors within your code. Each fetch of a row generates a doneinproc token. When you suppress these, you will also see a noticeable drop in used CPU time.
Warning: before you use this parameter you should carefully test the usability within your environment. Some client software depends on the doneinproc tokens. As far as I know, JDBC software (Sybase jConnect) will work correctly without the doneinproc tokens. I have done some testing with jConnect and it seems to work without any problem.
As with all configuration parameters, this setting affects the server as a whole, so all connections to ASE will see the same effect.
Traceflag 292
As said, with the configuration parameter "send doneinproc tokens" the tokens can be suppressed for non-select statements. A 'select' statement still causes a doneinproc token to be sent. With the use of traceflag 292 these tokens can also be suppressed. You can set this option with dbcc traceon(292) or in the runserver file with the -T flag. The traceflag will only have an effect when you have turned the "send doneinproc tokens" off.
My advice is to do not use this traceflag unless you have tested and confirmed that your applications still work properly. The software that interfaces with ASE is mostly build on top of CT-Library and CT-Library depends on these tokens. As soon as you set the traceflag you can expect to see errors.
| Doneinproc setting | Traceflag 292 | Doneinproc tokens send for |
|---|---|---|
| 1 | Off | All statements (Default ASE 12.5.1 behaviour) |
| 0 | Off | Only for select |
| 1 | On | All statements |
| 0 | On | Never send |
How much will you gain?
Quite a number of network packets are generated just by these doneinproc tokens. A sample execution of sp_helpdb will generate 44 packets, depending on the number of databases on the server. As soon as you turn off the doneinproc tokens this drops to 19. When traceflag 292 has been set the number drops to just 3.
A sample procedure that uses a cursor generated 572 packets that could be brought down to 195 by tuning doneinproc without setting the traceflag.
Tuning with sp_sysmon
The effect of not sending the doneinproc tokens can also be observed with the sp_sysmon stored procedure. The Network I/O Management section will show a drop in the number of TDS Packets Sent. With a fairly simple experiment (5000 times execution of sp_helpdb) a decrease in sent packets with 58 % could be observed. An extra side effect is a drop in used CPU cycles.
How to tune doneinproc for pre 12.5.1 releases
For pre-12.5.1 releases of ASE the configuration option has not been implemented. But with the dbcc tune statement you can accomplish the same behaviour.
dbcc tune(doneinproc,0) switches the doneinproc tokens off for all statements, except for selects. With dbcc tune(doneinproc,1), the default behaviour, you can reset this switch. However, when you reboot Sybase ASE the setting will be reset to 1. This means that when you start ASE a small script should re-execute the dbcc tune statement.
