SQL Server queries WITH (NOLOCK)

[BOster]

The global value is just bad all the way around. Some queries you really don't want a dirty read, others really don't matter, having a global setting that decides is just plain bad. And then there is the global timeout setting itself which is ridiculous. Set it too low and all of a sudden every query effectively gets submitted twice increasing database load and reducing performance, set it too high and users may end up waiting twice as long for expensive queries or queries that should only take a micro second now take xx seconds because of record locks.

 

[JEMILLER]

A key point with SQL Server is that without "Row Level Versioning" turned on you get blocking SELECTS. SELECT statements can block other SELECT statements. So this NOLOCK fallback that E1 uses comes into play regardless of whether the blocking condition is the result of an update.

With NOLOCK giving no guarantee of integrity I have decided at all my SQL Server sites to do my best to eliminate the possibility that E1 will revert to NOLOCK after 3 tries. Dirty reads are not "don't give me pending updates that have not been committed" but can be "give me whatever happens to have been written out to the data files at this particular moment". Unless you are running a non-update UBE with values that will never need to be audited (no financial balances other than indicative amounts) then I submit that you would never want what NOLOCK gives you.

To solve the SQL blocking issue you can either look at Row Level Versioning or optimising you DB I/O including TEMPDB to get the best possible query execution times. I came from Oracle first and was floored when I discovered that SQL Server did blocking reads. This I hear has explained in the past why SQL Server could beat Oracle performance benchmarks. If you aren't spending time with concurrency management by simply blocking other processes then you can run things faster. In SQL 2005 they finally introduced RLV which allows SQL Server to behave like Oracle and not block selects. You get a clean and consistent SELECT with no blocking. Blocking then only comes into play with writes. RLV has a number of considerations including increased TEMPDB usage. You shouldn't just turn it on. That being said I have had great results at all my SQL sites.

If you can't turn on RLV then you can only look at optimising your I/O. Since long running SELECTS with ORDER BY are natural for E1 you want to get your longest running queries to execute within a time where other processes that would be blocked by selecting on the same data don't wait long enough for the timeout. Optimising TEMPDB I/O is a key way to do this.

As to the difference between 8.10/older tools and 8.98.4.2 I think that they introduced this parameter to try and get around queries simply hanging until they got their turn. I think it was a bad approach. Reissuing a query multiple times with the final try being a dirty read is a silly approach. It introduces more system activity/churning and give you the possiblity for dirty data to be used as input to update transactions. If the setting was going to be introduced at all they should have a provided a system API to configure it at runtime in NER and C code and had some sort of OCM mapping that would control against which programs, UBEs and perhaps tables the setting could be applied.

In short - my advice is to implement row-level versioning.