Posted by Erland Sommarskog on 03/02/06 22:47

Robert (robert.j.sipe@boeing.com) writes:
> Thanx Erland, unfortunately read-only is not a viable option. We have
> be reviewing the database and are putting in indexes and hints on the
> queries to determine if the locking will become less of a problem. The
> users are engaged in some practices that have been going on for awhile
> and there application keeps timing out when a lock is placed on a table
> for more that 30 seconds. The biggest problem we have seen are the
> locks created by the ad hoc queries from Access and Excel. Until we can
> convince the dbo to setup an olap database or stop using Access I think
> the problems will continue.

Judging from your description, I don't think having a universal dirty read
would address your problem, even if it existed. The default timeout in
many client API (which is a really stupid idea, if you ask me) is not
related to locking, but the client API getting bored if does not see a
result set within 30 seconds. If this due to a complex query plan with,
NOLOCK is not going to help you. (But it may of course prevent writers
from being blocked.)

All client APIs permit you to set the timeout, but there may be a
problem if the queries are submitted without any real programming
code. You cannot set the timeout on the connect string. In ADO,
which is what you use from Access and Excel I guess, you set the
command timeout on the Connection or Command objects.

Note that query timeout is unrelated to SQL Server. All SQL Server sees
is a request to cancel the query batch.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]