INF: How to Change the Sleep Value Used by Dbdataready

ID: Q159234

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5

SUMMARY

Normal DB-Library asynchronous query processing is handled by calling first dbsqlsend and then dbdataready in a loop until dbdataready returns TRUE. By default, the dbdataready function will sleep for 250 ms.

In situations where you have written asynchronous query processing you may not want the dbdataready call to sleep for 250 ms. For example, if you have several queries that generally run in 50 ms, you could theoretically run five of the same queries in the 250 ms time frame. There is a registry setting to control the sleep duration for the client computer.

MORE INFORMATION

WARNING: Using the Registry Editor incorrectly can cause serious, system wide problems that may require you to reinstall Windows NT to correct them. Microsoft cannot guarantee that any problems resulting from the use of the Registry Editor can be solved. Use this tool at your own risk.


   Registry branch: HKEY_LOCAL_MACHINE on LOCAL_MACHINE
   Key path:        Software\Microsoft\MSSQLServer\Client\DB-Lib
   Key value:       DataReadySleep
                    REG_DWORD

The default value is 250 ms; the range of allowed values is 0 ms to 1,000 ms. To ignore the sleep operation, set the value to 0xFFFFFFFF.

Be careful when modifying this setting, because setting the value to 0 is not the same as 0xFFFFFFFF. The 0xFFFFFFFF value is checked in the dbdataready function, and the sleep function is not called, whereas 0 is passed to the Win32 sleep function. The following is an excerpt from the Win32 documentation on the sleep call:


   A value of zero causes the thread to relinquish the remainder of its
   time slice to any other thread of equal priority that is ready to run.

You must take into account that NOT sleeping may actually flood the network layers with calls to check the status of the connection. The best setting is generally two to four microseconds longer than the actual runtime of the query. Because you cannot control outside factors (such as the network load or server load) on any given day, it may be advantageous to set the value to 0xFFFFFFFF for those applications for which performance is critical, and implement your own sleep strategy. Some applications are designed to allow this parameter to be configurable, while others use an elaborate scheme to dynamically adjust the wait factor, based on the query or a prior history. In either case, you are introducing an arbitrary delay that can effect performance. Finally, in certain cases it is acceptable to thread the query out and run it with dbsqlexec.

A simple workaround other than (or in conjunction with) setting the DataReadySleep is to switch to a network library such as TCP/IP. TCP/IP does not use the same mechanism to determine when data has been returned from the server, and the reduced overhead may improve performance.

Keywords : kbusage SSrbDB_Lib
Version : 6.0 6.5
Platform : WINDOWS
Issue type : kbhowto