Hi Harm,

Back in the day, using (nolock) or READ UNCOMMITTED could speed up select queries quite a bit as they would no longer be blocked by active transactions, simply returning whatever is currently there, committed or not.

However, nowadays all SQL Server databases are set up using the read-committed snapshot isolation (RCSI). The Thinkwise Platform connects to the RCSI-enabled databases using READ COMMITTED isolation level.

RCSI with READ COMMITTED ensures that all reads are already non-blocking. If a transaction is affecting rows, the snapshot rows are instead read from the tempdb, so a consistent result is presented.

It is still technically possible to use READ UNCOMMITED, but the performance gains are very marginal, nearly indistinguishable. The only performance gain would be not having the tempdb version look-up in case a transaction is active.

The drawback to switching to READ UNCOMMITTED includes duplicate rows, missing rows and concurrency problems. The Thinkwise Platform is not configured to handle these issues.

Bottomline - I doubt using READ UNCOMMITTED is ever a solution to performance challenges.

Worth to check:

• Verify the transaction level as it is done on database creation. If you have an older database created before RCSI was available, it may not have this setting enabled yet.
• If your tempdb is completely bottlenecked by massive concurrent transactions, it may affect the speed of RCSI version lookups. But in this case the solution would be to ensure the tempdb is working properly (data files, storage)

Hi Anne,

Thank you for your quick response; this clarifies a lot.

We'll look into this and find a good solution.