Microsoft KB Archive/159234
Article ID: 159234
Article Last Modified on 10/16/2003
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q159234
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.
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: kbhowto kbusage KB159234