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.
Monday, 18 February 2013
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.