A colleague wanted to work an old Henny Youngman joke into a performance analysis. He wound up not using it, so I will:
Man goes to the doctor. He swings his arm and says, “Doc, it hurts when I do this.” Doc says, “Don’t do that!”
I came across this error message this morning:
Mar 6 2012 3:02AM|Error: 7886, Severity: 20, State: 2. Mar 6 2012 3:02AM|A read operation on a large object failed while sending data to the client. A common cause for this is if the application is running in READ UNCOMMITTED isolation level. This connection will be terminated.
I haven’t heard back from the client regarding the error, but my guess is that they’re surprised to find their application is running in READ UNCOMMITTED isolation mode. After all, they didn’t tell SQL Server to switch from READ COMMITTED TO READ UNCOMMITTED.
What the application did tell SQL Server to do, however, was run an insert statement using the NOLOCK query hint. Five of them, actually. Using NOLOCK gives you a “dirty read” of uncommitted data. The error occurs when the underlying data is being moved or changed by another user. This KB discusses the issue.
My advice to the client was “Don’t do that!” Using dirty-read data for inserts isn’t a good idea. The best solution here is to remove the NOLOCK hints.
The server had other issues contributing to the problem. I ran Bart Duncan’s missing index query and came up with 23 missing indexes with “improvement values” exceeding 1 million. The No. 1 index had an IV of 28 million. Never seen numbers that high on so many indexes. So the query was probably taking way too long to run, giving other applications (maybe even the same one with a different SPID) a chance to alter the data that the session in question was reading.
Adding the indexes would speed up query processing and cut down on the chance that this error would occur. However, running inserts with NOLOCK hints endangers data consistency. Best to remove the hints.