We have a database that contains an XML column . Over time we see a huge degradation in performance for insert, update queries . On an avg we used to see atleast 4k records completed in an hour , now we see just 1k records being inserted/updated . A full stats update maintenance is scheduled every Saturday , this used to make a huge difference in performance . However , recently the dba completed a full index rebuild for the table due to fragmentation . Right after this update , there was an improvement in performance . But we are seeing a downfall again and the stats update which used to help previously does not seem to help anymore . Please provide your input on what could be wrong ? We see high pageiolatch-sh waits . Could the old stats maintenance plans have become stale after index rebuild ? The table has 3 million rows and we do not perform any XML parsing operations . Entire XML is preformed and simply inserted or updated into the column .
Have you looked at the execution plans, both immediately after the index rebuild and after performance degrades? Are they different? That would be a very important clue as to what might be happening. The latch waits could be an indication of IO problems. That could either be volume issues, or actual performance issues on the disk. You'll need to look also at performance counters such as sec/write and sec/read to understand if the disks are having issues. You should also monitor for latches and latch wait metrics. The problem could be that you're just overwhelming the physical box. It's also possible that your indexing on the tables doesn't support the inserts you're doing. It's hard to say without absolute certainty without more information.