Microsoft SQL Server forum - How does one set read-uncommitted on the entire DB?

Author

How does one set read-uncommitted on the entire DB?

Robert

2006-03-05, 8:33 pm

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

Thanx Advance.

Erland Sommarskog

2006-03-05, 8:33 pm

Robert (robert.j.sipe@boeing.com) writes:
> Rather than setting by session I would like to configure the DB as read
> uncommitted.

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Robert

2006-03-05, 8:33 pm

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@sommarskog.se> wrote in message
news:Xns9779F2892305
2Yazorman@127.0.0.1...
> Robert (robert.j.sipe@boeing.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@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Erland Sommarskog

2006-03-05, 8:33 pm

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

pb648174

2006-03-06, 7:17 pm

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

Erland Sommarskog

2006-03-07, 8:24 pm

pb648174 (google@webpaul.net) writes:
> SQL 2005 - Row level versioning works very nice for this purpose.

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_SNAPS
HOT 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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sponsored Links