Rather than setting by session I would like to configure the DB as read
uncommitted.

Thanx Advance.

Robert (robert.j.sipe (AT) boeing (DOT) com) writes:
You can set the database read-only to eliminate locking entirely.

But else you can't do it, and that is probably a good thing. Dirty reads
is nothing you should engage in as a matter of routine. Maybe you should
review indexing in your database instead.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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.


"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Robert (robert.j.sipe (AT) boeing (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

SQL 2005 - Row level versioning works very nice for this purpose.

pb648174 (google (AT) webpaul (DOT) net) writes:
Yes, snapshot isolation can be a very good way to resolve locking issues.
And note that there is a database switch: ALTER DATABASE db SET
READ_COMMITTED_SNAPSHOT ON. This changes the default isolation level of
READ COMMITTED to use the snapshot instead.

However, I got the feeling that Robert's problem rather was long-running
queries, and not blocking.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx