INF: How to Change the Sleep Value Used by Dbdataready
This article was previously published under Q159234
This article has been archived. It is offered "as is" and will no longer be updated.
Normal DB-Library asynchronous query processing is handled by calling firstdbsqlsend and then dbdataready in a loop until dbdataready returns TRUE. Bydefault, the dbdataready function will sleep for 250 ms.
In situations where you have written asynchronous query processing you maynot want the dbdataready call to sleep for 250 ms. For example, if you haveseveral queries that generally run in 50 ms, you could theoretically runfive of the same queries in the 250 ms time frame. There is a registrysetting to control the sleep duration for the client computer.
WARNING: Using the Registry Editor incorrectly can cause serious, systemwide problems that may require you to reinstall Windows NT to correct them.Microsoft cannot guarantee that any problems resulting from the use of theRegistry 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 to1,000 ms. To ignore the sleep operation, set the value to 0xFFFFFFFF.
Be careful when modifying this setting, because setting the value to 0 isnot the same as 0xFFFFFFFF. The 0xFFFFFFFF value is checked in thedbdataready function, and the sleep function is not called, whereas 0 ispassed to the Win32 sleep function. The following is an excerpt from theWin32 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 networklayers with calls to check the status of the connection. The best settingis generally two to four microseconds longer than the actual runtime of thequery. Because you cannot control outside factors (such as the network loador server load) on any given day, it may be advantageous to set the valueto 0xFFFFFFFF for those applications for which performance is critical, andimplement your own sleep strategy. Some applications are designed to allowthis parameter to be configurable, while others use an elaborate scheme todynamically adjust the wait factor, based on the query or a prior history.In either case, you are introducing an arbitrary delay that can effectperformance. Finally, in certain cases it is acceptable to thread the queryout and run it with dbsqlexec.
A simple workaround other than (or in conjunction with) setting theDataReadySleep is to switch to a network library such as TCP/IP. TCP/IPdoes not use the same mechanism to determine when data has been returnedfrom the server, and the reduced overhead may improve performance.
Article ID: 159234 - Last Review: 11/01/2013 21:38:00 - Revision: 4.0
Microsoft SQL Server 6.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition
- kbnosurvey kbarchive kbhowto kbusage KB159234