I want all 3rd party code used here to be free from NOLOCK hints.
I have an Alert on "Error 601 - Data movement during NOLOCK", and I get several of those a day. I presume that for every Alert that I get there are many?? more that do not raise error but ARE subject to data movement in the Index and, without any error or indication, either fail to display some data rows, or duplicate some rows (i.e. Index Page Split has part of the page missing when the NOLOCK hint query runs, or the query sees all the rows from the index page AND also the rows from the split-page)
I have raised this with the 3rd party vendors as an issue. Predictably I heard nothing in response! So today I told the Vendors that I require them to give me a timetable for removal of all NOLOCK from their code, or to tell me that they cannot do that (in which case there is a real possibility that we will move to a different vendor).
I mentioned the Error 601 Alerts that I am getting and they said "What systems are they originating from?". I'm very happy to help the Vendors find a solution to the problem, but I have never seen anything that will give me a handle on WHICH queries with NOLOCK hints result in Data Movement errors (and even better would be any query with NOLOCK hint that suffered from a page split but did not trigger a 601 error).
I'd appreciate any suggestions for how I might find this needle-in-a-haystack.
Background (although I doubt you need it )
To me that NOLOCK is unacceptable as it suggests that for all the Alerts I get the program aborted (hopefully cleanly!) but also indicates that there will be a much?? higher number of successful uses, with no abort, where there was an index page split but which did not cause the program to abort (but DID either fail to display some rows [missing because of the index split] or include some rows more than once [present during the query, and present a second time on the freshly split index page]).
It is totally unacceptable to me that our users may not see some data rows on a perfectly innocuous display / reporting APP (because an index split meant that those missing rows were in the process of being moved, and were missed from the SQL Query because it used the NOLOCK hint). A user could make a critical business decision based on the [inaccurate] information displayed to them, and because the issue cannot be tracked, logged or reproduced, it will be impossible to prove that the Vendor's negligence was the cause.