Ran into a performance issue at one of the client sites yesterday. They were using SQL Server and were relying on the automatic stats collection. Since auto-stats gets kicked off only based on a sampling of the records (you can read this KB article to see how SQL Server determines when to kick off auto-stats and can also search the blog for other things related to stats collection), for their tables that had millions of records in it with their keys that grew monotonically, this meant that the stats for those newly inserted rows based on last night’s feeds was grossly in-correct. Since SQL Server’s optimizer is a Cost Based Optimizer (CBO), the updated stats are very important for generation of good execution plans.
They also were running a de-frag job regularly and as you might already know, when you rebuild an index, the stats are updated automatically. However, this de-frag job which was being run on a weekly basis was kicked off based on a certain percentage of fragmentation level. As a result, the tables with clustered keys that were growing monotonically were running into stale statistics issues since they were below the fragmentation threshold specified in the maintenance task. So, we added another step to the maintenance task to perform update of the statistics with a fullscan on the indexes of the tables that did not get de-fragmented. We restricted this fullscan stats update to the indexes to keep the timing short. This resolved the performance issue for the client as the optimizer now had good up-to-date statistics to generate a good execution plan.
There are additional changes to be aware of between SS2k and SQL 2005 when it comes to counter updates because of DML modifications. In SS2k, a counter is used to track row level modifications whereas SQL 2005 uses a counter that tracks changes at the column level. The counter updates are also different when an update is made to key column(s) v/s when you update non-key column(s). A non-key column update raises the counter with the number of updated columns whereas a key column update raises the counter with 2 for each column. And another very welcome change is that the TRUNCATE TABLE and BULK INSERT commands do not raise the counters in SS2k but they do in SQL 2005.
Systems Engineering and RDBMS 