A client of mine phoned me last week with an error message that had appeared in their application. My worst fears were confirmed when I heard the words "logical consistency-based I/O error".
The error message reported was: SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x31b2521f; actual: 0x4bd50f4c). It occurred during a read of page (3:8191715) in database ID 17 at offset 0x00000f9fdc6000 in file 'C:\MSSQL\Data\Data2.ndf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
I recently came across a situation at a client which I
thought I would share with you. I came across this while doing some routine
maintenance and noticed a small table consuming a lot of memory. I mean this
table, which is a smallish lookup table, was consuming almost 430 MB of buffer
space. The thing about lookup tables and the way they are used is that the
entire table is usually read into memory. This is by design but the problem
here is that there should be no way that this table needs 430 MB.
I see many developers writing SQL code and using NOLOCK as a "performance tool". I see NOLOCK spread liberally through scripts, applied to every single table as far as the eye can see. This technique is dangerous and I would like to share why.