November 21, 2007 at 12:54 pm
Is there a way to set (default) isolation level by application, login or user?
On startup, the analysis package we use is sending costly queries to gather the range of values for its dimensions. Makes sense, but it is doing it without setting a low isolation level (ideally read uncommitted) which would seem to be smart for this kind of query.
It's distinct from other connections by applicationname and login (thus user). So a way to set by any of these would be usable.
Other ways of attacking the problem:
Change the application: We're looking into it, but progress is slow.
Sprinkling the views it uses with (nolock) hints. That takes time and limits the usage of the view (but we're doing it anyway).
Many of the SET values are configurable in sp_configure's "user options", but I dont' see this one.
Oh, the ideal solution would apply to SQL 2000 also 🙂
Cheers,
November 21, 2007 at 1:39 pm
SQL Server 2005 offers new optimistic isolation levels that may help you out here but it would not be portable to your SQL Server 2000 box and it must be set at the database level. Look up Snapshot isolation in BOL for more info.
November 21, 2007 at 5:15 pm
ALTER DATABASE [your_db] SET ALLOW_SNAPSHOT_ISOLATION ON
GO
The db should have no user connected when enabling or disabling row versioning.
Test it first as this is at the db level; watch tempdb usage - it is heavily used.
Once it's turned on for the db, you can enable row versoning at the transaction level using
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
....
As with the other snapshot isolation level, which is READ COMMITED SNAPSHOT, any single read query will behave like a snapshot read but only for the duration of the query. So repeatable reads do not occur, but consistency is guaranteed.
Good luck
November 26, 2007 at 9:45 am
Thanks for the replies and suggestions, Michael and John. Gives us something to look into. Cheers.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply