This may already be happening automatically, but what I am trying to understand is whether BO DI 11.5.1.5 can be configured (or is set by default) to use “No Lock” when it is reading from a datastore.
I am working with MS SQL Server 2000.
Can anyone enlighten me on this? Thanks!
icytrue (BOB member since 2006-04-10)
No, DI does not issue any hints, be them locking hints or index hints.
The only way to do this, if a WITH (NOLOCK) is mandatory in your environment, is to have your dataflows’ sources be a SQL transform instead of a source table. Then you can hand-code your SELECT query from your source table and include the NOLOCK.
(I’d suggest you enter an enhancement with customer support for this. If you do, let us know the ER # and I’d be happy to add my vote to it too.)
dnewton 
(BOB member since 2004-01-30)
Thanks for that. I’ve done a bit more reading…
Firstly, do you know if using the SQL Transform limit in any way what DI can do in terms of its optimization of the SQL it sends to the database?
Secondly, are you aware of any difference in the behaviour at the database between
and
?
If there is no difference (as the MS SQL 2000 literature suggests) then the isolation level method would be easier to introduce to an existing DI job as it is required once per dataflow, rather than once per datasource.
All comments welcome!
icytrue (BOB member since 2006-04-10)
The pre-SQL command applies to the target table, not the source table. (I made the assumption that you’re more worried about locking/blocking on your source tables vs. your target tables. Perhaps that’s not the issue.)
So in DI terms, these aren’t equivalent. #1 relates to the target table. #2 relates to the source table.
dnewton (BOB member since 2004-01-30)
SQL transform prevents DI from doing ANY optimization. It will only send exactly what you type into the box. Period. Not only that, but it cannot push down anything that you join to it, will not generate metadata for it, etc. Generally a bad idea unless there is no other method available to you.
If this is the only way you can get your query to run (due to the locking issues you mentioned), then be very careful to have your DBA review the SQL with performance tuning in mind and add appropriate indexes to optimize it.
I agree with DNewton, if you request an enhancement, let me know and I will voice my support for you as well.
eepjr24 
(BOB member since 2005-09-16)
Would a “set …” command help? If yes, this is enhancement request: ADAPT00677995
Werner Daehn 
(BOB member since 2004-12-17)
If you’re saying that you can do a SET command on the source table at the start of the datafow, then yes that may help:
dnewton (BOB member since 2004-01-30)
Will it really? What about the scenario of “update table (NOLOCK)…”? This is important, too, isn’t it?
Werner Daehn (BOB member since 2004-12-17)
I am confused about whether the issue is locking on the source or the target. 
Or both.
In our environment, turning off locking on the target table is less important. (And dangerous – if you’re seeing locking issues on the target table because multiple things are reading/writing to the target, then, turning off locking is just hiding the problem.)
Note that I’m not referring to the blocking that SQL Server has when using table compares. I suppose adding a (NOLOCK) hint to the SELECT statement that the Table Compare does for each row would be valuable. And if controlled by a checkbox, something that the designer can choose to take a risk with.
dnewton (BOB member since 2004-01-30)
How would you proceed if you want SQL Server to force into a row level lock. Normally, after updating a good amount of the table’s rows SQL Server automatically forces the table lock, does it?
What I am thinking about is a feature where you can execute a custom statement right after the creation of the database session. So something similar like the pre_load_command but usable for all connections, lookups, table readers, etc. You will be able to specify such command globally per datastore or per dataflow.
Have you been at the Insight2006 this week? Have we met? 
Werner Daehn 
(BOB member since 2004-12-17)
I believe there’s a SQL server locking hint to force row-level locking (and prevent escalating the lock to a table lock). Something like:
select xxx
from YYY with (ROWLOCK)
where …
Sorry, no, I’m not at the conference. 
dnewton (BOB member since 2004-01-30)
my point exactly. There is no “set transaction rowlock”, it has to be part of the SQL statement “update … (rowlock)…”. So my feature would not be sufficent…
Werner Daehn (BOB member since 2004-12-17)
There is a support item from 6.5 about flipping a configuration switch to always add the no-lock. Does this work with 11.7? Is it the solution to this problem?
KB# 778090
SQLServerReadUncommitted=1 - Read uncommitted data in SQL Server datastore tables.
Victory (BOB member since 2008-02-04)
Resurrecting an old thread…
Was there ever any resolution to this? We’re using 11.7 and we appear to be running into this issue. When BODI reads from a SQL Server database it’s locking the tables so operational DML is failing. I can’t see any setting in the data store that would allow us to change this behavior. Has anyone been able to work around this? (Besides the SQL Transform solution)
Thanks!
RJones (BOB member since 2007-08-29)
Yes - that hint above (about SQLReadUncommitted) is also supported in 11.5/11.7, per tech support. Give it a try…
dnewton (BOB member since 2004-01-30)
Cool! Is that a setting in the data store or on the source? Where do we access that hint?
RJones (BOB member since 2007-08-29)
It would mean editing the DSCONFIG file on the job server. You should contact tech support to get the exact placement of it. Messing with DSCONFIG should be done carefully. 
dnewton (BOB member since 2004-01-30)
Thanks for the super quick replies!
I’ll take a look at that and see if that works for us. Thanks!
RJones (BOB member since 2007-08-29)
By the way: This is all kind of a workaround anyway (to prevent ETL from blocking your source transactional systems).
A better long-term solution would be to ETL out of a copy of your transactional system: Either a nightly backup-restore (if your refresh cycle is nightly), or a replica of the databases (done with database replication, SAN cloning, or some such thing).
These other options are more work and more cost, for disk space etc., but will guarantee there is no impact. Also, you could potentially increase performance by putting the database copy or replica on the job server itself, to reduce network traffic during ETL.
dnewton (BOB member since 2004-01-30)
Does this SQLReadUncommitted setting work for SQL Server 2008?
Where should the setting for SQLReadUncommitted=1 be created in DSCONFIG for DS 12.2?
dlaplant (BOB member since 2006-03-27)