Adlib home page > Support > Frequently Asked Questions

What should I do with SQL query time out errors?

When you perform a search which is likely to generate a large search result, like searching a field on an empty or small value in a very large Adlib SQL database, this may sometimes cause the query to time out. An error message will display, stating an error in reading the file (error 189), followed by the SQL query and the text "Query timeout expired". This can only happen in Adlib applications running on an Adlib SQL database, when the SQL Server has been installed on a server with multiple physical processors.
The cause of this is, in many cases, the fact that SQL Server handles these queries in a parallel manner. This means that parts of the SQL query underlying your search, as executed by the SQL Server, are handled by separate processing threads running on multiple processors. By default, the SQL Server is configured to use all processors available. However, due to inefficient planning of these separately running threads by SQL Server, the outcome might sometimes be that the results of these threads do not finish at the same time and that threads come to wait on others that take a long time to finish. On a server under heavy load, this may then cause processes to freeze after which a time out will occur.
Because this issue is caused by SQL Server, AIS cannot provide a final solution to this problem. A work-around can be provided though, by configuring the server to use all processor threads but one. You can do this in SQL Server Management Studio or from the SQL prompt. In SQL Server Management Studio, select the SQL Server itself* (not a single database underneath it), click the New query button, type a script similar to the following** and subsequently click the Execute button:

sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE WITH OVERRIDE; 
GO 
sp_configure 'max degree of parallelism', 8; 
GO 
RECONFIGURE WITH OVERRIDE;
GO

* Be aware that the max degree of parallelism option is an option to be set on the server level. It cannot be set on the database level. This could mean that the work-around presented here is not applicable to your particular environment.

** At AIS we have tested this in our own system environment and we've discovered that setting the number of processor threads in this option to maximum threads - 1 is usually enough to solve the time out errors. In this example, 8 is the number of processor threads to be used, yet if your server has 16 processor threads available, then the max degree of parallelism option should be set to 15. In the Windows Task Manager for the relevant server you can easily see how many processor threads are available. The image below, for example, shows 16 processor threads.
With the above script the new configuration is applied to the server without having to stop (or restart) the SQL service.

More information on this SQL Server configuration can be found at: http://msdn.microsoft.com/en-us/library/ms181007.aspx